Home   Ask Question Discuss Purchase Submit Events Give Solutions Share News
Automobiles Beauty & Styles Business Computers & Internet Sports Travel Consumer Electronics Education Entertainment & Music Family Society & Culture Food & Drinks Health
Computer Networking
Programming & Design
Other - Computers

Steps to create Cubes in SQL SSAS

We need report to be viewed in various applications. So to view the report stored in SSAS we need to create cubes in SSAS (SQL Server Analysis Service). Below I am using Microsoft Sample database AdventureWorks2008 to create cubes and get some sort of report. These are basic steps that you need to create cubes. You can have your own database as the data source to create cubes and generate your report as per your requirements.

  1. 1. Select Microsoft SQL Server 2008 „ SQL Server Business Intelligence Development Studio from the Programs menu to launch Business Intelligence Development Studio.
  2. 2. Select File > New > Project.
  3. In the New Project dialog box, select the Business Intelligence Projects project type.
  4. Select the Analysis Services Project template.
  5. Name the new project AdventureWorksCube2 and select a convenient location to save it.
  6. Click OK to create the new project.

  1. Right-click on the Data Sources folder in Solution Explorer and select New Data Source.

  1. Read the first page of the Data Source Wizard and click Next.
  2. Select the existing connection to the AdventureWorksDW2008 database and click Next. Or if you are creating the New Data Source, Pleas click on ‘New’ and create a new data source

10. Select ‘Use Service Account’ and click Next.

11. Accept the default data source name and click Finish.

12. Right-click on the Data Source Views folder in Solution Explorer and select New Data Source View.

13. Read the first page of the Data Source View Wizard and click Next.

14. Select the Adventure Works DW2008 data source and click Next.

15. Select the FactInternetSales(dbo) table in the Available Objects list and click the > button to move it to the Included Object list

16. Click the Add Related Tables button to automatically add all of the tables that are directly related to the dbo.FactInternetSales table. Also add the DimGeography dimension.

17. Click Next

18. Name the new view InternetSales and click Finish.

19. Right-click on the Cubes folder in Solution Explorer and select New Cube

20. Read the first page of the Cube Wizard and click Next.

21. Select the option to Use Existing Tables.

22. Select FactInternetSales and FactInternetSalesReason tables as the Measure Group Tables and click Next.

23. Leave all measures selected and click Next.

24. Leave all dimensions selected and click Next.

25. Name the new cube InternetSalesCube and click Finish.

26. In the Solution Explorer, double click the DimCustomer dimension.

27. Add the MaritalStaus field as an attribute, along with any other fields desired. Drag and Drop from Right ‘Data Source View’ column to left ‘Attributes’ coloumn

28. Similarly, edit the DimSalesTerritory dimension, adding the SalesTerritoryCountry field along with any other desired fields

29. Also edit the DimProduct dimension, adding the EnglishProductName field along with any other desired fields.

30. From Menu Bar on Top Select Project > AdventureWorksCube2 Properties, click on Deployment link on left pane and verify that your server name is correctly listed. Click OK.

31. From the top Menu Bar, Select Build > Deploy AdventureWorksCube2. On the right bottom corner you will see “successfully deployed message”. If you don’t then it needs to be troubleshot.

32. Right-click on the cube in Solution Explorer and select Browse.

33. Expand the Measures node in the metadata panel and expand Fact Internet Sales

34. Drag the Order Quantity and Sales Amount measures and drop it on the Totals/Detail area.

35. Expand the Dim Sales Territory node in the metadata panel.

36. Drag the Sales Territory Country property and drop it on the Row Fields area.

37. Expand the Dim Product node in the metadata panel.

38. Drag the English Product Name property and drop it on the Column Fields area.

39. Expand the Dim Customer node in the metadata panel.

40. Drag the Marital Status property and drop it on the Filter Fields area.

41. Click the dropdown arrow next to Marital Status. Uncheck the S checkbox.

42. Below Figure show the finished Cube.

If you need to know how to configure SharePoint Performance Point Service to display the SSAS report, please check this article

Bookmark and Share
Related Posts
Add Comment
Hot Categories: Religion & Spirituality Other - Family & Relationships Others Other - Electronics Computer Networking Celebrities Football (Soccer) Tennis
Contact Us About Us Privacy Policy Copyright Sitemap