Basic BISM

1. Basic BISM Chris Webb Crossjoin Consulting Ltd chris@crossjoin.co.uk

2. Who Am I? • Chris Webb chris@crossjoin.co.uk • Independent Analysis Services consultant and trainer www.crossjoin.co.uk • Author: “MDX Solutions” and “Expert Cube Development with SSAS 2008” • SQL Server MVP • Blogger: http://cwebbbi.wordpress.com

3. Agenda • What is BISM? • Tabular vs Multidimensional positioning • Building a Tabular Model • Demos, demos, demos…

4. What is BISM? • BISM = BI Semantic Model • It’s the new UDM (if you remember that) • The product is still officially called SQL Server Analysis Services • SSAS now has two design experiences: • Multidimensional models, ie cubes • Tabular models, a ‘corporate’ version of what we have with PowerPivot • BISM = Multidimensional + Tabular

5. Tabular vs Multidimensional • It’s an either/or choice at the moment: • An instance can only be one or the other • A project can only be one or the other • Though in the future we may be able to have a single database that can be viewed or designed in both ways

6. Tabular Pros • Represents the future • Concepts easier to understand for those with a relational/SSRS background • Speed of the Vertipaq storage mode • Support for Project Crescent • DirectQuery = ROLAP done right (hopefully) • Ability to import existing PowerPivot models • No more processing-this-unprocesses-that dependencies

7. Tabular Cons • Version 1.0 product, therefore immature • DirectQuery has a lot of limitations • No parallel processing for partitions -> big impact on processing times • No display folders and other ‘nice to have’stuff • Vertipaq effectively limited by RAM available on the server • Some paging options, but performance suffers • DAX still unable to do the same kind of complex calculations that MDX Scripts can

8. Multidimensional Cons • Regardless of the official line, unlikely ever to get much priority for new features • Very few new features in Denali • No support for Crescent yet (though coming at some point) • No integration with PowerPivot • Cubes are just too confusing for some developers • Old technology

9. Multidimensional Pros • Mature technology, well understood and well resourced in the marketplace • MDX Scripts allow for complex calculations • Disk-based model allows for greater scalability at the very high-end than Vertipaq • Allows for complex modelling, eg many-to-many relationships

10. Creating a New Tabular Project • Tabular projects require Visual Studio 2010 • Projects are built in three ways: • Start from scratch • Import from PowerPivot • Import from an existing Tabular model • Projects consist of a single .bim file, plus some hidden files and folders

11. Importing Data Into Tables • All the data sources that PowerPivot supports are present in the Tabular model • So you get more supported datasources that with Multidimensional, egOdata • Data can be imported by either • Selecting tables and then filtering in the UI • Writing your own SQL/MDX • Tables can be partitioned • You can create calculated columns with DAX

12. Workspace Databases • Workspace databases are a ‘working copy’ of your database that allow you to see your data while you model it • Probably hosted on a local SSAS instance, not a separate server (even your dev server) • Probably with a subset of your real data • Do not confuse this with your production or even your dev server! • Various options relating to whether it’s retained on the server, detached or deleted after you close BIDS • No cube browser in BIDS – you can open Excel direct from BIDS and browse your workspace database

13. Relationships • Relationships can be added between tables just as in PowerPivot • New relationship designer makes life much easier • Two tables can now have multiple relationships between them • egRole-playing date dimension tables • Only one relationship is ‘active’ • DAX has a UseRelationship() function to allow you to choose which one to use

14. Hierarchies • Values in a column can be sorted by using the Sort By Column property • eg for day or month names • Hierarchies can be built with drag and drop inside diagram view • No true support for parent/child hierarchies • But can be flattened using calculated columns and several new DAX functions like Path()

15. Measures • Simple measures (sums, counts etc) can be created with a single click • More advanced measures can be created with DAX expressions • Format strings have been replaced by separate properties • Measures are displayed in a grid under the table in BIDS (ugh)

16. DAX Queries • DAX is now a query language as well as a calculation language • Very different from MDX • Tabular models can still be queried with MDX • Much more suitable for detail-level reporting than MDX • Much more ‘relational’ – but not SQL • DAX queries will often perform better than MDX queries against the same model

17. DirectQuery • DirectQuery is essentially ROLAP for Tabular • All queries are translated to SQL • Only works with SQL Server data sources • Several limitations: • Some DAX functions don’t work (eg time intelligence) • No calculated columns • No MDX queries, only DAX • Vertipaq/DirectQuery mixed mode possible • Data exists in both storage modes • One storage mode used by default • Clients can choose which storage they use with a connection string property

18. Security • Tabular models have something similar to Multidimensional dimension security • No cell security • Allow you to use DAX expressions to filter rows in tables for particular roles • Basic administrative security, can either • Read data and/or • Refresh data • Or be an administrator

19. Summary • Tabular is very easy to get up and running • Suitable for 70% of SSAS projects today • Certainly good for all the small-scale or basic projects • Good if you never liked or understood cubes • Outperforms Multidimensional in most cases • Download the CTP3 demo VM!

20. Blogs • Cathy Dumas http://blogs.msdn.com/b/cathyk/ • Kasper de Jonge http://www.powerpivotblog.nl/ • Marco Russo http://sqlblog.com/blogs/marco_russo • Alberto Ferrari http://sqlblog.com/blogs/alberto_ferrari