1.准备工作1.1说明SQLSERVER版本：2016PowerBIReportServer、Desktop版本：2020.01SQLSERVER示例数据库：AdventureWorksDW1.2安装SQLServer相关1.安装SQLSERVER2.创建示例数据库AdventureWorksDW下载地址：https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks3.安装SSDT下载地址：https://docs.microsoft.com/zh-cn/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-20161.3安装PowerBI相关下载地址：https://docs.microsoft.com/zh-cn/power-bi/report-server/install-report-server1.安装PowerBI报表服务器2.配置PowerBI报表服务器3.安装PBIDesktopRS版本2.在AnalysisServices中完成建模参考：https://docs.microsoft.com/zh-cn/sql/analysis-services/lesson-1-create-a-new-tabular-model-project?view=sql-server-20162.1创建新的表格模型项目1．在SSDT中，在文件菜单上，单击新建>项目。2．在中新的项目对话框框中，展开已安装>商业智能>AnalysisServices，然后依次AnalysisServices表格项目。3．在中名称，类型AWInternetSales，然后指定项目文件的位置。默认情况下，“解决方案名称”将与项目名称相同；但可以键入其他解决方案名称。4.单击“确定”。5.在中表格模型设计器对话框中，选择集成工作区。在工作区将在模型创作期间托管与项目同名的表格模型数据库。集成工作区表示SSDT将使用内置实例，无需单独安装一个AnalysisServices服务器实例仅仅出于模型创作。若要了解详细信息，请参阅工作区数据库。6.在“兼容级别”中，确认已选中“SQLServer2016(1200)”，然后单击“确定”。2.2导入数据1.在表格模型资源管理器，右键单击数据源>从数据源导入。2.在表导入向导中下，关系数据库，单击MicrosoftSQLServer>下一步。3.在“连接到MicrosoftSQLServer数据库”页的“友好的连接名称”中，键入AdventureWorksDBfromSQL。4.在中服务器名称，键入安装了AdventureWorksDW数据库的服务器的名称。5.在中数据库名称字段中，选择AdventureWorksDW，然后单击下一步。6.在“模拟信息”页中，需要指定在导入和处理数据时AnalysisServices将用于连接数据源的凭据。确认已选中“特定的Windows用户名和密码”，在“用户名”和“密码”中输入Windows登录凭据，然后单击“下一步”。7.在“选择如何导入数据”页中，确认已选中“从表和视图的列表中进行选择，以便选择要导入的数据”。需要从表和视图的列表中进行选择，因此，单击“下一步”以便显示源数据库内所有源表的列表。8.在“选择表和视图”页中，选中以下各表的复选框：“DimCustomer”、“DimDate”、“DimGeography”、“DimProduct”、“DimProductCategory”、“DimProductSubcategory”和“FactInternetSales”。如需筛选数据，参考：https://docs.microsoft.com/zh-cn/sql/analysis-services/lesson-2-add-data?view=sql-server-2016#FilterData9.完成数据选择后，点击完成。2.3标记日期表要使用DAX时间函数，必须指定包含日期列的日期表，本步骤标记日期表以及日期列。1.DimDate表的列FullDateAlternateKey名称作为标识符不是很友好，双击列名重命名为Date。2.标记日期表，并选择Date列作为日期列。2.4创建关系关系是在两个表之间建立的连接，用于确立这些表中的数据应该如何相关。例如，DimProduct表和DimProductSubcategory表基于每个产品属于某个子类别的事实具有某种关系。单击模型菜单>模型视图>关系图视图，查看模型关系： 两个表之间的实线表明此关系处于活动状态，也即，当计算DAX公式时，默认情况下将使用此关系。 两个表之间的虚线表示此关系处于非活动状态，表之间可以具有多个关系，但一次只有一个关系可以处于活动状态。 箭头显示筛选器方向，星号和1显示此关系是多对一关系。 选中字段A拖动到字段B，即可建立字段A到字段B的关系。2.5创建计算列、度量值、KPI计算列：表格模型中的计算的列，可以将新数据添加到您的模型。您可以创建用于定义列的行级值的DAX公式，而不用在列中粘贴或导入值。然后，计算列可用于报表、数据透视表或数据透视图中，您可以像使用任何其他数据列一样使用计算列。度量值：在表格模型中，度量值是使用DAX公式创建的计算，以便用于报表客户端中。系统根据用户在报告客户端应用程序中选择的字段、筛选器和切片器来计算度量值。KPI：在表格模型中，KPI（关键绩效指标）用于根据目标值（由度量值或绝对值定义）度量某一值（由基础度量值定义）的性能。本文提供了表格模型作者Kpi表格模型中一个基本的了解。在DimDate表中创建MonthCalendar计算列，模型视图>DimDate>右键添加列，在公式栏键入公式：=RIGHT(""&FORMAT([MonthNumberOfYear],"#0"),2)&"-"&[EnglishMonthName]，双击列名重命名为：MonthCalendar。依次创建以下计算列：计算列表名DAX公式DayOfWeekDimDate=RIGHT(""&FORMAT([DayNumberOfWeek],"#0"),2)&"-"&[EnglishDayNameOfWeek]ProductSubcategoryNameDimProduct=RELATED('DimProductSubcategory'[EnglishProductSubcategoryName])ProductCategoryNameDimProduct=RELATED('DimProductCategory'[EnglishProductCategoryName])MarginFactInternetSales=[SalesAmount]-[TotalProductCost]在DimDate表中创建DaysCurrentQuarterToDate度量值，模型视图>DimDate>度量值网格>右键左上方空单元格>编辑公式，在公式栏键入公式：DaysCurrentQuarterToDate:=COUNTROWS(DATESQTD('DimDate'[Date]))。依次创建以下度量值：度量值表名DAX公式DaysInCurrentQuarterDimDateDaysInCurrentQuarter:=COUNTROWS(DATESBETWEEN('DimDate'[Date],STARTOFQUARTER(LASTDATE('DimDate'[Date])),ENDOFQUARTER('DimDate'[Date])))InternetDistinctCountSalesOrderFactInternetSalesSalesOrderNumber的非重复计数:=DISTINCTCOUNT([SalesOrderNumber])InternetOrderLinesCountFactInternetSales=COUNTA([SalesOrderLineNumber])InternetTotalUnitsFactInternetSales=SUM([OrderQuantity])InternetTotalDiscountAmountFactInternetSales=SUM([DiscountAmount])InternetTotalProductCostFactInternetSales=SUM([TotalProductCost])InternetTotalSalesFactInternetSales=SUM([SalesAmount])InternetTotalMarginFactInternetSales=SUM([Margin])InternetTotalTaxAmtFactInternetSales=SUM([TaxAmt])InternetTotalFreightFactInternetSales=SUM([Freight]) 与计算列不同，使用度量值公式可以键入度量值名称后,跟一个冒号,再跟公式表达式。 选中列标题，单击“自动求和”(∑)按钮旁边的向下箭头，可快速创建基础汇总度量值。创建InternetCurrentQuarterSalesPerformanceKPI，在FactInternetSales创建度量值：InternetCurrentQuarterSalesPerformance:=IFERROR([InternetCurrentQuarterSales]/[InternetPreviousQuarterSalesProportionToQTD],BLANK())，右键该度量值，创建KPI。2.6创建层次结构层次结构：在表格模型中，层次结构是定义表中两个或更多列之间的关系的元数据。层次结构可与报表客户端字段列表中的其他列单独出现，使客户端用户可以更方便地在报表中导航和包含数据。在DimProduct表中创建Category层次结构关系视图>右键单击DimProduct表>创建层次结构，将层次结构命名为Category，依次拖动ProductCategoryName、ProductSubcategoryName、ModelName、EnglishProductName形成层次结构。在DimDate表中创建层次结构在DimDate表中，创建名为的新层次结构日历。以下列按顺序添加：CalendarYearCalendarSemesterCalendarQuarterMonthCalendarDayNumberOfMonth在中DimDate表中，创建会计层次结构。包含以下列：FiscalYearFiscalSemesterFiscalQuarterMonthCalendarDayNumberOfMonth最后，在DimDate表中，创建ProductionCalendar层次结构。包含以下列：CalendarYearWeekNumberOfYearDayNumberOfWeek2.7创建透视创建“InternetSales”透视1.单击模型菜单>右键透视>创建和管理。2.在“透视”对话框中，单击“新建透视”。3.双击新的透视列标题，然后重命名InternetSales。4.选择表的所有除DimCustomer。2.8创建分区在FactInternetSales表中创建分区1.在表格模型资源管理器，展开表，右键单击FactInternetSales>分区。2.在分区管理器对话框中，单击复制。3.选中分区名称，将名称更改为FactInternetSales2010。4.选择SQL正上方的预览窗口以打开SQL语句编辑器右侧的按钮。因为您希望分区只包含特定期间内的那些行，所以您必须包含WHERE子句。您只能通过使用SQL语句创建WHERE子句。5.在中SQL语句字段中，通过复制并粘贴以下语句替换现有语句：6.单击“验证”。7.依次创建2011、2012、2013、2014年分区。8.单击FactInternetSales分区，然后单击删除。9.单击FactInternetSales表，然后单击模型菜单>过程>处理分区。2.9创建角色创建销售管理员角色1.在表格模型资源管理器，右键单击角色>角色。2.在角色管理器中，单击新建。3.单击新角色，然后在名称列中，重命名为销售管理员。4.在“权限”列中，单击下拉列表，然后选择“读取”权限。创建销售分析员US角色1.在角色管理器中，单击新建。2.重命名为销售分析员US。3.向此角色授予读取权限。4.单击行筛选器选项卡，然后对于DimGeography表仅在DAX筛选器列中，键入以下公式：=DimGeography[CountryRegionCode]="US"，利用此公式，您可指定只有CountryRegionCode值为“US”的行才对用户可见。如需控制列级权限，可记事本打开Model.bim修改。参考：https://docs.microsoft.com/zh-cn/sql/analysis-services/tabular-models/object-level-security?view=sql-server-20165.单击“成员”选项卡，然后单击“添加”。在“选择用户或组”对话框中，输入要包括在角色中的来自组织的Windows用户。后面使用PowerBI分析数据时，使用的Windows用户连接AS被视为销售分析员US角色。2.10部署将模型部署到AnalysisServices1.切换到解决方案资源管理器2.选中项目AWInternetSales，右键打开属性窗口，设置部署服务器。3.生成>部署AWInternetSales，期间可能会弹出凭据输入窗口，分别输入源数据库凭据和AnalysisServices服务凭据。在AnalysisServices中查看模型使用SQLServerManagementStudio连接AnalysisServices，可以看到刚刚部署的数据库AWInternetSales。3.在PowerBI中创建报表分析模型数据3.1获取数据1.开始>获取数据>选择“SQLServerAnalysisServices数据库”，点击连接。2.输入数据库地址，数据库名，选择实时连接，确定。3.2设计报表创建销售分布地图1.点击地图控件加入画布，调整控件大小。2.将DimGeography表的EnglishCountryRegionName字段拖入位置区域。3.将FactInternetSales表的SalesAmount字段拖入大小区域。这里顺便测试一下权限管控，使用jizw用户连接AS获取数据，同样的步骤创建地图，只可以看到北美的销售额。创建业绩趋势图1.点击簇状柱状图，调整大小。2.将DimDate表的月份列拖入轴区域。3.将FactInternetSales表的SalesAmount字段拖入值区域。创建分类饼图1.点击饼图，调整大小。2.将DimProduct表前面创建的层次结构：类别拖入图例区域。3.将FactInternetSales表的SalesAmount字段拖入值区域。创建年度切片器1.点击切片器，调整大小。2.将DimDate表的年度字段拖入字段区域。这样一张简单的PowerBI就创建完成了，将Sheet名称改为销售趋势分析。3.3发布报表将报表发布到PowerBI报表服务器1.文件>另存为>PowerBI报表服务器2.输入报表服务器报表门户地址，点击确定。3.选择替换或新建报表，点击确定。在PowerBI报表服务器上查看保存的报表1.打开报表服务器报表门户地址：http://172.20.4.142/PBIReports，输入凭据。2.点击SalesReport查看上一步发布的报表。
