1) Creating Data Warehouse
Execute T-SQL Script to create data warehouse with fact tables, dimensions and populate
them with appropriate test values.
After downloading extract file in folder.
Follow the given steps to run the query in SSMS (SQL Server Management
Studio).
1. Open SQL Server Management Studio 2012
2. Connect Database Engine
Password for sa : admin123 (as given during installation)
Click Connect.
3. Open New Query editor
4. Copy paste Scripts given below in various steps in new query editor
window one by one
5. To run the given SQL Script, press F5
6. It will create and populate “Sales_DW” database on your SQL Server
OR
1. Go to the extracted sql file and double click on it.
2. New Sql Query Editor will be opened containing Sales_DW Database.
3. Click on execute or press F5 by selecting query one by one or directly click on Execute.
4. After completing execution save and close SQL Server Management studio & Reopen to
see Sales_DW in Databases Tab.
2) Start SSDT environment and create New Data Source
Go to Sql Server Data Tools --> Right click and run as administrator
Click on File → New → Project
In Business Intelligence → Analysis Services Multidimensional and Data Mining models
→ appropriate project name → click OK
Right click on Data Sources in solution explorer → New Data Source
Data Source Wizard appears
Click on New
Select Server Name → select Use SQL Server Authentication → Select or enter
a database name (Sales_DW)
Note : Password for sa : admin123 (as given during installation of SQL 2012
full version)
Click Next
Select Inherit → Next
Click Finish
Sales_DW.ds gets created under Data Sources in Solution Explorer
3) Creating New Data Source View
In Solution explorer right click on Data Source View → Select New Data
Source View
Click Next
Click Next
Select FactProductSales(dbo) from Available objects and put in Includes
Objects by clicking on >
Click on Add Related Tables
Click Next
Click Finish
Sales DW.dsv appears in Data Source Views in Solution Explorer.
4) Creating new cube
Right click on Cubes → New Cube
Select Use existing tables in Select Creation Method → Next
In Select Measure Group Tables → Select FactProductSales → Click Next
In Select Measures → check all measures → Next
In Select New Dimensions → Check all Dimensions → Next
Click on Finish
Sales_DW.cube is created Successfully
5) Dimension Modification
In dimension tab → Double Click Dim Product.dim
Drag and Drop Product Name from Table in Data Source View and Add in Attribute Pane
at left side
6) Creating Attribute Hierarchy in Date Dimension
Double click On Dim Date dimension -> Drag and Drop Fields from Table shown in Data
Source View to Attributes -> Drag and Drop attributes from leftmost pane of attributes to
middle pane of Hierarchy.
Drag fields in sequence from Attributes to Hierarchy window
(Year, Quarter Name, Month Name, Week of the Month, Full Date UK)
7) Deploy Cube
Right click on Project name → Properties
This window appears
Do following changes and click on Apply & ok
Right click on project name → Deploy
Deployment successful
To process cube right click on Sales_DW.cube → Process
Click run
Browse the cube for analysis in solution explorer
