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.
