By now I assume that you might have already gone through my previous content & have a basic understanding of Power Pivot & how to work with Data Models. Now it's time to take things a bit more up in complexity and work with advanced data models which are commonly used with big data systems & data warehouses.
You might have heard about STAR schema before while working with database systems, but if you are new to this concept then let me explain it in simple terms.
Similar to the data model that we created in my earlier content, STAR schema is also a data model but it is a multi dimensional data model which makes analyzing large datasets very easy. As the name suggests the shape of this model looks like a star hence the name.
At the center of the schema, we would have a FACT table which contains measurable or transactional values. Fact table is connected with multiple dimension tables which contain columns with specific business entities or attributes.
Requirement
Create a data model utilizing the below dataset and then create a single pivot which has the ability to utilize all the dimensions & values from all the tables.
Data Used
https://docs.google.com/spreadsheets/d/1cWhRVeutG5qEICj88KkafcLxtI7KOsVCsIsVMeYHUus/edit?usp=sharing
Understanding Fact & Dimension structure
Now in this scenario, we already have tables defined as Fact & Dimensions, however in many scenarios, you will be given raw unstructured data and will be required to create the Fact & Dimensions from that single source. Usually we create this structure to attain 3 basic advantages
Splitting the data makes it easier to understand & maneuver through the data
This type of structure is best suited for OLAP models, i.e. faster processing on large datasets
Remove data redundancies occurring due to duplicate dimensions
Let’s create the STAR schema
Import all the data into Power Pivot. Now if you are not familiar with this process then go through the below link for better understanding
Once imported, go to the Diagram view of the Power Pivot editor and start joining Fact table keys with keys of dimension table
Once done, click on the Pivot Table button on the Home tab in Power Pivot editor/studio
In the next prompt, select New Worksheet & press Ok
Now the pivot table has been created which connects each dimension table with the Fact table
That’s it, now you can play around and analyze the data based on your requirements. Stay tuned for more !
Comentarios