In our case, sometimes it is much easily to export some tables from database table and work in Excel to generate a quick pivot report.
Here I demonstrate a simple data modeling using Microsoft Power Pivot.
Lets say I have a list of Malaysia property transacted price, but they are stored in different relational database tables as Transaction Fact table and 4 other dimensional data tables, namely State, City, Address, Condo.
I export them out from our database and store them in the Microsoft Excel file as below:
2 files: 1. excel files with 4 dimensional data
2. transaction fact file in csv format
So first we import the fact file (csv) into the the powerpivot:
Next copy the dimension worksheets and paste on power pivot:
Now create the relationship between the tables:
If you look at the Diagram view it should look as below:
From there start building a pivot reports as below:
Now you can manipulate your pivot report as how you want to present the data meanwhile the report audience still maintain a certain flexibility in drilling down the data.
It was so nice article. I was really satisfied by seeing this article. Power BI Online course