Reply
Regular Visitor
Posts: 15
Registered: ‎04-13-2018
Accepted Solution

Moving Power BI Analysis into SSIS or SSAS?

[ Edited ]

Hello! I hope my question isn't too off topic.

 

I've been working with Power BI for a few months to build reports and it's helped me gain traction regarding our need for a more robust data warehouse and BI solution. I'm reading The Data Warehouse Toolkit (Kimball) and I'm looking into SSIS and SSAS, but the problem that I have now is that I have a number of DAX statements that produce very useful calculated columns in Power BI and I'm not sure how to migrate them into an ETL solution to "bake" the results into a data warehouse.

 

For example, I have a fact table that contains a record for each time a specific robot moves a box. Sometimes this robot moves two boxes -- this represents two records. I have added calculated columns in Power BI to determine properties such as

  • If the robot moved two boxes, were they the same item? Did they go to the same destination? (complication: inspecting the same dataset)
  • If the box is part of an order, is this the first time the box has been moved since the order was released? (complication: inspecting multiple other datasets)
  • Did this box come straight from production, from area X or was it a return? (complication: inspecting multiple other datasets)

And so on. This fact table has become very wide (which probably indicates that the dataset is potentially not designed well; hoping the book will help me flush that out), but putting that aside, I have complex DAX statements that would be difficult to reproduce in queries to the operational database.

 

My goals are to reduce the complexity of the Power BI report, remove the coupling between Power BI and the operational database, and I want to have snapshots and running transactions of these calculated columns.

 

Considering all of that, I believe that should remove these calculations from the Power BI report, capture them in an ETL process to migrate the data from the operational database to a data warehouse, and then start a new Power BI report using the DW. But I'm not sure if SSIS or SSAS would support the level of transformation that I need.

 

Do you have any advice? Do you think SSIS or SSAS would be appropriate to apply these sort of calculations? From my initial investigations, it looks like SSIS only supports basic transforms.


Accepted Solutions
Regular Visitor
Posts: 15
Registered: ‎04-13-2018

Re: Moving Power BI Analysis into SSIS or SSAS?

A quick follow up for anyone else in my shoes who comes here later.

 

After doing a lot of reading, it seems like the current recommended practice is to use SSIS to perform ETL and SSAS for analysis, then you can connect Power BI or another front end into SSAS.

 

SSIS performs very basic ETL, such as standardizing gender values, converting dates to UTC, and compositing data from multiple sources. No DAX analysis.

 

SSAS contains the DAX analysis and dataset designers that you're familiar with from Power BI. Use SSAS to do your calculations and measures.

View solution in original post


All Replies
Regular Visitor
Posts: 15
Registered: ‎04-13-2018

Re: Moving Power BI Analysis into SSIS or SSAS?

A quick follow up for anyone else in my shoes who comes here later.

 

After doing a lot of reading, it seems like the current recommended practice is to use SSIS to perform ETL and SSAS for analysis, then you can connect Power BI or another front end into SSAS.

 

SSIS performs very basic ETL, such as standardizing gender values, converting dates to UTC, and compositing data from multiple sources. No DAX analysis.

 

SSAS contains the DAX analysis and dataset designers that you're familiar with from Power BI. Use SSAS to do your calculations and measures.