Microsoft SSAS vs tabular (powerBI)
Lately, several clients have been curious what the difference is between PowerBI and Microsoft SSAS. These points are basic guidelines for business users, and not technical users. MS BI Blog has a more technically geared analysis of what your options are of putting BI in the cloud.
Microsoft SQL Server Analysis Services (cubes)
Microsoft SSAS has been around for long time, and is generally implemented for large BI projects where the requirements are clear and consistent.
- Cubes are simply another type of database.
- Optimal for very large datasets, like a financial system with a very large amount of transactions.
- Best for prescriptive reporting tasks, where the reporting tasks are known and not likely to change frequently.
- More effort required to change the cube, as changes require more steps to implement. This means the technology is less suited for iterations.
- MDX is the language used in the cube to perform calculations. It has a very steep learning curve for the average user, business users do not typically write MDX.
- Assumes standardized dimensions. (A dimension is something like date, facility type, an asset class)
- Precomputes aggregations based on the dimension. This is why dimensions are relatively static.
- Available in the cloud as an Azure service, this does entail additional infrastructure costs.
Microsoft PowerBI implements a tabular data model under the hood. It is effectively using the same database engine as SSAS, and provides a tabular model for PowerBI to leverage.
- While the tabular model does not pre-aggregate as a cube does, the near unlimited computing power in the cloud makes that irrelevant for most use cases.
- PowerBI can pull data in from just about every data source and combine them in the same model for the user. This includes sourcing data from SSAS, or cubes from any other vendor.
- Tabular model allows for quick iterations, shorter changes to model.
- DAX is the language used in the tabular model. It’s an easier language to learn, and is very similar to excel. It’s also easier for end users to learn.
- Flexibility with dimensions. This is important, as you may not always know how you want to slice and dice your data at the outset.
- Easier to manage complicated formulas, for example, production capability (average of top 10 days production per well per month, compared to the following month)
- Tabular can pull data into the model (in-memory), or can go to the data source directly (Direct Query)
- PowerBI supports moving the tabular model to a server on premises, such that the solution is flexible.
- Very cost effective – The business gets to visualize their data at a cost per seat with PowerBI. There is a limit (10 gig) to the size of the model, but computationally expensive tasks don’t cost more. Since you don’t have to pay for computing power and memory with powerBI as you do with Azure, you’re basically getting a free ride with powerBI.
Capabilities available to both platforms:
- Both the tabular and cube models can be leveraged as powerpivot models in excel.
- Both solutions drill down, have hierarchies
The conclusion to be drawn here is that there is a lot of optionality in the platform. From a database point of view, the reasons to ditch SSAS are greater now that powerBI and the tabular model have come along. SSAS never truly made inroads into the market as it’s a cumbersome solution to implement, and its Time to Value is relatively poor. That’s why most of the industry stuck to creating DW structures in SQL, it was faster to implement and deliver value. The same will be true of the tabular model in Power BI.