We DSAU25 have been fortunate to learn about Power BI, Query Editor, Data Modeling and DAX so far from our expert instructors namely Rachid, Grace and Ross. In this blog post, I am going to talk about some best practices to use Power BI effectively that I have learned from them so far.

Best Practices for Data Transformation

  1. After connecting your dataset, it is a good practice to explore and have a look into the datatypes, column names, query names, etc. This is an opportunity to rename any misleading columns, change table names and wrong datatypes.
  2. All these applied steps will be given default step name. However, if we want to go back to our previous state in future to check how we might have manipulated the data, we should keep a habit of renaming our steps. Also, providing additional description will give us an extra context in future.
  3. Grouping the related tables is another way to help us organize our work.

Best Practices for Data Modeling

  1. Identify and separate your dataset into the dimension and fact table.
    • Dimension Tables:

      They are also known as reference tables which contains information that describes the data. For example: Product table contains information about product such as product name, color, size, etc. And, employee table will give you information about employee name, hired date, role, so on and so forth. They help in filtering or grouping the data in your analysis. Therefore, this kind of table is broad in nature.

    • Fact Tables:

      They are also known as transactional tables as this table is more about an event occurring. For example: Sales data, sales is happening at a point of time between salesperson, customer and products. Another example would be regarding doctor appointments, its an event and some patients are coming at a point of time. Fact tables helps us to analyze measures such as sales amount, profit, loss, etc. and they are pretty long and have huge amount of row data.

    • Separating our dataset into these tables will help increase processing time in Power BI.
  2. Organize your fact table and dimensions table using star schema approach.

    Star schema model resembles a star with the dimension tables as the points of star and fact table in the center of the star. Generally, dimension table will have one to many relationship with fact table and arrows flowing to the fact table. This means the primary key of the dimension table will be aiding to filter the measures in fact table. Therefore, you will notice that the fact tables will have more foreign keys.

  3. Create a separate table for all your measures.

    It is good practice to create explicit measures, base calculations through DAX and keep it organized in separate table. Even better if you could find a way to
    organize your measures into respective folders such as all revenue related calculations in a revenue folder or, all your % calculations and # No. calculations in another. This will make your job more efficient and productive. You provide folder names to your selected measure by navigating to  the model view in the properties panel.

  4.  

4. Provide proper indentation and comments if your DAX calculation is complicated.
5. Give context to your tables, columns and measures as needed in description box.

These were some of the things I learnt to do well during these past week and I hope you found it helpful as well.

Till then, adios! Keep learning and growing!

#powerbi #dax #datamodeling #datatransformation

 

Prerana Amatya
Author: Prerana Amatya