Microsoft Power BI Reviews

A Getting-Started and Survival Guide for planning, designing and building Tabular Semantic Models with Microsoft SQL Server 2012 Analysis Services.

by Paul Turley

This post will be unique in that it will be a living document that will be updated and expanded over time.  I will also post-as-I-go on the site about other things but this particular post will live for a while.  I have a lot of good intentions – I know that about myself and I also know that the best way to get something done is to get it started – especially if I’m too busy with work and projects.  If it’s important, the “completing” part can happen later.  In the case of this post, I’ll take care of building it as I go, topic by topic.  Heck, maybe it will never be “finished” but then are we ever really done with IT business solutions?  I have been intending to get started on this topic for quite some time but in my very busy project schedule lately, didn’t have a concise message for a post – but I do have a lot to say about creating and using tabular models.

I’ve added some place-holder topic headers for some things that are on my mind.  This list is inspired by a lot of the questions my consulting customers, students, IT staff members and business users have asked me on a regular basis.  This will motivate me to come back and finish them and for you to come back and read them.  I hope that you will post comments about your burning questions, issues and ideas for related topics to cover in this living post about tabular model design practices and recommendations.

Why Tabular?

SQL Server Analysis Services is a solid and mature platform that now serves as the foundation for two different implementations.  Multidimensional models are especially suited for large volumes of dimensionally-structured data that have additive measure values that sum-up along related dimensional attributes & hierarchies.  

By design, tabular architecture is more flexible than multidimensional in a number of scenarios.  Tabular also works well with dimensional data structures but also works well in cases where the structure of the data doesn’t resemble a traditional star or snowflake of fact and dimension tables.  When I started using PowerPivot and tabular SSAS projects, I insisted on transforming data into star schemas like I’ve always done before building a cube.  In many cases, I still do because it’s easier to design a predictable model that performs well and is easy for users to navigate.  A dimensional model has order and disciple however, the data is not always shaped this way and it can take a lot of effort to force it into that structure.

Tabular is fast for not only additive, hierarchal structured data but in many cases, it works well with normalized and flattened data as long as all the data fits into memory and the model is designed to support simple relationships and calculations that take advantage of the function engine and VertiPaq compression and query engine.  It’s actually pretty easy to make tabular do silly, inefficient things but it’s also not very hard to make it work really well, either.

James Serra has done a nice job of summarizing the differences between the two choices and highlighted the strengths and comparative weaknesses of each in his April 4 blog post titled SQL Server 2012: Multidimensional vs Tabular.  James points out that tabular models can be faster and easier to design and deploy, and that they concisely perform well without giving them a lot of extra attention for tuning and optimization.  Honestly, there isn’t that much to maintain and a lot of the tricks we use to make cubes perform better (like measure group partitioning, aggregation design, strategic aggregation storage, usage-base optimization, proactive caching and cache-warming queries) are simply unnecessary. Most of these options don’t really exist in the tabular world.  We do have partitions in tabular models but they’re really just for ease of design.

What About Multidimensional – Will Tabular Replace It?

The fact is the multidimensional databases (which most casual SSAS users refer to as “cubes”) will be supported for years to come.  The base architecture for SSAS OLAP/UDM/Multidimensional is about 13 years old since Microsoft originally acquired a product code base from Panorama and then went on to enhance and then rewrite the engine over the years as it has matured.  In the view of many industry professionals, this is still the more complete and feature-rich product.

Both multi and tabular have some strengths and weaknesses today and one is not clearly superior to the other.  In many cases, tabular performs better and models are more simple to design and use but the platform is lacking equivalent commands and advanced capabilities.  In the near future, the tabular product may inherit all of the features of its predecessor and the choice may become more clear; or, perhaps a hybrid product will emerge.

Isn’t a Tabular Model Just Another Name for a Cube?

No.  …um, Yes.  …well, sort of.  Here’s the thing:  The term “cube” has become a defacto term used by many to describe the general concept of a semantic model.  Technically, the term “cube” defines a multidimensional structure that stores data in hierarchies of multi-level attributes and pre-calculated aggregate measure values at the intersect points between all those dimensions and at strategic points between many of the level members in-between.  It’s a cool concept and an an even cooler technology but most people who aren’t close to this product don’t understand all that.  Users just know that it works somehow but they’re often confused by some of the fine points… like the difference between hierarchies and levels.  One has an All member and one doesn’t but they both have all the other members.  It makes sense when you understand the architecture but it’s just weird behavior for those who don’t.

