Adventure Works Cycles, the fictitious company on which the AdventureWorks sample databases are mainly based, is a big, multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European and Asian commercial markets. While its base operation is positioned in Bothell, Washington with 290 employees, several regional sales teams are located all through their market base.
Task:
You are needed to design a dimensional model for analyzing Purchases for Adventure Works Cycles and implement it as cubes by using SQL Server Analysis Services. The AdventureWorks OLTP sample database is a data source for you BI analysis. Description of the AdventureWorks databases is accessible at:
https://msdn.microsoft.com/en-us/library/ms124825(v=sql.100)
Part A: Business requirements
Discuss the background and objectives of your BI project. Clearly explain the subject areas for your analysis and the data source tables in the OLTP system.
Part B: Key facts
Describe the key facts to accomplish the objectives of your analysis. You should comprise at least two key facts relating to purchases. Elaborate on the granularity for the facts.
Part C: Dimensions
Explain the dimensions for analyzing the key facts as laid out in Part B. Especially you should discuss:
• The hierarchy for each dimension
• Discuss if there are any conformed dimensions, and then explain the reasons why there are or are not conformed dimensions.
Part D: Multidimensional Schema
You should present your complete design of multidimensional database design as a Star schema or Snowflake schema
Part E: Implementation
Document your implementation of multidimensional cubes by using SQL Server Analysis services. You should load your data cubes with source data tables in the AdventureWorks OLTP database, as identified in part A. Your implementation must comprise precisely the dimensions and facts as explained in Parts A to D. Your documentation must comprise screenshots from SQL Server Analysis Services.