Forum Discussion
Microsoft EmployeeShare your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)
Advocate IIThis looks great. I think our business users will be very happy not to have to rebuild what we already have in enteprise BI just to add a couple of their own tables to a report.
I do have a few usability questions regarding composite usage with an enteprise model.
1. Surrogate ID's - If the enterprise model is based on surrogate ID's, and a user wants to add their own data (from Excel, for instance) and relate it to existing dimensions, will they have access to the model in Power Query in order to merge the surrogate key into their data? How else will they be able to make the join in their new composite model?
2. Conformed Dimensions - Coming from SSAS MD, we used to have conformed dimensions, a single instance of which could be shared across multiple cubes. Power BI doesn't have anything like that, so dimensions get repeated in many different models. If two models are brought together in a composite, and both have a "Company" dimension, for instance, what's the right way of making sure each model's measures get filtered by its respective "Company" dimension?
3. Refresh - We have a large enterprise model where individual tables are refreshed at different times using XMLA executed by SSIS. This works but isn't great, the Power BI Service timestamp doesn't show that the model has refreshed to the user, we can't see in the Service when the tables are refreshing, and the different XMLA queries end up waiting on each other to finish instead of executing in parallel. Would composite models be a better solution? In other words, could we split our current model into "marts" that refresh independently and are then joined together in a composite?
Great work Power BI team!
- jeroenterheerdt
Hi, you should be able to use PQ to create your surrogate IDs on Excel, just like normal. Re: #2: you can import the Company dimension once and relate it to the other tables. Re #3: not sure, sounds like an interesting scenario.
- nicg
Thanks for responding.
1. What I mean is, assume we have a published Power BI shared dataset where the model makes relationships using surrogate keys, and a user pulls that into Power BI Desktop as a composite model. Then they add their own data from some other data source. Presumably, that other source will have no knowledge of the surrogate keys. How can relationships be created in this scenario. I ask because surrogate keys are a common dimensional modeling technique.
2. Does that mean we can pull in selected tables from composite models? I was thinking we had to import the entire model. In which case, you'd have multiple company dimensions making it difficult to "slice" the data.
- jeroenterheerdt
Sorry for my late reply, I missed your response. 1) You would need to add those surrogate keys to the other data source as well. 2) No, you have to import the entire model, but you don't have to show all of the tables to the user nor relate all of them.
