To consolidate the knowledge during the journey to learn data warehouse I spent time learning about Microsoft Access using Star Schema, an essential point is to create a database by yourself, create relationship between tables, connect to Power Pivot and see what could happen.
Firstly, I studied about the Star Schema, it’s a concept to create database. Basically in the Star Schema design, a single object (the fact table) sits in the middle and is radically connected to other surrounding objects (dimension lookup tables) like a star. Each dimension is represented as a single table. The primary key in each dimension table is related to a foreign key in the fact table.
Based in the Star Schema concept I created the database using Dimensions Tables and the Fact table.
And finally the Star Schema of this database:
OBS: Talking to Cody, he told me about the Dim_Time, actually that table is a Dim_Date table because there are Date information and not Time, this is a simple example of database to learn, if you want to know more about time and date tables in Star Schema, look these links:
Connecting to Power Pivot
In the previous episode is registered the connection between an Access database and the Power Pivot, if you have any question about it, just go there and take a look.
After connected, you’ll see a table in Power Pivot and you’ll be able to manipulate it.
BONUS: Power View
Now it’s time to use Power View, it’s an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting.
You click in the Insert tab, then in Power View and start to build your reports.
You select the fields and information that you want to see in the report/dashboard. Be creative!
Do you see how easy it is? I’m really excited to learn more!
Now you learnt how to create a database in Microsoft Access using Star Schema. You can play around and do many systems with these tool, as much as you practice, more you learn, more opportunities you have.