Microsoft SQL Server Analysis Services, SSAS, Microsoft SQL Server has an online analytical processing, OLAP, data mining and reporting tools. This article shows you how to create a data warehouse: database, dimension, solution and OLAP cube. Article created based on http://www.learn-with-video-tutorials.com/analysis-services-video-tutorial-free
Create a database. Create the Car_transactions database. Open SQL Server Management Studio and create commands DATABASE car_transactions;
Go to the Car_transaction database and create some tables.
Enter some records. Open SQL Server Management Studio and insert records in tables.
Create a user. Open SQL Server Management Studio. In Object Explorer, right-click the login folder and select New Login. Type the login name. Select SQL Server Authentication. Type the password and confirm. Our password is just. We apply password policy options. Select the default database. Click on Server Roles.tab and select the appropriate role. Click OK.
Create an Analysis Services project. On the Microsoft Windows Taskbar, click Start, point to All Programs, expand Microsoft SQL Server xxxx folder, and then select SQL Server Business Intelligence Development Studio.
On the File menu, point to New and then select Project. Name your project. The solution changes automatically to match the text project name in the name box. If you need to select Create Directory to resolve and rename the solution. Click Ok to create a project.
Create a data source. The first task of creating an Analysis Services project is to create a data source. The data source contains information that the Analysis Services source uses to connect to the database. It includes the name of the data provider, the name of the server and the database, and authentication credentials which will use the Analysis Services.
In BIDS in Solution Explorer, right-click on the data source folder and select New Data Source. The Data Source Wizard appears. On the Welcome page, click Next. On the Select to define the Connection Page, click the New button. The Connection Manager dialog box appears.
Type a server name: Localhost Select or enter the database name list box, select car_transactions. Click Test Connection. A dialog box opens with the message “Test connection succeeded.” Click OK. Click OK to close the Connection Manager dialog box. In the Data Source Wizard, on how to define a connection page, click Next. On the Impersonation information page, select Use Service account and click on Next. Click Finish to complete the wizard.
Now that you have created a data source, you are ready to create a data source view.
Create a data source view. The data source view is a logical data model that exists between your physical source database and the Analysis Services dimensions and Cubes.
When you create a data source view, you specify tables and ideas in the source database that contain data that you want to use to create dimensions and cubes.
In Solution Explorer, right-click on the Data Source View folder and select New Data Source View. The Data Source View Wizard appears. On the Welcome page, click Next.
On the Select Data Source page, select the car_transactions relational data source and click Next. Select the dimensions and tables of facts, and then add related tables and click Next. Accept the default name and click Finish. The Data Source View Designer displays the tables you selected.
Create the dimension. Creating customer dimensions: In Solution Explorer, right-click the Dimensions folder and choose a new dimension. On the Welcome to the Dimension Wizard page, click on Next. Verify that the use of an existing table is selected and click Next.
Select customers from the main table list. Select the alias from the Name column list, and then click Next. Verify that the city and country tables are selected and click Next. From the Available Properties list, select all the attributes and click Next.
On the Completer Wizard page, type the dimension name and click Finish. Drag the country attribute from the Properties pane and drop the hierarchy panel. Drag City and Cast ID attributes from the Features pane and drop it to <New Level>. In the hierarchical pane, right-click on the new hierarchy header and select Rename. Type the Country client hierarchy and press Enter.
In Object Explorer, right-click on customer dimension, and select the process. Then click Yes. In the Process Dimensions dialog box, click Run. In the Process Progress dialog box, click Close. Now that the customer dimension has been created. You can preview the dimension – click on the browser tab. Create another dimension: Salesmen, cars and time.
Create a cube. In the Solution Explorer, right-click on the Cubes folder and select the new cube. On the Welcome to the Cube Wizard page, click Next. Verify that existing tables are selected and click Next.
In the measurement group tables list, select Transactions_facts_table and click Next. Select the solution page measurement lists the groups and the measures that the wizard will create. Click Next. On the Select current dimension page, verify that the Customer, Cars and Salesman dimensions are selected. Click Next.
On the Complete Wizard page, type the name of the cube and click Finish. The cube designer, car transaction shows the structure of the cube.
In Dimensions, right-click and select Add Cube dimension. To add a solid dimension, choose the time and click OK. In the Cube Designer, click the Dimension Usage tab. Select the cell in the grid at the intersection of time dimension and measure Transactions Cars Facts. Click the Ellipsis button. Choose the regular type. Select the date. Create relationships between date columns (time dimensions) and salesdate columns (facts table). Select SalesDate In Solution Explorer, right-click on the car transaction cube and select the Process Cube. Click on the run to process the cube. To preview the cube, select the Browser tab.