Simple data model and quick pivot report using Microsoft Power Pivot

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.

Related Posts

One thought on “Simple data model and quick pivot report using Microsoft Power Pivot

Comments are closed.