BISM Introduction Marco Russo marco russosqlbi com http

BISM Introduction Marco Russo marco. russo@sqlbi. com http: //sqlblog. com/blogs/marco_russo

Who I Am • • BI Experts and Consultants Problem Solving Complex Project Assistance Data. Warehouse Assesments and Development Courses, Trainings and Workshops Microsoft Business Intelligence Partners Book Writer New M 2 M paper: www. sqlbi. com

Agenda • • • Power. Pivot is the road to BISM What is BISM Multidimensional and Tabular Several Scenarios Dimensional vs Relational Modeling

Semantic Model for Business Intelligence WHAT IS UDM?

UDM – Unified Dimensional Model • Semantic model for Business Intelligence • Introduced by SQL 2005 (SSAS) • Connect data from several sources – Relationships between tables – Calculations • In tables – calculated columns in DSV (T-SQL) • In the model – MDX script (MDX)

UDM – Data Modeling 1/3

UDM – Data Modeling 2/3

UDM – Data Modeling 3/3

UDM – Data Analysis

SQL Server 2005 Analysis Services Share. Point 2003 & Share. Point 2007 3 rd party SSAS clients • Reporting Services 2005 & Report Builder 1 MDX MDX Excel 2003 + add-in & Excel 2007 MDX Performance. Point 2007 MDX UDM

SQL Server 2008 Analysis Services Share. Point 2007 • Reporting Services • Performance. Point Services • Excel Services • Visio Services 3 rd party SSAS clients Reporting Services 2008 & Report Builder 2 MDX MDX Excel 2007 MDX UDM

SQL Server 2008 R 2 Analysis Services Share. Point 2010 • Excel Services • Performance. Point Services • Visio Services 3 rd party SSAS clients Reporting Services 2008 R 2 & Report Builder 3 MDX MDX Excel 2010 MDX UDM

Semantic Model for Business Intelligence WHAT IS BISM?

BISM – BI Semantic Model • Business Intelligence Semantic Model – For reporting, analytics, scorecards, dashboards – Unify OLAP (UDM) and relational (SMDL) models • Queryable with MDX and DAX • Different engines: – MOLAP – ROLAP (UDM) – Vertipaq – in-memory columnar database (Tabular) – Direct. Query – query passthrough to SQL Server 2012

BI Semantic Model

BI Semantic Model What about Existing Applications? Existing applications Based on Unified Dimensional Model Every UDM becomes a BI Semantic Model New applications New technology options 2012

BI Semantic Model: Architecture

Road to BISM • Power. Pivot for Excel – – Free Add. In for Microsoft Excel 2010 Available since June 2010 Different releases for 32/64 bits Contains the Vertipaq Engine • SSAS running in process with Excel • Power. Pivot for Share. Point – Vertipaq running server side – Integrated with Share. Point

Road to BISM: SQL Server 2012 • SQL Server 2012 – – – New release of SQL Server (expected in 2012) MOLAP / Vertipaq running server side Works standalone (Power. Pivot for SP cannot) Vertipaq / MOLAP selected at instance level New release of Power. Pivot (2. 0)

Vertipaq: change your mind! • Vertipaq in SQL Server Analysis Services 2012 – Not a simple evolutionary step – It is a complete new environment – New calculation paradigms, new data modeling techniques, new formula authoring experience – No aggregations – Security, partitioning and paging (in Power. Pivot) • Want to learn BISM? – Forget MDX, Forget SQL, Learn DAX!

SQL Server Analysis Services 2012 BISM Multidimensional Share. Point 2010 • Excel Services • Reporting Services • Performance. Point Services • Visio Services SQL Server Reporting Services 2012 MDX 3 rd party SSAS clients MDX • Project Crescent ? MDX Excel 2010 Share. Point 2010 MDX UDM

SQL Server Analysis Services 2012 BISM Tabular Share. Point 2010 • Excel Services • Reporting Services • Performance. Point Services • Visio Services SQL Server Reporting Services 2012 MDX 3 rd party SSAS clients Share. Point 2010 MDX • Project Crescent DAX MDX Excel 2010 MDX DAX BISM DAX 3 rd party SSAS clients

Instance-level choice • A SSAS instance can be: – MOLAP (classic mode) – Power. Pivot for Share. Point (since SQL 2008 R 2) – BISM (only SSAS 2012) • You can install several instances of SSAS 2012 side-by-side on the same server, also with different roles

DAX Role in BISM • DAX is – A language to write expressions (like Power. Pivot 1. 0) – A query language (new to BISM) • Syntax based on CALCULATETABLE (existed since 1. 0) • Now a different projection can be defined (ADDCOLUMNS) • You can control the order of results (EVALUATE / ORDER BY) – It is the fastest way to query BISM data stored in Vertipaq engine • MDX is available, but it adds some overhead

What is Changing? SQL 2012 SQL 2008 R 2 BISM UDM DAX MDX RELATIONAL MULTIDIMENSIONAL IN MEMORY ON DISK FAST VERTIPAQ ENGINE AGGREGATIONS LEAF LEVEL COMPUTATION CALCULATED MEMBERS FILTER / ROW CONTEXT MDX FUNCTIONS, TUPLES ETC SELF SERVICE + CORPORATE ONLY EASY DATA INTEGRATION BI PROFESSIONALS NEEDED

Relational vs Multidimensional • UDM – Facts, Dimensions, Bridges, Snowflakes – Relationships: Regular, Referenced, M 2 M – Hierarchies, Attribute Relationships • BISM – Tables – Relationships (1: N, One Column only)

Complexity in BISM • BISM: simple data model • Complexity is moved into: – Calculated columns – Measures – DAX, DAX and… DAX! • Every column is a dimensional attribute • Every column is a measure

Is Dimensional Modeling still necessary for Power. Pivot / BISM? DIMENSIONAL VS RELATIONAL MODELING

Dimensional vs Relational • Multidimensional (OLAP/UDM) – – Strong separation between facts and dimensions Advanced relationship types Great aggregation level computations Poor leaf level computation performances • Designed, optimized for dimensional models • No brain option: go dimensional

Dimensional vs Relational • BISM (Tabular) – – No concept of fact and dimension: only tables Very simple concept of relationship No aggregations, in memory computation Great leaf level computation performances • Optimized for simple models • Two options – Dimensional modeling – Relational modeling

Dimensional vs Relational • Relational Modeling – Does not mean third normal form! – Users like facts and dimensions • Denormalization – Needed for the user experience – And for performances • Each relationship has a cost, remember it!

BISM: New Power • Calculated columns – Materialize complex calculations – ABC Pareto is a good example – Have access to the whole data model • Not like calculated columns in UDM DSV! • Linked tables – Two steps computation • Easy integration with many sources • Power to the end user

BISM: DAX • • • Brand new language Mixes programming and querying Much cleaner and easier than MDX Designed for efficiency on modern CPU Hard to enter the «DAX Mind» – For MDX and SQL programmers – Don’t search same concepts, they are not there! • Once you master it… awesome! • Simple, not easy

Prepare to DAX! Power. Pivot for Excel 2010: Give Your Data Meaning

THANK YOU! For attending this session and PASS SQLRally Nordic 2011, Stockholm