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.Microsoft Access using Star Schema

Based in the Star Schema concept I created the database using Dimensions Tables and the Fact table.

2

Microsoft Access using Star Schema

Microsoft Access using Star Schema

Microsoft Access using Star Schema

Microsoft Access using Star Schema

And finally the Star Schema of this database:

Microsoft Access using Star Schema

Tchanaaaan!

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:

http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

http://www.codeproject.com/Tips/642912/Create-Populate-Time-Dimension-with-Hourplus-Va

 

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.

Microsoft Access using Star Schema

After connected, you’ll see a table in Power Pivot and you’ll be able to manipulate it.

Microsoft Access using Star Schema

 

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.

Microsoft Access using Star Schema

You select the fields and information that you want to see in the report/dashboard. Be creative!

Microsoft Access using Star Schema

Microsoft Access using Star Schema

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.

Leave a Comment

Your email address will not be published. Required fields are marked *