Smart Table Structure for Embedding Power BI
- Rachel Thomas
Users can embed any Power BI report in a CXO dashboard. However, to make the drilling capacity more efficient, it is good to set certain table structures. This is especially recommendable for use cases where embedding Power BI reports is used to expand the data that is in the EPM systems reported on in CXO. If Embedding Power BI is used to show additional data (e.g. project data) then this table structure can still add efficiency, but it might have limited impact depending on the data structure.
It is recommended that users set up one table with all the actual figures, and then place the filters on the data of this table by using support tables. By changing the POV in CXO, you will be able to filter the data as required. This feature can work for one to one (1:1) mapping and filtering as well as one to many (1:m).
An example to show how the recommended data model works:
Table “Sales_data” in the middle holds all transactions. If we send an entity from CXO, we use it to filter the ENT_hierarchy table in Power BI by matching it to Parent_ent. Then, within Power BI, we send the remaining Member_ent member(s) to the Sales_data table. As a results the Sales Data table gets filtered on the basis of these member(s), and the filtered data is shown in CXO.
Similarly, you can set up tables such as “ACC_hierarchy”, “Channel_Hierarchy”, Customer_hierarchy and “Period” to filter the transactions based on other CXO Dimensions. For Period, it might be recommended to combine multiple CXO dimensions into one dimension. For instance, Year, month, and view.
Refer:
Create and manage relationships in Power BI Desktop - Power BI
Interaction between CXO and Power BI filters