Hi Team,
We are connecting Power BI Desktop to an ODBC data source(OBIEE) via BI Connector.
We are trying to join a fact table with the dimension tables (star schema) under 'Many-to-1' relationship and created a new measure (with aggregation) in the fact table.
We had created a visualization (table format) with the columns from the selected tables and the created measure.
With the results returned, the added measure does not correspond to the values with the filters applied (incorrect data is shown).
We are getting two queries instead of one (the added measure is given by Power BI as another query), even though the tables are joined. This occurs only with the aggregations.
Steps to reproduce the issue:
- Connect to an ODBC data source via BI Connector. Use 'DirectQuery' mode for the connection.
- Choose a fact table and 2-3 dimension tables.
- Navigate to 'Manage Relationships'. Create 'Many-to-1' relationship between the fact table and the selected dimension tables with the 'Cross filter direction' as 'Single'.
- Create a custom measure under the fact table with the aggregation for a column in one of the dimension tables. e.g., Measure1 = SUM(DimTable1[Column1])
- Choose dimensions from different tables and add the created measure (from the fact table) to a visualization.
- Apply filters to the column(s) added from the dimension tables.
The results seem to be different as Power BI sends the queries separately for the selected columns and the created aggregate measure. But the same setup created in OBIEE generates the required data (with a single query).
Please let us know if you need further information.
Regards,
BI Connector Team.
