Transcript Basic BISM
Basic BISM Chris Webb Crossjoin Consulting Ltd [email protected] Who Am I? • Chris Webb [email protected] • 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 Agenda • • • • What is BISM? Tabular vs Multidimensional positioning Building a Tabular Model Demos, demos, demos… 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 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 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 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 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 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-tomany relationships 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 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, eg Odata • 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 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 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 – eg Role-playing date dimension tables – Only one relationship is ‘active’ – DAX has a UseRelationship() function to allow you to choose which one to use 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() 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) 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 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 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 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! 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