1 / 20

Basic BISM

Explore the world of Business Intelligence Semantic Models (BISM) with a focus on Tabular vs. Multidimensional analysis. Learn the pros and cons, project creation, data importing, workspace databases, relationships, hierarchies, measures, and DAX queries.

rhondas
Download Presentation

Basic BISM

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related