If you use SQL Server Analysis Services as a data source for Power BI, you have two types of connection; Live Connection or Import Data. If you use Import Data, you can select columns and measures to be imported into Power BI. However, what if you want to add more columns to it, or edit something? In this short blog post, I’ll explain how you can do that in Power BI.
Import Data from Analysis Services
Usually, SQL Server Analysis Service (or SSAS in short) used in scenarios that there is a shared data model built for report visualizers to build reports on it. In Power BI, using a specific type of connection called Live Connection you can connect to an SSAS model and get all the columns and calculations. However, this type of connection comes with some limitations for power users and self-service users. One of the limitations is that users cannot add new columns and some types of calculations, they cannot also combine the data from SSAS model, with some data they have in other sources such as Excel file. The solution for flexibility and self-service usually is the Import data from the SSAS model. This is an option that you can see when you get data from Analysis Services;
When you use the Import mode, you will be able to select all columns and measure you want to be loaded into Power BI;
You can then use the extracted data in Power BI to build visualizations;
Adding more Fields or Editing Fields List
What if after the initial selection of fields, you realize that you need a few more fields to be added, or remove one of the fields that you don’t need anymore? where you can do these modifications?
Power Query is the place that you can do all these changes. you can get there using the Home tab, and Transform Data.
In The Power Query Editor, there are three places you can add fields or edit the existing fields. One of the places is at the Added Items step in the query with clicking on the Settings icon;
Another way is to use the Add Items from the Cube Tools > Manage menu
and one other way is to use the table selector, and choose the Add Items from there;
Any of the methods above will get you to the window to add more items (which can be columns or measures) to the list:
Now you can add all the fields that you need (be careful to not combine unrelated fields to each other, that might create a cross join scenario).
Choose the right type of connection
Please note that I am not saying that you should be using Import Data when you use SSAS, and also I am not saying you should be using Live Connection. You have to choose the right type of connection based on your need;
- if you are a report visualizer and have all the columns and measures you need, and just want to visualize it, then use Live Connection.
- If you are a self-service data analyst and want to add extra analytics by adding more columns or bringing more data from other sources, then use Import Data.
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.