Data Warehouse
We use a data warehouse for analysis, of data and most often, it's used in reporting the data. Mostly, historical and current data is used to store at one place, in a Data Warehouse.
There are two kinds of tables which we have to generate in it.
- Dimensional Tables
- Fact Tables
SQL
This tool is used to communicate with the database. Data can be manipulated, retrieved, and stored in the database. We can perform CRUD operations in it, for the manipulation of the data. CRUD stands for create, read, update, delete.
SQL Database and Data Warehouse
We can store different kinds of data in the database and the analytics are done in the Data Warehouse. The Data Warehouse is std in the databases, for analysis of data.
Star Schema
There are various schemas to represent the warehouse structure. One of them is Star Schema, which resembles a star because there are fact tables, and around those, there are dimensional tables.
Implementation
First of all, install the SQL Server Management Studio. Version 2012, 2014, and above can be viable. I have SSMS 2012 for implementing the star schema in SQL.
First, let us add a database in the SSMS, for process analysis of the data.
I have created a database named ”DemoStar”.
The next step is to add the table. First, I will add the dimensional tables. The dimensional tables have the relationship of the primary key, with the fact table and primary key functions, as a foreign key in the fact table.
Dimensional Tables
These are simple tables that can be made through queries, or (in our case) in SSMS directly. Now, let us create some dimensional tables.
Select a table in the database, which you have created. Then, select a dropdown in which you have to select tables.
Now, right-click on the table name and add a table, which you are required to add. Then, add attributes with datatypes in it, as in the given picture below.
Now, press “control+ s”, so that the table name will be saved. I have saved a table named as Customer. I have added two tables that are following -
Fact Tables
In fact, the dimensional table acts as a foreign relationship, in which through dimensions the fact that we have to be analysis, can be analyzed. They are foreign keys that can be gotten from dimensional tables, so that the facts can be analyzed, by using the dimensions.
Select the table and add a table, by using the primary key of dimensional and used facts in it.
And, you can save the table as the fact table.
For making a primary key of dimensional table to the foreign key, we use following query and the queries will be run successfully.
- Use DemoStar
- Alter table [dbo].[Fact] Add Constraint
- FK_P_Id Foreign Key (P_Id) References [dbo] . [Dim_Product](P_Id)
- Use DemoStar
- Alter table [dbo].[Fact] Add Constraint
- FK_C_Id Foreign Key (C_Id) References [dbo] . [Dim_Customer](C_Id)
After the created diagram, I have added a, few more tables in it so it will shape like star.
Click in the database diagram and you will get to know how the star has been generated, by using a relationship of dimension table, with fact table.