Replicate data model

[mstefancik]

Hi,

is there any way how to replicate 1:1 database model in Power BI?

With all the relations, primary keys as well as foreign keys.

How to properly create the model? Schema is relational DB. Neither star or snowflake.

Correct me:

To provide all users ability to analyse data with a drag and drop method  without need to know database schema, it should be star schema.

Once relational schema is created, users need to know data model schema, as they need to drag proper columns into the charts.

Am I right?

Anyway, how to create replicate database schema in Power BI desktop?

[ankitpatira]

@mstefancik There is an easy way to replicate your relational database model in powerbi desktop.

First time you connect to your relational database you need to choose connection method as DirectQuery. This will not bring your data into powerbi desktop via Import but connect live to your database and you will have all FK and PK keys maintained. Once you save your pbix file then in bottom right hand corner you will see option saying 'DirectQuery enabled click to change'. Click on that and change method from DirectQuery to import. This will enforce powerbi desktop to import all your data in but maintain your PK FK relations.

[mstefancik]

Good hint, thanks. Problem is that I can not connect directly to SQL server DB as it is only backend layer of ERP system, which is under control of 3rd party vendor. So they only extract tables I need from DB and import them to newly created DB. There is place I am connecting to. So then I need to recreate model manualy in PowerBI desktop, which I can not achieve somehow.

[ankitpatira]

@mstefancik Unfortunately you will have to create it manually in PBI desktop then. Things I've learned from doing it multiple times is to ensure cross filter direction is alway set to Single for all. This will let you create active relations for all the tables. 

[Anonymous]

Hello @mstefancik

Power BI is not really designed for analyze a relational DB schema. You're rigth, it really better to build a star schema.

But as soon as you did it, if you give semantic label to your table and field, the end-user don't have to know the schema underneath.

If you want to replicate the relational DB schema, in Power BI desktop, go to "Get Data", configure your data source, and retrieve the table you want to use. If the relationship between table as defined in your schema, Power BI will automaticaly build them, otherwise, you'll have to do it by yourself (drag & drop).

Sebastien

[mstefancik]

I understand how to create model, the problem is how to correctlz replicate foreign keys.

Specifically:

I am trying to replicate MS project 2013 on premise schema as I do not have OLAP created above it.

Typical issue. I want to filter various tables for certain year pull from time set table.

I do not know how to achieve, it filters all parts i need as they all come from various tables. projects, tasks, assignments, tiemsheet etc.