Using SQL view as a source or building a model from this view

[m4xon]

Hello,

I have a question related to the best practices. Very often I receive a prepared view from a client to use to create report. 

Of course we are talking about not too large reports consisting of up to 5 pages. SQL views consist of more than 20 columns and more than 10 000 rows. I have there everything I need to build measures and visualisations. In such a situation, should I pitch the view to fact and dimension tables, creating relationships etc like in normal snowflake model or stay with the table I have? I guess that it depends of the project, but what will be the best practices? Often I stay with the sql table and I create in PQ kind of dim tables of categories or other things that I would use for example in slicers, but I don't know if it is a good approch, so I would be grateful fore all comments and advices.

Thanks

Max

[v-stephen-msft · Accepted solution]

Hi @m4xon ,

It is generally recommended to create a proper data model with fact and dimension tables, and establish relationships between them. This approach is known as the star schema or snowflake schema.

In a star schema, the fact table contains the measures (such as depot) and foreign keys to the dimension tables. The dimension tables contain the descriptive attributes (such as categories) that are used to slice and dice the measures.

By creating a proper data model, you can take advantage of the benefits of Power BI such as filtering, slicing, drill-down, and drill-through. You can also improve the performance of your report by reducing the amount of data that needs to be loaded and processed.

However, if the SQL view provided by the client already contains all the necessary data and relationships, and the report is not too large, it may be acceptable to use the view directly. In this case, you can still create additional tables in Power Query to use as slicers or filters, as you have mentioned.

In summary, the best approach depends on the specific requirements of the project, the size of the report, and the complexity of the data. If the data is complex and the report is large, it is generally recommended to create a proper data model with fact and dimension tables. If the data is simple and the report is small, it may be acceptable to use the SQL view directly.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Idrissshatila]

Hello @m4xon ,

for a better performance in Power BI, Make all the transformations in SQL As views and then just use Power BI As a visualisation tool.

also make sure to build your model as star schema https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[m4xon]

Hi, so you mean that after importing the view I should convert/rebuild it to a star schema?