Since the tabular semantic model is actually Analysis Services with a single definition of object metadata, certain client tools will continue to treat the model as a cube, even though it technically isn’t.  A tabular Analysis Services database contains some tables that serve the same purpose as measure groups in multidimensional semantic models.  The rest of the tables are exposed as dimensions in the same way that cube dimensions exists in multidimensional.  If a table in a tabular model includes both measures and attribute fields, in certain client tools like Excel, it will show up twice in the model; once as a measure group table and once as a dimension table.

(more to come)

Preparing Data for a Tabular Model

Data Modeling 101 for Tabular Models

Are There Rules for Tabular Model Design?

Tabular Model Design Checklist

What’s the Difference Between Calculated Columns & Measures?

What are the Naming Conventions for Tabular Model Objects?

What’s the Difference Between PowerPivot and Tabular Models?

How to Promote a Business-created PowerPivot Model to an IT-managed SSAS Tabular Model

Getting Started with DAX Calculations

DAX: Essential Concepts

DAX: Some of the Most Useful Functions

DAX: Some of the Most Interesting Functions

Using DAX to Solve real-World Business Scenarios

Do I Write MDX or DAX Queries to Report on Tabular Data?

Can I Use Reporting Services with Tabular & PowerPivot Models?

Do We Need to Have SharePoint to Use Tabular Models?

What Do You Teach Non-technical Business Users About PowerPivot and Tabular Models?

What’s the Best IT Tool for Reporting on Tabular Models?

What’s the Best Business User Tool for Browsing & Analyzing Business Data with Tabular Models?

Survival Tips for Using the Tabular Model Design Environment

How Do You Design a Tabular Model for a Large Volume of Data?

How Do You Secure a Tabular Model?

How to Deploy and Manage a Tabular Model SSAS Database

Tabular Model Common Errors and Remedies

Tabular Model, Workspace and Database Recovery Techniques

Scripting Tabular Model Measures

Simplifying and Automating Tabular Model Design Tasks

A conversation on Twitter earlier this week has inspired me to pose this question…Is Report Builder the “Red-Headed Stepchild” when it comes to the Microsoft Self-Service BI toolset?  Report Builder doesn’t get much attention these days, for various reasons we’ll discuss below.

(Note:  The perspective of this blog entry is Self-Service BI so Report Builder is the reference – however, everything stated is applicable to full-fledged Reporting Services as well.)

Is Report Builder Deprecated?

Nope!  Report Models, however, are deprecated.  As of SQL Server 2012, you can no longer create or update Report Models (SMDL files).  You can continue to use Report Models currently, but it is recommended to move away from them as time allows.  Report Builder itself is at version 3.0 and is a mature product.

Self-Service BI Tools

The primary set of Microsoft Self-Service BI tools includes Excel (+ add-ins for data modeling such as PowerPivot), Power View, and Report Builder.  Some people actually wouldn’t include Report Builder in this list, but I do believe it has a valid place as one of the Self-Service BI tools (albeit, a smaller audience & used for specific purposes).

Drawbacks of Report Builder

• Learning curve / ease of use for report designer.  There are a significant number of properties and options.  This offers significant control over the output – the cost for this significant control is ease of use because all the options in Report Builder can be a bit overwhelming for the casual business user.  However, it’s not overly difficult to use for technically adept users who enjoy working with reporting tools and data.
• Limited interactivity.  While there are some interactive features (such as drill-down, drill-through, sortable columns), each has to be explicitly defined by the report designer.  Report Builder isn’t dynamically interactive like Power View or even Excel – rather, Report Builder is far more suitable for fully formatted reporting needs.
• Longer to develop.  There’s some things that can be done with Power View or Excel that are inherently more work to do in Report Builder.  The first example that comes to mind is hierarchies – with Power View or Excel, you drop a hierarchy onto the row & the navigation up/down works; with Report Builder you’d have to set up what is shown vs. hidden and the toggle properties.  This is not overly difficult to set up, but could be frustrating for someone just getting started with Report Builder.
• GUI support is limited.  There is a drag & drop graphical interface for SQL Server and Analysis Services data sources (plus a couple of others).  This limited support leaves the report designer writing query syntax sometimes – which is obviously not the most user-friendly for non-technical folks.  Currently the nicest way for users to work with Report Builder is using a BISM data source (i.e., the data is stored in Analysis Services or PowerPivot).  SQL Server (relational) can be ok for users to work with if the data sources are made to be understandable & easy to work with (ex: with reporting views or stored procedures) – this takes some effort from the IT Dept. or BI Center to make sure it’s made suitable for self-service.

