sql2018 ssas_分析服务（SSAS）SQL Server数据仓库设计最佳实践

sql2018 ssas

Before jumping into creating a cube or tabular model in Analysis Service, the database used as source data should be well structured using best practices for data modeling. Some might say use Dimensional Modeling or Inmon’s data warehouse concepts while others say go with the future, Data Vault. No matter what conceptual path is taken, the tables can be well structured with the proper data types, sizes and constraints.

在开始使用Analysis Service创建多维数据集或表格模型之前，应使用数据建模的最佳实践对用作源数据的数据库进行良好的结构化。 有些人可能会说使用Dimensional Modeling或Inmon的数据仓库概念，而另一些人则说会与未来的Data Vault保持一致。 无论采用哪种概念路径，都可以使用适当的数据类型，大小和约束来很好地组织表。

This article is going to use a scaled down example of the Adventure Works Data Warehouse. The full version of the Adventure Works DW can be downloaded from CodePlex.

本文将使用Adventure Works数据仓库的缩小示例。 完整版本的Adventure Works DW可以从CodePlex下载。

Taking on a complete data warehouse project can be overwhelming. The best path from experience is to create the minimum structure that yields a good result. Not the best, but something of value where management will approve and extend other data warehouse projects.

进行一个完整的数据仓库项目可能会很困难。 经验的最佳途径是创造出可以产生良好结果的最小结构。 不是最好的，而是管理部门将批准和扩展其他数据仓库项目的有价值的东西。

The beginning can be a data mart that might answer the following questions:

开始时可能是一个数据集市，可能会回答以下问题：

1. Compare Sales of Mountain Bikes for years 2015 and 2016
   比较2015年和2016年山地车的销售额
2. What happens to profits when you remove the Bike Category?
   当您删除“自行车类别”时，利润会怎样？
3. Show average sales for subcategories within categories in the USA?
   显示美国类别中子类别的平均销售额？
4. Calculate Gross Profit Margin for Internet Sales region by Year, Quarter and Month
   按年，季度和月计算互联网销售区域的毛利率

From these specific inquiries, we can get an idea of the data required. There will be a Data dimension because of the need for Year, Quarter and Month. The main numerical value is sales, but there is a calculation for Gross Profit Marg