Creating a Star Schema Using a Data Source View
Overview
A data warehouse or data mart from where we would source our data could contain ten to hundreds of tables. Also one would not have the liberty to change the schema of these tables to suit the requirements of the cube design. The Data Source View is an insulation layer between the actual data source and the solution. We can create and modify the schema we need in this layer and this is used as the data source for the different objects we create in the solution. A Star Schema is a schema structure where different dimension tables are directly connected to the fact table. If you imagine a fact table in the center and different dimensions attached to it, you would find the figure similar to a star and hence the name star schema. It’s the simplest form of the schema and hence we will use this in our exercise.
Explanation
Right-click on the Data Source View and select New Data Source View and a wizard should pop-up with a Welcome screen. Select “Next”, and the next screen should prompt you to select a relational data source. Select the data source we just created and click “Next”, the next screen should prompt you to select tables that we intend to use in our solution. Select the tables as shown in the below screenshot. The below fact and dimension tables are chosen as they are interlinked with each other and also suits the requirements of the exercises to follow.
Select “Next”, name the DSV to something appropriate and this should finally create your Data Source View. After arranging the tables in the DSV, your schema should look similar to the below screenshot.
In the above figure, you can see that both the fact tables are related to all three dimensions in the same manner. This is a typical case of a star schema. You can also browse the data, create calculated fields, assign primary keys and carry out other similar function in this designer to modify the schema without modifying the actual schema in the database.