Use Power BI with existing Data Warehouse?

[wembleybear]

We are considering a move to Power BI as our current product has poor training resources and our end users are finding it complex. At the moment, we have a SQL server Data Warehouse that loads data from our ERP system each night. The data has been validated and we are happy with the DW itself. In the DW there are several tables from which the Dimensions and Cubes in the BI system are populated.

Being a relative newbie to BI in general, my question is around how easy it would be to use the DW tables we currently have in Power BI. Appreciate that it is difficult to give an answer as you obviously have not seen our DW but the structure is consistent across all tables. Here is a screen shot of the table which is mapped to the Asset cube in our BI:

[v-yulgu-msft]

Hi @wembleybear,

Power BI desktop provides "SQL Server database" connector that allows us to fetch data from SQL Server. You can refer to this link for more detailed description:

Working with SQL Server data in Power BI Desktop

Best regards,
Yuliana Gu

[wembleybear]

Hi,

Yes, I know it is possible to do that. My question is around the actual structure of the data in the SQL DW. Currently, the data in our DW is optimized for a multi-dimensional BI platform as shown in my original post, and the data has been significantly transformed to facilitate that.

There are tables to feed the cubes, and then tables that feed the dimensions and attributes etc.

Would this be a problem for Power BI to interpret when it is structured this way?

Thanks

Martyn

[TomMartens]

Hey,

basically each DWH that contains Dimension tables and Fact tables, specifically desgined to meet the requirements of SSAS Multidimensional will fit the requirements of Power BI, for a couple of reasons I would try to use the relational tables even if this will lead to a rewrite of MDX calculations now using DAX.

There are two exceptions of the above mentioned:

• ragged hierarchies and parent/child dimensions, there is some kind of improvement in the SSAS Tabular model, but Power BI still has its difficulties to visaulize such structures
• many-to-many relationships between dimensions and facts, this now is "natively" supported in SSAS Tabular and Power BI, it can become difficult to migrate, the more complex these many-to-many relationships are on the SSAS MD side

So from the information you revealed: Go for it 🙂

If you use DISTINCTCOUNT measures on the SSAS MD side you will be fascinated how fast this revealing "aggregation" type can become.

[wembleybear]