Positives of Report Builder

• Native connectivity to many data sources, including BISM.  The Microsoft BI framework is very different from other BI tools (such as Cognos or Business Objects) which require a metadata layer – i.e., a report model between the data source & the reporting tool.  Microsoft permits tremendous flexibility here – in fact, you can natively send queries from Report Builder to a variety of databases including non-Microsoft.  Power View is very limited in terms of accepted data sources, and Excel can be somewhat limited (unless you bring the data into an intermediary PowerPivot model first – PowerPivot offers great flexibility in this regard, but do realize you are storing the data redundantly). 
• Significant formatting control.  If you need a pixel-perfect highly formatted report, Report Builder is the tool for you.  Ironically, this is the inverse of the “learning curve / ease of use” drawback listed above.  With Excel you can exercise a lot of control over the look & feel of your report (except Pivot Tables – you have to use formulas if you need to "break” out of the Pivot Table).  Power View has some text size control and some color schemes to choose from, but overall offers minimal user control over formatting (after all, it’s a data discovery tool meeting an entirely different need – and it is purposely trying to remain simple).
• Consistent RDL file format.  If a business user starts a report in Report Builder and needs some help with it, one of the IT or BI folks can open the report using their tool of choice (BIDS or SSDT in Visual Studio), make some updates, and send it back to the user with the file format intact.  The consistent format is also helpful if a report is being promoted from the Self-Service environment to the Corporate BI environment.
• Reusable elements.  To facilitate Self-Service BI using Report Builder, things such as shared data sources (reusable data connections) are obvious but there are others as well.  Shared datasets (reusable queries) can be really helpful to handle common things like Dates and Geography.  Report parts (reusable charts, graphs, tables) can be helpful to display commonly used elements.  Images can also be stored centrally for reuse.  Setting up reusable elements does take some effort from the IT Dept. or BI Center though, but can improve the Self-Service user’s experience tremendously.
• Parameterization.  Reports with a number of parameters (within reason of course) can sometimes be thought of as “guided ad-hoc analysis” because one report can yield many different combinations of results depending on parameter values.  Report Builder handles parameterization well.
• Subscriptions and alerts.  If you wish to have reports delivered to you at a predefined frequency or based upon a condition, then Reporting Services is the tool to make that happen.
• Export and RSS capabilities.  Report Builder can export to many different file formats.  It can also publish an RSS feed – this can be very useful for a business user to consume existing aggregated/calculated data that has been rendered by Report Builder without recalculating or reinventing anything.  Utilizing published report data via RSS also helps with the elusive “one version of the truth” that’s a constant challenge.
• Integration with SharePoint.  With a Report Builder report, you can view or edit the report directly from the SharePoint document library (with appropriate permissions of course).  Alternatively, a Report Manager portal can be used (although it would be used in isolation from other BI tools and reports).

So, even though sometimes Report Builder seems to be the “Red-Headed Stepchild” I very much appreciate having the tool in our toolbox.  I hope it’s alive and well for a long time. 

Got any thoughts on this subject?  Leave a comment … I’d love to hear your thoughts.

Disclosure: The company I work for is a Microsoft Partner

After working on a couple of serious tabular model projects, I’ve been talking to other BI practitioners lately about their thoughts regarding tabular model technology and I’m finding that there is little consensus among all these industry experts about which option to recommend for new BI projects.  At SolidQ, I work with some of the most experienced Business Intelligence experts in the industry.  At conferences and in discussion groups, there has been a lot of discussion about the choice between Analysis Services multidimensional models and the newer tabular semantic model technology introduced in SQL Server 2012.  There are pros and cons for either side but I think there are some important factors to consider.  Among some of the most experienced Analysis Services consultants, I have heard that some are primarily using and recommending tabular for most new projects and others are arguing that tabular offers little significant value over the tried-and-true multidimensional cubes and MDX script solutions.

As is typical for a newer product, the usability experience for the model designer isn’t perfect.  In some ways, it’s different and just may take some getting used to, but in other ways there is clearly room for improvement.  The question now is; do the advantages of tabular vs multidimensional outweigh the somewhat rocky design experience?  I’m not taking sides in this comparison but merely offering an objective analysis of the state of tabular model option in SQL Server 2012 Analysis Services, Service Pack 1.  I expect this product to go through changes so for future reference, this information is for the released product at the time of this posting in June of 2013.

The Good

• The core VertiPaq (or xVelocity) query and aggregation engine is stable & reliable.  Originally developed about five years ago and released with PowerPivot for Excel and SharePoint in SQL Server 2008R2 over three years ago, this technology has proven to be ready for serious use.

• Under most conditions, for analytic reporting, data scanned and aggregated from an in-memory data structure performs faster than other conventional options; including relational and multidimensional storage.

• Tabular can be less complex than multidimensional, OLAP SSAS.  The core design and usage concepts are easier for both those who design models and for those use use them for analysis and reporting.

• Tabular models can be easier & faster to implement because the model structure is simpler and there may be fewer steps in the design process.

• DAX, the core calculation expression language for tabular models, is fairly easy to learn.  Fundamental DAX expression concepts can be easier to understand than equivalent MDX commands used in multidimensional modeling and calculations.

The Bad 

• Comparing features, tabular can be hard to justify when compared to multidimensional.  Traditional SSAS cubes still have more capabilities than tabular models and for someone who already has OLAP skills and background, tabular design is a new learning curve.

• PowerPivot is a version 2 product.  As such, it’s simple and fairly reliable in the majority of design scenarios.  In some, mostly rare, cases, error handling and debugging capabilities aren’t as mature and robust as more tenured technologies like SSAS multidimensional or relational.

• Tabular SSAS is a version 1 product.  As such, it has a list of well-known design annoyances and features that could (and should) improve in the next product go-round.

• The recommended design patterns & best practices, both from Microsoft development and support teams and from members of the expert practitioner community,  are still emerging.                         

• One model.bim file = one developer working at a time.  A departure from the long-time SSAS project development environment where every object (such as data sources, dimensions and cubes) were defined in isolated files managed by the project; SSAS tabular manages all of the database objects in a single model definition file.  The model designer treats the model is more of a black box than a solution comprised of independent objects.  However, the fact is that most of the same objects we work with in multidimensional/cube projects are all defined as XML elements in this file.   It can be opened and properties manipulated,and there are a growing number of third-party tools to provide enhancements.  Regardless, it is one big project file tat would need to be checked-out in source code management as a single project asset.

• The tabular SSAS support community is thriving but still small.  A core group of trusted professionals from all over the world are the loudest voices right now.  They promote tabular solutions and provide active and collective support for one another.

• The DAX expression editor in PowerPivot & the Visual Studio-based SSDT designer is quirky.  You have to know is strengths and weaknesses and be willing to use it in that context.  It attempts to assists with code completion but in the end, it doubles-up brackets and duplicates, rather then replaces old code, that it suggests.  No doubt that the experience will get better as enhancements are developed and released but we must live with a product today that is useful and reliable a lot of the time but, it leaves plenty of opportunity for improvements.

• The entire tabular model must fit in memory.  There’s no caching, swapping or distributed processing option for large models.  This means that very large models need very large hardware and there is currently no scale-out option to distribute work loads or fail-over.  Got a lot of data?  Get a lot of memory.

The Ugly

• After you get into serious data with 30, 40 or 50 tables and some complexity to your model, the version 1.0 SSDT tabular model designer can be cumbersome and error-prone.  I’ve seen it stop responding and throw errors when there were no errors.  I will say that it’s helpful and reliable most of the time but on occasion, when it falls down, I often save and close my work; shut down Visual Studio all together and the fire it back up.

• My biggest peeve about the SSDT model designer is that all design work is performed while connected to the workspace database.  This means that for every table and column rename,and for every single property setting change, this information is written to the model workspace database on the local SSAS tabular server, and after the changes have been committed, control is given back to the model designer.

• Some key SSAS features are not currently supported.  These include things like parent-child hierarchies, many-to-many relationships, cube actions and display folders.  Actually, this is not entirely true; actions and display folders can be added using after-market tools (like BIDS Helper, available in the CodePlex library) and by hand-writing the XMLA script, but they are not currently supported through the SSDT model designer.  There is simply a category of features that didn’t find their way into this first version of the product.  There are work-arounds and methods to enable these capabilities but they’re not supported, or at least not to the the same degree as they are in multidimensional SSAS.

The Beautiful

• There is no doubt that in-memory, tabular model technology is the promise of the future.  It just makes sense.  Several vendors have come to the same conclusion and are developing products following this paradigm.  Oracle just made a big announcement about adding in-memory column store to their future 12C product. 

• Data residing and processed in memory is faster than data residing in disk.  This is what VertiPaq does; whether implemented as PowerPivot, an SSAS tabular model or as a SQL Server column store, it works efficiently and elegantly without the complexities and overhead of indexes, partitions, file groups and other techniques typically used to optimize on-disk data stores.

• Even though tabular is fairly new, many useful & valuable features are supported today and work well.

• PowerPivot models upgrade seamlessly to tabular models.  This provides a path for business users and IT professionals to author models in familiar tools (Excel or Visual Studio) and then promote them to a server hosted environment.

• Tabular models are managed and stored by SQL Server Analysis Services!  Although some components of the tabular engine and the designer are new and still have wrinkles to be ironed-out, the core product is based on the solid and time-tested foundation of SSAS.  This means that many of the features not implemented now will be available in future builds.

• Client applications that support SSAS multidimensional will also support tabular.  In fact, any application built to work with SSAS cubes will natively work with PowerPivot and tabular as if it were a cube.  This is because SSAS tabular uses the same data provider that understands both MDX & DAX queries.

  Disclosure: The company I work for is a Microsoft Gold Partner

A quick look at the whole idea on another weblogs gives you a sense that all of them just talked about very brief things like report refresh feature in BO or cube feature in MS Analysis Service. I choose MS BI and I want to share my reasons and opinions on why I choose it and give you another quick but a little deeper compare on these two Business Intelligence platforms.

As we all know both Business Objects and Microsoft are big companies who are working on BI solutions and both have their own advantages. It’s not true to compare them in term of which one is better, we have to check what is our requirements and then depend on requirements take the decision whether MS or BO. A vision like this could help us relief from religious decisions against a software or technology.

In a BI architect first of all we have the data store level, I mean the storage of the raw data not the stage or olap cubes or universe data source, I mean the first place of our data. This is important to know that where your raw data is and what is the type of storage used to store them. Whether file system or Access or Fox database or a complex database solution like oracle, sqlserver or a web service can made our place of raw data. We have to check our tools against them; check to see which one gives us a smooth way to transfer them among ETL process to destination. So take a look at what Business Objects gives us.

There is a Data Integration platform in Business objects but the problem is that you have to buy that separately because it is not shipped with the BI system. In Microsoft sqlserver enterprise you have all the services and features needed for this part of the game. SSIS is the service that sqlserver deliver for data extract, integration and load. Both product gives you the ability to enhance the data quality and data cleansing portion of your integration phase, but when we down to details things change a little to the Microsoft side, because of the ability of using your Dot.Net knowledge to write complex parts of ETL process you have more room to think and do whatever you want in your process, and in BO side it is always look simple and it’s really not easy to take complex situation into it. There are advantages and disadvantages on this. First you can do many things with the ability of dot.net code but it could give you complexity in your development so you have to decide on your situation, if things looking normal both could fit your need, but if the situation is not stable and you have to make yourself ready for the changes in future it’s better to get the power of SSIS and spend a little more time development today to create a powerful and easily changeable mechanism that could help you in future. You can also do that with Business Objects Data Integration but you have to spend more bucks for the development and changes of ETL processes because development cost in Business Objects solutions is always a nightmare for a project.

At this point we have a brief understanding of differences in ETL process between two vendors, so it’s good time to take a look back to the source database. Here is a very quick answer, if you use mostly MS products to store your transactional data then take your decision and move to MS for a robust and compatible BI platform. Business Objects don’t have a database system and it always used other database solutions to store data for its universe.

So guess what happen ! from an administrator perspective performance tuning is somehow problematic ! since we should use other database systems we should use different technics for each database systems. And this is one of the areas that MS wins the competition because when you use Microsoft platforms there lots of joint mechanism for performance considerations.

Before the SQL Server 2012 we have SSAS with its famous aggregated cubes, because of the nature of SSAS in previous versions we couldn’t call it a semantic layer, here is a little why. A semantic layer provides translation between underlying data store and business-level language(Business semantic that business users familiar with). There was no actual translation in previous release of SSAS. Perhaps we had some difficulties over SSAS to understand for a business user. So Microsoft change its approach in SSAS 2012 from delivering a complex understandable solution to end users to a true semantic layer like what we has in Business Objects that called Universe. So from now MS BI users can use a powerful toolset like Microsoft Excel and use their existing knowledge to interact with semantic layer. What Microsoft do in backyard is to create aggregations in memory so the performance of this approach is really high ! I don’t want to deep dive into what Microsoft do in backyard in this post but it would be one of my next topics. (sounds like advertisement

I talked about aggregations so know that in BO there are no facility for aggregation tables, so you have to deal with DBAs to create aggregation tables manually and integrate them into the Universe.

One of the important aspects of a BI system is the learning curve of the solution, it was always the slogan of the Business Object that learning curve is very low ! yes for end users it is not hard to interact with Universe. BUT ! the thing that I say here is the problem of every BI platform from Microsoft to BO or Cognos that deliver Semantic layer, it is very easy for a user to get the wrong answer, because everything is behind the Universe or Semantic Model and know that tracking from report back to the base data is a Non-trivial task. So be aware about letting users create whatever they want with their own knowledge. There should always an IT professional observing the whole process. So never think about a fully out of the box solution, because you will shortly find it on Mars ! or your users may have the chance to take decisions based on wrong calculations and find their way to Mars again

Another important aspect of a BI systems is the cost of it, about the Business Objects we can definitely say that it is expensive and for sure Microsoft could be expensive ! but how can we decide ' the answer is to compare the detail parts, there are 4 main parts Database, ETL, Semantic Layer and Reporting or user interaction layer. If you choose to go over BO you have to find heads for your data warehouse, database solution and Java skills or tomcat or other J2EE platform professionals for ETL and development phase and BO specific heads for Universe Modeling, Design, Implementation, perhaps you need security administration and if you want to integrate your Active Directory with this platform it is problematic and integrating with other LDAP platforms is a nightmare ! so be aware of these costs. The point of Microsoft solution is that we can use our in house knowledge like Dot.Net and SqlServer, SharePoint, Windows Server and these knowledge are transferable to other skills. But with BO we need headcount dedicated to BO (Universe Design, Implementation, Maintenance, Security) since BO skills are not transferable to other skills, those extra heads blow the project’s budget ! Microsoft BI platform is a more manageable, more secure and less expensive solution, I see the BO as a consultant dream, as an endless font of billable hours

Conclusion

I decide to go over Microsoft BI platform but I would not suggest anyone at first place to choose Microsoft. This is really depend on the nature and scale of the project and what you did and what technologies you have used in past but a quick look gives an idea that Microsoft’s platform is looking more robust and coherent in different parts so it can be a very good and convenient choice and perhaps after the release of SQL Server 2012 and its BI Semantic layer the answer is more easier and acceptable than before.

I also would like to hear about your experience on either of these solutions.

What is our primary use case?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

What do I think about the scalability of the solution?

How are customer service and support?

Which solution did I use previously and why did I switch?

What about the implementation team?

What's my experience with pricing, setup cost, and licensing?

What other advice do I have?

Which deployment model are you using for this solution?

What is most valuable?

What needs improvement?

For how long have I used the solution?

How was the initial setup?

What's my experience with pricing, setup cost, and licensing?

What other advice do I have?

What is our primary use case?

How has it helped my organization?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

How is customer service and technical support?

What was our ROI?

Which other solutions did I evaluate?