During my evaluation of Power Query as a reporting engine I wondered why we should keep detail fields in our cube at all if the preferred output is a flat table anyway. Cubes are meant for aggregation, aren’t they?
Especially in the Finance- & Accounting area you will come across many cubes with detail fields because sometimes you simply need to perform analysis on ledger entry level. But this seems like a loose/loose scenario in my eyes: Not only do these detail reports often perform badly, their biggest negative impact might lie in the fact that they cause the fact tables to be x-times bigger than the next aggregation level, thereby decreasing the overall performance of the cube.
So how about this approach then: Use Power Query for your reports on detail level: Directly connect to your fact table in the DWH and merge to your SSAS-data in order to retrieve the attributes/filters only. Or keep your fact tables in a dedicated DB if your DWH serves other purposes as well and you fear the performance impacts of those queries.
So this would leave the cubes’ fact tables with much less data -> improving performance.
I tried some scenarios that worked fine. But putting the fact tables into a separate tabular model instead of a relational DB performed quite badly.
Does anyone have experience with this approach? If you know someone who might, please forward.
What do you think about this approach, any other obstacles I’ve missed?