Transcript Overview on new SSAS 2012 Tabular Model
Overview on new SSAS 2012 Tabular Model
Kevin S. Goff
Kevin S. Goff: 30 seconds of Shameless Promotion
• Developer/architect since 1987 / Microsoft SQL Server MVP 2011 • Columnist for CoDe Magazine since 2004 • Wrote a book, collaborated on a 2 nd book • Currently writing a 3 rd book on BI with SQL Server • Frequent speaker for SQL/SharePoint/.NET community events • SQL Server/Business Intelligence Practice Manager for
SetFocus, LLC
• SetFocus Free Training site:
http://freetraining.setfocus.com
• www.KevinSGoff.Net
(Go to downloads area) 4/27/2020 Overview - SSAS 2012 Tabular Model 2
My daughter Katy (3 years old)
4/27/2020 Overview - SSAS 2012 Tabular Model 3
New Tabular Model
• This is a demo of what we can do with the Tabular Model and
how it differs from the standard Multidimensional OLAP approach in SSAS
• Not intended to show how to create a project from scratch • Let’s look at a “final result” and walk backwards to see a few
steps on how it was put together
• This presentation is based on the TechNet webinar I did earlier in
2012 (see the last slide for the link)
• Presentation is also based on a CoDe Magazine article that will
appear in CoDe Magazine at the very end of the year – if you want an early draft of the article, email me at [email protected]
and I’ll send you a copy of the article
• This slide deck is available on
http://kevinsgoff.net
4/27/2020 Overview - SSAS 2012 Tabular Model 4
New Tabular Model
• • • • • • •
Major new feature in SQL Server 2012 Need to take a step back to look at the history of SQL Server Analysis Services and OLAP
1998: Microsoft released OLAP Services with SQL Server 7 2000: Microsoft released Analysis Services with SQL Server 2000 – Better support for business dimensions – Limitations by today’s standards (only 1 fact table per cube), but an important release 2005: Enhanced version of Analysis Services with SQL Server 2005 – UDM (Unified Dimension Model): an “API”, a “bridge” between users/developers and multidimensional sources – Support for more dimension types – Significant Data Mining functionality – To many, the first truly powerful version for creating OLAP solutions 2008: Enhanced version of Analysis Services w/SQL Server 2008 – More data mining enhancements – Some MDX enhancements By 2008, OLAP developers had great tools with SSAS to build multidimensional OLAP cubes 4/27/2020 Overview - SSAS 2012 Tabular Model 5
New Tabular Model
• To many, Microsoft OLAP with Analysis Services is great • To others, some criticisms:
– Some perceive a steep learning curve – The query language (MDX) can be difficult to learn – Different paradigm than relational databases
• The market started to see competing tools that allowed developers/power users to create analytic databases with less perceived complexity (QlikView) • In the spring of 2010, Microsoft responded…
4/27/2020 Overview - SSAS 2012 Tabular Model 6
New Tabular Model
• • • • • In spring of 2010, Microsoft introduced a very important tool with Excel 2010 / SQL Server 2008R2: PowerPivot – Free add-on for Excel: allowed users to take a relational database (or other data sources) and create the equivalent of a compressed star-schema cube (as a database inside of Excel), so that users could “slice and dice” the data in the same way they previously could with OLAP data – Integration with Analysis Services 2008R2 and SharePoint 2010, so that users could publish the combination pivot table/chart and underlying database to SharePoint, so that others could interface with it Introduced the Vertipaq engine (now called xVelocity - same technology used for the columnstore index in SQL Server 2012) Introduced DAX (Data Analysis Expressions) for creating calculations and expressions A 1.0 product: some quirks, interface missing some features, some issues with SharePoint integration, but most felt would eventually become a game-changer Not as powerful as a full-blown OLAP SSAS database solution, but provided a certain level of autonomy for power users to create certain analytic solutions using nothing more than Excel 4/27/2020 Overview - SSAS 2012 Tabular Model 7
New Tabular Model
• So as of mid-2010, there were 2 ways to create analytic databases:
– Microsoft Analysis Services (multidimensional OLAP) for larger, corporate and enterprise BI solutions • Very powerful • Still required steep learning curve to master • Usually requires strong knowledge of XMLA and MDX – PowerPivot (either with or without SharePoint 2010 integration) for personal or departmental BI solutions • Easy to use • Needed more functionality • DAX language could be as difficult as MDX • Needed access to this data from other reporting / client tools 4/27/2020 Overview - SSAS 2012 Tabular Model 8
New Tabular Model
• • SQL Server Analysis Services 2012 and the new Business Intelligence Semantic Model (BISM) Three ways to create Business Intelligence database applications: – 1) Analysis Services multidimensional OLAP Model, created using SQL
Server Data Tools (formerly BIDS).
• Tool of choice for those who have already gone through the SSAS/OLAP learning curve, and are looking for the most advanced functionality • A few additions/changes, but largely unchanged from SQL Server 2008 – 2) New Analysis Services Tabular Model (also created using SQL Server
Data Tools)
• Not nearly as powerful as multidimensional OLAP, but might be sufficient for some corporate BI solutions • Can create an SSAS database using SSDT and then access in tools like SSRS or PerformancePoint services , just like you would with an OLAP database • Very fast for many databases • Good for prototyping of what will eventually become OLAP multidimensional databases – 3) New updated version of PowerPivot 2.0 • Greatly enhanced over PowerPivot 1.0
• Better data designer, KPIs 4/27/2020 Overview - SSAS 2012 Tabular Model 9
New Tabular Model
# users Tool Query language
SSAS Multidimensional OLAP
Large SSDT MDX Reporting tool access Pass through query to underlying source Row level security w/Windows authentication Excel, SSRS, PPS, or any tool capable of reading MS OLAP cubes (Power View cannot be used directly) Yes (ROLAP) Yes Many to many (bridge) and role-playing relationships Size restrictions / management Database engine Yes Very large cubes, can use either MOLAP or ROLAP, can build partitions OLAP
SSAS Tabular Model
Large SSDT DAX (if MDX is passed, is converted to DAX, so long as DirectQuery is not used) Excel, SSRS, PPS, Power View, or outside tools that can read tabular model Yes (DirectQuery) Yes Only via DAX Very Large models, can use DirectQuery, can build partitions xVelocity (formerly Vertipaq)
PowerPivot (with SharePoint)
Small to medium Excel DAX (if MDX is passed, is converted to DAX) Excel, SSRS, PPS, Power View, or outside tools that can read tabular model No Only in data refresh if windows authentication implemented on underlying data source Only via DAX Limit of 2 GB after compression xVelocity (formerly Vertipaq) 4/27/2020 Overview - SSAS 2012 Tabular Model 10
Tabular Core Features: DAX (Data Analysis Expressions)
• First, define source data and “shape” in star-schema format • Creating Tabular Projects in SSDT (formerly BIDS) • Defining table relationships (best to shape data in star-schema, Fact-
Dimension relationships)
• Creating necessary hierarchies for parent/child traversal of data • Customizing tables (sort order) • DAX Language - Data Analysis Expressions – –
Good for equivalent of Fact/Dimension relationships that are found natively in MultiDimensional OLAP, such as Many-to-Many and Role-Playing Relationships Good for custom calculations and KPIs
• Key Performance Indicators (KPIs) – not as powerful as MultiDimensional
OLAP, but with additional DAX calculations, can usually get the job done
• One-click Incremental testing in Excel (really cool feature) • Deploying • Roles • Partitions 4/27/2020 Overview - SSAS 2012 Tabular Model 11
How can we access tabular Data
• Once deployed, a Tabular Model database is available in an SSAS 2012
Tabular instance, just like an OLAP database is available in a regular SSAS Multidimensional instance
• Can access data in SSRS, using the regular OLAP-style interface (the engine
translates the MDX code from SSRS into DAX code)
• Can access in PerformancePoint Services (must use a connection string:
Data Source=WIN-1016SLN6NLI\SQL2012TABULAR;Initial Catalog=ADW2012Tabular
)
• Can access in Excel 2010 • Can create new reports in SharePoint Enterprise using new SQL 2012 Data
Visualization tool called Power View – good for summary reporting and “storyboarding” using Silverlight plug-in
– Can actually export to PowerPoint and use PowerPoint to interact with
report
– Excel 2013 has native Power View capabilities 4/27/2020 Overview - SSAS 2012 Tabular Model 12
Recommended reading
• I’ve written some articles in
CoDe Magazine on SQL 2012
•
http://code-magazine.com/articleprint.aspx?quickid=1203051&printmode=true
•
http://code-magazine.com/articleprint.aspx?quickid=1206021&printmode=true
– Look for more articles on SQL 2012 later in the year • I ran a 13 part webinar series on Microsoft TechNet, new features in SQL 2012 –
Link to the recordings
• Recommended Reading 4/27/2020 Overview - SSAS 2012 Tabular Model 13
Upcoming speaking engagements
• New York City SQL Server BI User Group
( http://msbigdatanyc.com/ ) on November 12, on the new Columnstore Index in SQL Server 2012
• “Probably” speaking at SQL Saturday in Washington DC on
December 8, 2012 ( http://sqlsaturday.com/173/schedule.aspx?sat=173 )
• Speaking at SQL Live! Conference in Orlando in December 10-14,
2012:
–
http://sqllive360.com/events/2012/sessions/session list.aspx
–
http://download.1105media.com/vslive/sqllive/2012/SQL_S erver_Live_Agenda.pdf
4/27/2020 Overview - SSAS 2012 Tabular Model 14