I recently was working with a customer implementing a data warehouse and SQL Server Analysis Services (SSAS). The customer was very familiar with Microsoft Power BI and had previously went to a conference to learn more about it. The customer was telling me that during the lecture he was in, the speakers talked about how Power BI was more than sufficient on its own and there was no use for Analysis Services. They asked me why the instructors would advise against using Analysis services, especially after seeing firsthand how it had made their life easier. After a discussion on when and when not to use Analysis Services, I thought I would write a blog post to elaborate on it.
The short answer is, for many situations Power BI is more than sufficient for creating reports without the need for Analysis Services. Power BI has a great data modeler built into it that can do some pretty complex modeling. It also can create calculated columns and measures like SSAS does. You can even create custom date tables for reporting data against a date dimension. In a sense, it can be used as a self-contained data warehouse.
The longer answer is, there are a few key areas to take into consideration when selecting a solution to meet your needs. You should consider the complexity of the data load, shareability of the reports, and security of the reports and the data in them.
Power BI is a great tool for aggregating data sets together to be able to analyze them visually. It also has the ability to load data from multiple data sources. Once loaded, you can manipulate the data models to relate the data and create some complex data models. When loading the data sources, it is important to remember that Power BI is importing that data directly into the report. This means, that if you import 100 MB spreadsheet of data, all of that data is going to be added inside of your report.
Reports can become quite large which means simply sharing a pbix file does not become very practical. You will have to rely on either Power BI Service (Power BI in the cloud) or Power BI Reporting server to host the reports and data models to be able to share your reports.
Once your reports are on a platform, where they can be shared, then you will have to worry about who can access these reports. Some companies have stringent guidelines about what data can be shared around the organization. Tools such as Power BI can make this harder to lock down, especially when the organization is giving users access to databases for analysis reasons. This opens many security and auditing concerns.
SQL Server Analysis Services alleviates many of these concerns. It also presents a new set of challenges that must be planned for. An organization should consider the following information when decided whether or not to implement Analysis Services. The final outcome should be based on the organization’s needs and requirements.
Let’s start by providing more information about what SQL Server Analysis Service (SSAS) is. SSAS is a service that is provided with SQL Server as an optional service to install. While SQL Server hosts traditional relational databases, SSAS instead hosts objects called OLAP (Online Analytical Processing) cubes. These cubes in a way are miniature multidimensional databases specifically designed for querying. They do possess some key differences from traditional relational databases that should be mentioned.
First, OLAP cubes are multidimensional so when they are queried, they return results in tuples instead of a two-dimensional table. There is also a somewhat hybrid model called a Tabular cube. Since these cubes have different structures than a normal relational database, they require a special query language to query them. Instead of using SQL for querying like relational databases do, they instead use a either DAX or MDX for queries depending on what kind of OLAP cube you are querying.
The exciting aspect is SSAS can be set up in different modes that support different functionality. The main modes are Multidimensional, Tabular, and Power Pivot. Each mode comes with specific feature sets. A lot of the features are shared across the different modes, but there are some features that are specific to a particular mode. You should plan out what mode you are going to install your SSAS instance in as it determines how your cubes are developed and features available to your users. Multidimensional cubes can not be ran in Tabular mode. Likewise, Tabular cubes can not be ran in Multidimensional mode.
Now that we have a little background on Analysis Services, let’s take a look at how Power BI utilizes the service. One of the first things most people notice when connecting up to Analysis Services, for the first time from Power Bi, is you can use live query or import when connecting to the data source. The important thing to note here is when using the live query, the data is not imported. The data is instead queried from the source each time you change a visual.
Since the data is not imported into the report, the reports stay very small and are easier to share. This also means that when you are working with a report, it has to have access to the OLAP cubes to be able to query for the data.
When using live query, you will notice you will no longer be able to modify the data model inside of Power BI. All of the data modeling is done through SSAS. Without the data being imported into the report, Power BI doesn’t have a way to model it.
The most interesting point is how this allows the corporation some advantages. Using live query permits us to add some security measures in place. When querying against a SSAS server, users are authenticated using their Windows or domain credentials. This means that if someone who is not supposed to have possession of a pbix file and yet is still able to get a hold of the file, they will not be able to query data from the report unless they are granted permission in the cube to access that cube.
To take this further, not only can we revoke permission to the cube, we can limit a user’s ability to see certain information in the cube, even if they have access to see the cube. As mentioned above, SSAS uses Windows Authentication to check who is browsing the cube. We can then use the user’s information to filter the results at the row level (or cell level depending on the OLAP mode) based upon the user who is browsing the cube.
An example, where this would be beneficial, is if you oversee creating sales reports for twelve salespeople. All twelve salespeople want to see the same exact report, but they are only allowed to see their own sales. In order to do this in Power BI directly, you would have to filter the results of the report to a particular salesperson, lock down the report to where they can’t change the filters, and send them a copy of the report. This would have to be done 12 times, once for each salesperson.
In the future, if you had to make an update to the report, you would have to repeat this process each time for each salesperson. With SSAS you avoid all the extra steps. Considering the OLAP cube is set up correctly, once you create the report and send it to the intended users, they will see only their results. You do not have to go through the steps of publishing multiple filtered reports.
To further address security around the data sources, using SSAS allows you to add a layer of security between your data sources and your users. Instead of allowing users to directly query your production database(s),which can cause performance issues and table locks, they now have a dedicated source to query that is designed to more efficiently provide that data. By doing this you are not only preventing querying of your production databases, but you can completely terminate access to your production databases from users querying it directly. This is extremely useful in organizations where access to data sources are audited such as publicly traded companies.
Next let’s approach this from a data consistency standpoint. Since SSAS is a centralized data source, everyone who consumes data from it will be seeing the same data without syncing issues. It also allows you to create calculations or measures. This allows everyone in the organization to use the same calculations when calculating things such as sales margin or year over year sales.
There are many more perks to implementing SSAS, but let’s address some drawbacks. SSAS is not as user friendly as Power BI when it comes to importing and modeling data. It relies more heavily on database data structures so you will need to have someone with the skillsets necessary to implement SSAS, a database of some type such as a data warehouse, and implementing an ETL process for importing data into that database from disparate data sources.
This extra infrastructure also means extra costs for cloud hosting or investing in servers and software licensing. Since licensing of software is involved, it is recommended to work with someone who is familiar with the licensing models of these Microsoft products.
Setting up SQL Server Analysis Services can be a large task, and is not suitable for all situations. For many situations, just utilizing the built-in tools in Power BI will be more than sufficient and will meet the needs of many users. However, SQL Server Analysis Services is in my opinion a mainstay that has proven its use for well over a decade. There are times when you need more than just the capabilities of Power BI. If you have reached the point where you think you are needing more than Power BI can provide alone, contact us and let us help you get the most of your reporting solutions.
Competitive Edge
Please contact us to explore how SDP can leverage this technology to create a competitive edge for your business.
About the author
Kevin Williams is a Senior Software Engineer and Power BI Practice Lead at Software Design Partners. He has a Microsoft Certified Solutions Associate: Business Intelligence Design certificate and focuses in business intelligence and .NET development.
.jpg)
