Business Intelligence Semantic Model Tomislav Piasevoli Soft Pro

Business Intelligence Semantic Model Tomislav Piasevoli Soft. Pro Tetral d. o. o.

Contents • • • What is BISM? Power. Pivot SQL Server „Denali” The significance of BISM Guidelines

Prerequisites • interest in BI • curiosity

Few questions to start with. . . • how many of you worked in Power. Pivot? • how many used Power. Pivot on Share. Point? • how many of you has experience in building cubes? • how many used cubes for reporting?

1 WHAT IS BISM?

Analytical systems • turn data into information • goals of their architecture 1. optimize access to source data 2. prepare the data for analysis

I – Optimize access to source data • store the data in structures suitable for analysis and ad-hoc querying or leave them „as is“ on the source • requirements – – good performance flexibility scalability. . . • which technology to choose/use?

MOLAP, ROLAP, HOLAP • row-based databases – MOLAP = multidimensional – ROLAP = relational – HOLAP = hybrid (only for measure groups = facts) • • performance based on aggregations partitioning compression (MOLAP) not limited by available memory

II – Prepare the data for analysis 1. enable the data analysis like there’s only one source of data – actually, there can be many various data sources • • relational databases Excel files text files RSS feeds, . . . – where to perform the join of the distributed systems? 2. provide the interpretation (business meaning) to data – how to make it comprehendable to the end users?

Semantic data model • a metadata layer (on top of the data) – describes concepts (entities) and connections (relations) between them – user-oriented • shows what the tables and relations represent in the real world semantics = the study of meaning

UDM • existing semantic model for BI • came with SQL Server 2005 • enables connecting data from various sources – add relations between tables – add calculations • in tables – calculated columns in DSV (T-SQL) • over the model – MDX script (MDX) – calculations cannot be combined (different languages!) or it’s very hard to do so

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 Reporting Services 2008 & Report Builder 2 MDX 3 rd party SSAS clients MDX Excel 2007 MDX UDM

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

Power. Pivot add-in for Excel 2010 PP

2 POWERPIVOT

Power. Pivot • SQL Server 2008 R 2 Analysis Services as an integrated version • covers personal and team BI segments

Integration Excel 2010 • free add-in for Excel 2010 (www. powerpivot. com) • comes as 32 -bit and 64 bit application Share. Point 2010 • comes with SQL Server 2008 R 2 x 64 • during the installation, you choose SQL Server Power. Pivot for Share. Point 25

Verti. Paq • • in-memory column-based database very high data compression scales linearly with the number of cores doesn’t require the process of designing and building aggregations and other tunning • envisioned to support partitioning and paging on large data sizes (although it doesn’t do that in Power. Pivot)

BISM v 1 • • • the new semantic model for BI came with SQL Server 2008 R 2 (for Power. Pivot!) enables connecting data from various data sources – add relations between tables – add calculations, two places: • in tables – calculated columns (DAX) • over the whole model – calculated measures (DAX) – calculations can be combined (same language)

BISM v 1 • the result of merging todays analytical (UDM = SSAS) and reporting (SDML = SSRS) semantic data model with the goal that it integrates (in due time) – reporting, analysis, dashboards and scorecards – personal, team and corporate BI segments • works in cached (Verti. Paq) mode

BISM v 1 – data modeling

BISM v 1 – data analysis

DEMO BISM v 1 in SQL Server 2008 R 2

Short contents of the demo • • Power. Pivot is an arhived SSAS database DAX functions are hidden among MDX functions Share. Point controls with the BISM instance of SSAS the existance of new DMVs for BISM instance on Share. Point • BISM models support MDX • SQL Profiler Verti. Paq SE events

3 SQL SERVER „DENALI”

BISM v 2 • one model for all – reporting, analysis, dashboards, scorecards – personal, team, corporate BI • has a relational and multidimensional API • it was announced that it will eventually support both cached (Verti. Paq) and the pass-through* (realtime) mode * only SQL Server data sources for now

Pass-through • no additional database • data stays as is in the original structures • ideal for the realtime analysis

