Microsoft SQL Server 2012:

 

As part of the ongoing services that BTEKS offer,

we make sure that we stay abreast of the most recent and current database management skills

and techniques.

 

Microsoft SQL

Server 2012
is one of the most recent database management systems that we have been trained to service

and are ready to provide all the needed associating services to any company that seeks to request our help.

 

We are capable of providing know how and bring added value and expertise to your existing

database team or

lead your team in the management of your MS SQL Server 2012 using the

MS SQL Tools:

 

SQL Server Data Tools

 

SQL Server Management Studio

 

Analysis Services

 

Configuration Tools

 

Integration Services

 

Master Database Services

 

&

 

Performance Tools

 

HOW TOO:

 

How to Create a Database in Sql Server 2012

 


BTEKS

 

MS SQL SERVER 2012

 

 

Creating a Fact Table,
Dimension Table and a
Relational Diagram
for a Data Warehouse


 

 

Data Warehouse, in my opinion, is the specifically designed utilization of a database's functionality to extrapolate the possible possibilities of all the facts of all that data
that have been collected for your company or industry
over a span of extended time.

 

Questions Answered and even Questions Created
once the data is actually migrated into a Data Warehouse.

All of that collected data is usable and can provide stuff that helps you
and your business or your interest.

 

When studied, one finds that "Data" has a personality,
and if we begin to understand the personality of the data that you are collecting,
we can use that understanding to predict a lot of stuff.

 

 

Creating a Fact Table and a Dimension Table for a Data Warehouse;

 

 

Opening MS SQL Server 2012 Management Studio

 

Via the Connect to Server window, click “connect” to open SQL Server Management Studio

 

From the Object Explorer, click on your Data Warehouse Database.

 

Fact Table Creation Options:

1. Scripting:  You can create a fact table exactly as you create any other table, just type in the script and execute the script to create the fact table in the Data Warehouse Database.

2.  Load & Execute a SQL File:  You can open a pre-scripted .sql file that has all the pre-scripted code to create your fact table by simply locating the file on your hard drive and opening it with SSMS.

 

Option 1 We will write the script and execute.

 

Open a new query window:

 

Ensure that your opening the query window in the correct database as described below.

 

Type your script or paste your script in place as displayed below.

 

Execute your script to create your Fact Table.

 

 

View the newly created Fact Table under the VTCDW Database Tree.

 

 

Option 2 We will open an pre-scripted .sql file and execute.

 

After clicking on the Data Warehouse Database, we will locate our .sql file as described below.

 

Once you have navigated to the .sql file, open it.

 

Via SSMS we can view and edit the .sql file as needed to add too.. or remove from the code that is displayed below.

 

Execute your script to create your Fact Table.

 

 

View the newly created Fact Table under the VTCDW Database Tree.

 

 

Creating a Dimension Table:

The same procedures to create the Dimension Table can be repeated via the two above steps.

 

 

Creating a Database Relationship Diagram

 

in a Data Warehouse Database

 

The goal is to build a diagram that displays a Primary key and a Foreign Key relationship
between tables and then applying these relations to a diagram that provides
a visual look at the relationships between these tables.

 

These steps will be performed in a Data Warehouse Database that

create relationship between the Fact Table and the Dimension Tables.

 

Assuming that you have created some "Dimension Tables" as well as the "Fact Tables" as described above, the first step in creating your Database Relationship Diagram is described below.

 

A message will display if your actually accessing the Database Diagram for the first time... click ok.

 

The "Add Table" Window displays a list of all the tables created in the selected Database.

 

Once a select table is added, SSMS will automatically add the table to the Diagram Window, and the

"Add Table" window will continue to display until you have selected all the tables or click "close" to end your selections.

 

Select the next table to be added to the SSMS Diagram Window.

 

Select another table to be added to the diagram window.

 

Adding the final table to the diagram window.

 

After all windows are selected from the Add Table window, close it.

 

With all tables selected and are now displayed in the Diagram Window, lets get started on creating relationships.

 

Creating Relationships between the tables:

 

Notice that each table has a “Primary Key” with a picture of a small key beside the

row/column that is the primary key.

 

Creating foreign relation between the tables:
as an example, creating a foreign key relationship between “dimproduct table” and

the “FactInternetSales table”, click on the "ProductKey" on the “dimProduct” table and drag it to the “ProductKey” row on the Fact Table.

 

HOLD ON.... don't panic.

Clicking OK above will display these 3 windows and the below describes each section. 

Read the below display which describes the Relationship Name, and identifying which is the foreign key column and the primary key column for each table.

 

The Foreign Key Relationship Window provides details associated with the
"FK_FactInternetSales_Dimproduct1" created.

These details associate with what will be created when you click ok, and all the associating rules associated with a Foreign Key and Primary Key Relationship are coded in this relationship.

 

 

 

The Diagram Window displays a relationship between the "dimProduct Table"

and the "FactInternetSales Table" known as a Primary Key and Foreign Key Relationship

between the two tables.

 

We will now create a relationship between the FactInternetSales Table and the dimDate Table, and the dimCustomer table following same process as described above.

 

The DimDate Table will be next:

 

The dimDate is identified in the Relationship Name.

 

Validating relationship specifications.

 

Now, the final table will be added in this relationship tree.

 

Finally, the last relationship is being created.

 

we click ok to finish the final table connection.

 

The SSMS Diagram Window displays the relationship connections between the Fact Table and the 3 Dimension Tables.

 

Save the changes made to the MS SQL Database.

 

Enter a name for your Diagram and click ok.

 

Save the changes and the code creating the relationships in the database will be written.

 

Verifying that you have a Foreign Relationship established in the Fact Table, and a Primary Key Relationship on the 3 Dimension Tables is done by expanding the tree to view the tables column keys.

 

To view the newly created Database Relationship Diagram, click on the diagram to open it in the SSMS Diagram Window.

 

This concludes the instructions on

"How to create a Fact Table, and a Dimension Table, and a

Relationship Diagram in MS SQLS Server 2012.