Data Modelling using Direct Query

[Ejykso]

Hi All,

I have a requirement to use direct query for my Power BI project . The data resides in our SQL database and they are all transactional data. How do I implement dimensional modelling for this data in the database without bringing it to power BI as direct query does not allow any data modelling in Power BI.

Thanks

[mahenkj2]

Hi @Ejykso 

I am not very clear on your comment 'as direct query does not allow any data modelling in Power BI.' Though I understand that transformation in PQ have some limitations, but at some extent you can do it. Based on my limited experience on direct query usage, it is better to things sort out at source level and keep little for PQ/DAX. 

Then as such dimentional modelling is not a challenge. You can create dimension and fact table at source and use either 100% direct query, or import dimension tables and use direct query for fact tables only.

You can refer further documentation of Microsoft or ask specific questions here.

Hope it helps.

[Ejykso]

The problem is how do you create the dimension and fact table from transactional data at source and create the appropriate relationship between the tables? 

[mahenkj2 · Accepted solution]

Hi @Ejykso 

I would give a short answer for now. Suppose, you have a transactional data table in your source data. It may or may not be of the format conducive for a reporting dimensional model.

• When you wish to do everything at power bi, you can connect to that transaction table via direct query, and transform the table as needed.
• You can reconnect to that transactiont table again via direct query and transform such as to work as dimension table. or you can reference the query of above step again as well.  I usually prefer to create a separate dimension table in data source, and then import or direct query that. If you have no access to data source, you can create dimension tables in excel and import in power BI, else create ssis packages to transform fact tables to diemsnion tables to keep them dynamic. There are certain dimensions which are more or less static in long term, like machine numbers, they will be changed only when machines are added/removed, these can be created in excel files and added in power bi model. Incase of change, update excel table. 
• Certain dimension, like product ID keep adding in database, can be created as dimension table by using data source fact tables, by transforming them suitbly.
• So depending your situation, you need to decide suitable strategies.
• Microsfot have provided extensive guidance on this topic, please see link below and in the left hand index, you may go through indepth detail:

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Hope it helps.