How to deploy Data warehouse using SQL Server and SQL Server Data Tool (BI)

•      Install SQL SERVER MANAGEMENT STUDIO (enterprise edition) because in this we find both Analysis Services and Database Engine. In Database Engine, we make a database or use already made a database to develop Dataware house and in Analysis Services we analysed data or do the analysis for the company.

•     Install SSDT-BI for Visual Studio, with the help of this tool we can build Data Warehouse easily in visual studio as well as in SQL SERVER

If the database is not made or not available then we have data in excel sheet or in.CVS form 

•     Open SQL SERVER, Database Engine then creates New Database and name it, then right click on that created database then go to Tasks then go to Import data and then select Data Source (Flat File Source) to import data from flat files by browsing it as shown in Fig. 

•     After clicking on next you can edit data type of your data by going to Edit Mappings then click on next to proceed data as a table in the database.

•     With the help of this process, you can import your all data (including dimensions and fact tables) or database or every dimension table and fact table in database

•     Now open SQL SERVER DATA TOOL for VISUAL STUDIO, there open new project, select Business intelligence then select Analysis Services Multidimensional and Data Mining then name the project.

•     Now go to Data Sources there open New Data Source then click on New after clicking new wizard will open as shown in fig

•     Now copy server name of your SQL SERVER in this SERVER NAME blank, then click OK. After making a connection to the SQL SERVER then click on next, there select use the credentials of the current user then click on next and then click on finish.

•     Select the database name that you have created in SQL SERVER for making Data Warehouse. 

• Now make Data Source View, for this make New Data Source

View by right clicking on New Data Source Views then click on next then next and then click on finish.

•     Now make Dimensions  from Dimension table, for this go to Dimensions, then click on New Dimensions then click on Use an existing table then select dimension or table (also select attributes that you’ll use for browsing in data warehouse) that you already created in Data Source View, after making all dimensions then generate Time Dimension, for this click on Generate a time table on the data source for Time Dimension then click on next for time calculations and time period

• Now make Cube, from fact tables, for this go to Cubes, then click on New Cubes then click on Use existing tables then select fact tables as shown in fig

After making a cube, a process that cube to deploy Data warehouse. Here you will see an error, you have to create a Login in your Database to remove this error. For this go to SQL SERVER then go to security and there create Login and write name Login name, for this go to the properties of SQL Server (MSSQLSERVER) in log On

Copy this login name and paste it to that login name wizard that you have opened for making login

After copying login name then go to the Server Roles and check the following check boxes then go to user mappings and select your database name

After all this process, process the cube again, now your error has been removed.

Now your Dataware house has been deployed.

Now you can go for calculations, and add new calculations that are:

• Status
• Goal
• Profit

After calculations, you can add KPI and then do aggregations for further calculations