BI semantic model and its layers Data model • relational model • supports both relational (DAX) and multidimensional(MDX) interface Business logic • DAX calculations (columns and measures) • after„Denali” maybe even MDX Data access • cached (Verti. Paq) • in „Denali” also the realtime (pass-through mode)

Analysis Services „Denali” - UDM Share. Point 2010 • Excel Services • Reporting Services • Performance. Point Services • Visio Services Reporting Services „Denali” 3 rd party SSAS clients Share. Point 2010 MDX ? MDX Excel 2010 MDX • Project Crescent MDX UDM

Analysis Services „Denali” - BISM Share. Point 2010 • Excel Services • Reporting Services • Performance. Point Services • Visio Services Reporting Services „Denali” 3 rd party SSAS clients Share. Point 2010 ? MDX • Project Crescent MDX DAX MDX Excel 2010 MDX DAX BISM DAX 3 rd party SSAS clients

Power. Pivot „Denali” add-in for Excel 2010 BISM

Announced improvements of BISM* • hierarchies, KPIs, parent-child, drillthrough, perspectives • additional DAX functions (Rank. X, Distinct. Count, Group. By, Lookup, . . . ) • security (role-based with Active Directory, column/row based) * doesn’t mean that „Denali“ will actually have these exact features

What BISM in „Denali” won’t have? • some of the UDM features – scripts, actions, translations, role-playing dimensions – object model – write-back • other – realtime for non-SQL Server data sources – MDX query support for realtime

UDM improvements in „Denali” • mostly outside of GUI – no more 4 GB string store limit – better support for Power. Shell – better monitoring (XEvents) – various engine optimizations • the goal is to make the SSAS (UDM instance) more scalable

4 THE SIGNIFICANCE OF BISM

Advantages of BISM • relatively simple model (no cubes, dimensions, measures, . . . ) • fast response no matter the granularity of query • flexible (every column is also a measure and an attribute) • DAX calculations are similar to Excel formulas • relational model and result format is more understandable and user-friendly to majority of people

The same semantic model Power. Pivot for Excel Power. Pivot for Share. Point SSAS „Denali”

Scalability Excel Share. Point „Denali”

Other advantages of BISM • faster development than in UDM • prototyping by end-users – they know the best what they want from the data and what the data means (semantic!) • easier changes of model • reduction of cost in developing the full BI solution

New possibilities in data modeling • Highlights from various blogs – Marco Russo – ad-hoc joins, ETL? – Thomas Ivarsson – master-detail design – Alberto Ferrari – point-in-time, SCD-2, rank

5 GUIDELINES

source: Thomas Kejser, SQLCAT Column. Store 50 TB 5 TB 100 GB ROLAP Scalability* Positioning the BISM Scalability= - good support for concurrent queries - not dependent on available memory - good maintainability MOLAP BISM 2 GB Power. Pivot Usability

Conclusion • • BISM is not a replacement for UDM DAX is not a replacement for MDX every model has its advantages BI architects must decide when to apply which model

Advantages and disadvantages BISM UDM scope leaf level ad-hoc models m 2 m complex models

BI developers • most of the existing solutions should stay on UDM • new solutions – use UDM for complex systems (complex calculations, write-back, budgeting, forecasting, . . . ) – try BISM as a potential solution for the rest, mostly simple systems • UDM and BISM can be installed on the same server (SSAS instances)

Users, here’s your DIY kit! • try and/or migrate to Office 2010 • install the free Power. Pivot for Excel 2010 add-in • load and connect various data sources • try and learn DAX • analyze your data • validate the completness of your model

Model is ok? • use it for your personal BI – Excel 2010 and Power. Pivot • share the model with others – Share. Point 2010 and soon SQL Server „Denali“

Model is not (yet) ok? • improve it – more experienced colleges • give the Power. Pivot model to your company guru – external consultant • improvement is possible? – Power. Pivot – SQL Server „Denali“ (BISM instance) • or else. . . – build the UDM model based on the BISM template – implement it on SQL Server „Denali“ (UDM instance)

source: Tech. Net

Related content • Presentations – MS BI Roadmap (SQL Server Denali) • Workshops – Build Your First Microsoft BI Solution with SQL Server 2008 R 2