建模Modeling

使用 Power BI 中的建模功能,可以连接到多个数据源,然后按照创建适合客户需求的独特数据模型的方式进行组合。With the modeling capabilities in Power BI, you can connect to multiple data sources, then combine them in ways that create a unique model of data that suits your needs.

你将了解操作方式...

数据建模简介Introduction to modeling your data

欢迎进入 Power BI 引导学习课程的建模部分。Welcome to the Modeling section of the Guided Learning course for Power BI. 本部分介绍了如何使用 Power BI Desktop 将已连接的数据准备就绪,以供使用。This section shows you how to get your connected data ready for use, using Power BI Desktop. 通常情况下,你将会连接到多个数据源以创建报表,且需所有数据协同工作。Often, you'll connect to more than one data source to create your reports, and you'll need all of that data to work together. 建模就是实现这一点的办法。Modeling is how you get it there.

若要创建不同数据源之间的逻辑连接,需创建一种关系To create a logical connection between different data sources, you create a relationship. 数据源之间的关系使 Power BI 能够了解表与表之间的关系,以便你能创建有趣的视觉对象和报表。A relationship between data sources enables Power BI to know how those tables relate to one another, allowing you to create interesting visuals and reports. 本部分对关系作出了解释(尽管仅针对以数据为中心的关系),甚至还说明了如何在不存在任何关系的情况下创建关系。This section explains relationships (only the data-centric ones, though), and even shows you how to create relationships when none exists.

和往常一样,本课程将按照 Power BI 中的工作流引导你进入学习之旅。As always in this course, your learning journey follows the same path as the flow of work in Power BI. 本部分的大部分内容仍将在 Power BI Desktop 中进行,但是此处所做的工作会直接影响 Power BI 服务中的工作。We'll still be in Power BI Desktop for most of this section, but the work done here has direct affect on working in the Power BI service.

数据建模简介Introduction to modeling your data

既然我们已经回顾了如何导入和转换数据,现在就可以开始建模。Now that we've reviewed how to import your data and transform it, it's time to start modeling.

Power BI 的一大优点就是无需将数据平展成表。One of Power BI's strengths is that you don't need to flatten your data into one table. 相反,你可以使用来自多个源的多个表,并定义它们之间的关系Instead, you can use multiple tables from multiple sources, and define the relationship between them. 还可以创建你自己的自定义计算并分配新度量值以查看数据的特定段,并在可视化效果中使用这些新度量值以实现轻松建模。You can also create your own custom calculations and assign new metrics to view specific segments of your data, and use these new measures in visualizations for easy modeling.

如何管理数据关系How to manage your data relationships

Power BI 允许你以可视方式设置表或元素之间的关系。Power BI allows you to visually set the relationship between tables or elements. 若要查看数据的图表视图,请使用关系视图(位于报表画布旁屏幕的最左侧)。To see a diagrammatic view of your data, use the Relationship view, found on the far left side of the screen next to the Report canvas.

关系视图中,你可以看到表示各个表的数据块,它们之间的表列和表行就是表示的关系。From the Relationships view, you can see a block that represents each table and its columns, and lines between them to represent relationships.

添加和删除关系非常简单。Adding and removing relationships is simple. 若要删除关系,右键单击它并选择删除To remove a relationship, right-click on it and select Delete. 若要创建关系,拖放想要在表格之间创建链接的字段。To create a relationship, drag and drop the fields that you want to link between tables.

若要隐藏报表中的表格或单列,在关系视图中右键单击它,然后选择在报表视图中隐藏To hide a table or individual column from your report, right-click on it in the Relationship view and select Hide in Report View.

有关你的数据关系的更详细视图,请选择开始选项卡中的管理关系。这将打开管理关系对话框,该对话框将你的关系显示为列表而不是直观的关系图。For a more detailed view of your data relationships, select Manage Relationships in the Home tab. This will open the Manage Relationships dialog, which displays your relationships as a list instead of a visual diagram. 在此处,你可以选择自动检测以查找新的或更新数据中的关系。From here you can select Autodetect to find relationships in new or updated data. 选择管理关系对话框中的编辑,以手动编辑你的关系。Select Edit in the Manage Relationships dialog to manually edit your relationships. 你也可以在该位置找到高级选项以设置关系的 基数交叉筛选 方向。This is also where you can find advanced options to set the Cardinality and Cross-filter direction of your relationships.

基数的选项有 多对一一对一Your options for Cardinality are Many to One, and One to One. 多对一 是维度类型关系事实,例如一张销售表,每种产品都具有多行数据,与之匹配的是一个表格,在唯一行中列出了所有产品。Many to One is the fact to dimension type relationship, for example a sales table with multiple rows per product being matched up with a table listing products in their own unique row. 一对一 通常用于链接引用表格中的单个条目。One to One is used often for linking single entries in reference tables.

默认情况下,关系将设置为在两个方向上交叉筛选。By default, relationships will be set to cross-filter in both directions. 只在一个方向交叉筛选限制了关系中的某些建模功能。Cross-filtering in just one direction limited some of the modeling capabilities in a relationship.

设置数据之间的准确关系让你能够在多个数据元素间创建复杂的计算。Setting accurate relationships between your data allows you to create complex calculations across multiple data elements.

创建计算列Create calculated columns

创建计算列是一种用于丰富并增强数据的简单方法。Creating calculated columns is a simple way to enrich and enhance your data. 计算列是通过对以下计算进行定义而创建的新列:转换或合并现有数据的两个或多个元素。A calculated column is a new column that you create by defining a calculation that transforms or combines two or more elements of existing data. 例如,可以通过将两列合并为一列来创建新列。For example, you can create a new column by combining two columns into one.

创建计算列的其中一个有效原因是,当没有可用于建立关系的唯一字段时,在表格之间建立关系。One useful reason for creating a calculated column is to establish a relationship between tables, when no unique fields exist that can be used to establish a relationship. 当在 Power BI Desktop 中创建简单的表格视觉对象时,缺少关系会变得明显,并且所有条目都将获得同样的值,而你知道基础数据是不同的。The lack of a relationship becomes apparent when you create a simple table visual in Power BI Desktop, and you get the same value for all entries, yet you know the underlying data is different.

若要使用具有唯一字段的数据创建关系,例如,当数据中存在“区号”和“本地电话”列时,你可以通过将两者的值合并创建新的“完整电话号码”计算列。To create a relationship with unique fields in data, you can, for example, create a new calculated column for "Full Phone Number" by combining the values from the "Area Code" and "Local Number" columns when those values exist in your data. 计算列是用于快速创建模型和可视化效果的有用工具。Calculated columns are a useful tool for quickly creating models and visualizations.

若要创建计算列,请在报表画布的左侧选择 Power BI Desktop 中的数据视图To create a calculated column, select the Data view in Power BI Desktop from the left side of the report canvas.

在“建模”选项卡中,选择新建列From the Modeling tab, select New Column. 这将在你可以使用 DAX(数据分析表达式)语言输入计算的地方启用公式栏。This will enable the formula bar where you can enter calculations using DAX (Data Analysis Expressions) language. DAX 是一种功能强大的公式语言(Excel 中也有),它可以生成可靠的计算。DAX is a powerful formula language, also found in Excel, that lets you build robust calculations. 在键入公式期间,Power BI Desktop 会显示匹配的公式或数据元素来帮助并加速公式的创建。As you type a formula, Power BI Desktop displays matching formulas or data elements to assist and accelerate the creation of your formula.

在输入表达式期间,Power BI 公式栏将提供特定的 DAX 函数和相关数据列建议。The Power BI formula bar will suggest specific DAX functions and related data columns as you enter your expression.

一旦在每个表中都创建了计算列,它们便可以用作关系键来建立它们之间的关系。Once the calculated columns are created in each table, they can be used as a unique key to establish a relationship between them. 转到关系视图,你便可以将字段从一个表拖动到另一个表来创建关系了。Going to Relationship view, you can then drag the field from one table to the other to create the relationship.

返回报表视图,你现在将看到各区的值都不同。Returning to Report view, you now see a different value for each district.

还有可以通过创建计算列执行的各种其他操作。There are all sorts of other things you can do by creating calculated columns, too.

优化数据模型Optimizing data models

导入的数据通常包含报表和可视化任务中实际不需要的字段,原因是它为额外信息,或者此数据已在其他列中可用。Imported data often contains fields that you don't actually need for your reporting and visualization tasks, either because it's extra information, or because that data is already available in another column. Power BI Desktop 提供一些工具,可用于优化数据、使其更利于创建报表和视觉对象,以及查看共享报表。Power BI Desktop has tools to optimize your data, and make it more usable for you to create reports and visuals, and for viewing your shared reports.

隐藏字段Hiding fields

若要隐藏 Power BI Desktop 的字段窗格中的某列,请右键单击它,然后选择隐藏To hide a column in the Fields pane of Power BI Desktop, right-click on it and select Hide. 请注意,不会删除隐藏的列;如果此字段已在现有可视化对象中使用,则数据仍位于该视觉对象中,且你仍可在其他可视化对象中使用此数据,隐藏的字段仅不在字段窗格中显示。Note that your hidden columns are not deleted; if you've used that field in existing visualizations, the data is still in that visual, and you can still use that data in other visualizations too, the hidden field just isn't displayed in the Fields pane.

若在关系视图中查看表格,则隐藏的字段将通过灰显进行表示。同样,其数据仍然可用且仍是模型的一部分,只是隐藏不可见而已。If you view tables in the Relationships view, hidden fields are indicated by being grayed out. Again, their data is still available and is still part of the model, they're just hidden from view. 通过右键单击该字段并选择取消隐藏,可随时取消隐藏已隐藏的任何字段。You can always unhide any field that has been hidden by right-clicking the field, and selecting unhide.

按其他字段对可视化数据进行排序Sorting visualization data by another field

建模选项卡中提供的按列排序工具对于确保数据按预期顺序显示非常有用。The Sort by Column tool, available in the Modeling tab, is very useful to ensure that your data is displayed in the order you intended.

举一个常见的例子,包括该月名称的数据默认按字母顺序排序,因此比如“八月”显示在“二月”之前。As a common example, data that includes the name of the month is sorted alphabetically by default, so for example, "August" appears before "February".

此情况下,解决此问题的方法是在字段列表中选择字段、再从建模选项卡中选择按列排序,然后选择要排序的字段。In this case, selecting the field in the Fields list, then selecting Sort By Column from the Modeling tab and then choosing a field to sort by can remedy the problem. 本例中,“MonthNo”类别排序选项按预期对月份进行排序。In this case, the "MonthNo" category sort option orders the months as intended.

另一种优化信息以使其正确处理的方式是设置字段的数据类型。Setting the data type for a field is another way to optimize your information so it's handled correctly. 若要从报表画布中更改数据类型,请在字段窗格中选择该列,然后使用格式下拉菜单选择其中一种格式设置选项。To change a data type from the report canvas, select the column in the Fields pane, and then use the Format drop-down menu to select one of the formatting options. 任何已创建且显示此字段的视觉对象均将自动更新。Any visuals you've created that display that field are updated automatically.

创建计算度量值Create calculated measures

度量值 是存在于你的 Power BI 数据模型中的一种计算。A measure is a calculation that exists in your Power BI data model. 要创建度量值,在报表视图中从建模选项卡选择新度量值To create a measure, in Report view select New Measure from the Modeling tab.

DAX(Power BI 中数据分析表达式语言)最大优势之一是它有很多有用函数,特别是关于基于时间的计算,如本年截止到现在或同比。One of the great things about DAX, the Data Analysis Expression language in Power BI, is that it has lots of useful functions, particularly around time-based calculations such as Year to Date or Year Over Year. 你只需使用 DAX 定义一次时间度量值,然后从数据模型中按照你所需数量的不同字段来划分它。With DAX you can define a measure of time once, and then slice it by as many different fields as you want from your data model.

在 Power BI中,定义的计算称为度量值。In Power BI, a defined calculation is called a measure. 若要创建度量值,从主页选项卡中选择新度量值。此操作将打开公示栏,你可以在其中输入定义度量值的 DAX 表达式。To create a measure, select New Measure from the Home tab. This opens the Formula bar where you can enter the DAX expression that defines your measure. 键入时,在输入计算过程中,Power BI 会建议相关的 DAX 函数和数据字段,并且你还将得到解释语法和函数参数的工具提示。As you type, Power BI suggests relevant DAX functions and data fields as you enter your calculation, and you'll also get a tooltip explaining some of the syntax and function parameters.

如果你的计算很长,则通过键入 ALT-Enter,你可以在表达式编辑器中添加额外的换行符。If your calculation is particularly long, you can add extra line breaks in the Expression Editor by typing ALT-Enter.

创建新的度量值后,它将出现在屏幕右侧的字段窗格中一个表格中。Once you've created a new measure, it will appear in one of the tables on the Fields pane, found on the right side of the screen. Power BI 会将新的度量值插入到当前你选中的表格中,不管度量值处在数据的什么位置,你都可以通过选中此度量值,并使用主页表下拉菜单,轻松地移动它。Power BI inserts the new measure into whichever table you have currently selected, and while it doesn't matter exactly where the measure is in your data, you can easily move it by selecting the measure and using the Home Table drop-down menu.

你可以像使用其它任何表格列一样使用度量值:只需将它拖放到报表画布或可视化效果字段中。You can use a measure like any other table column: just drag and drop it onto the report canvas or visualization fields. 度量值还可与切片器无缝集成,快速分割数据,这意味着你只需定义一次度量值,然后就可将其用于不同的可视化效果。Measures also integrate seamlessly with slicers, segmenting your data on the fly, which means you can define a measure once, and use it in many different visualizations.

计算 DAX 函数是一个功能强大的函数,它可实现各种类型的有用的计算,对于财务报表和视觉对象尤为有用。The Calculate DAX function is a powerful function that enables all sorts of useful calculations, which is especially useful for financial reporting and visuals.

创建计算表Create calculated tables

计算表是 DAX 的一个功能,可用于表达一众新增建模功能。Calculated tables are a function within DAX that allows you to express a whole range of new modeling capabilities. 例如,如果你希望进行不同类型的合并联接或以函数公式的结果为基础创建随即变化的新表,使用计算表可以达到此目的。For example, if you want to do different types of merge joins or create new tables on the fly based on the results of a functional formula, calculated tables are the way to accomplish that.

要创建计算表,请转到 Power BI Desktop 中的数据视图(可从报表画布的左侧激活该视图)。To create a calculated table, go to Data view in Power BI Desktop, which you can activate from the left side of the report canvas.

从“建模”选项卡选择新建表,以打开公式栏。Select New Table from the Modeling tab to open the formula bar.

在等号的左侧键入新表的名称,在右侧键入要用于形成该表的计算。Type the name of your new table on the left side of the equal sign, and the calculation that you want to use to form that table on the right. 完成计算后,新表将显示在模型的“字段”窗格中。When you're finished your calculation, the new table appears in the Fields pane in your model.

创建后,即可像使用任何其他表的关系、公式、报表那样使用计算表。Once created, you can use your calculated table as you would any other table in relationships, formulas, and reports.

浏览基于时间的数据Explore your time-based data

使用 Power BI 分析基于时间的数据非常容易。It's easy to analyze time-based data with Power BI. Power BI Desktop 中的建模工具自动包括一些生成字段,在这些字段中,你可通过一次单击深化到年份、季度、月份和日期。The modeling tools in Power BI Desktop automatically include generated fields that let you drill down through years, quarters, months, and days with a single click.

当你在报表中使用日期字段创建表格可视化效果时,Power BI Desktop 将自动包括按时间段分解的信息。When you create a table visualization in your report using a date field, Power BI Desktop automatically includes breakdowns by time period. 例如,Power BI 会自动将日期表中的单个日期字段分为年、季度、月和日,如下图中所示。For example, the single date field in the Date table was automatically separated into Year, Quarter, Month and Day by Power BI, as shown in the following image.

可视化效果默认在 级别显示数据,但是你可以通过打开视觉对象右上角的 向下钻取 来更改它。Visualizations display data at the year level by default, but you can change that by turning on Drill Down in the top right-hand corner of the visual.

现在,当你单击图表中的条幅或线段时,它将深化到时间层次结构的下一级别,例如从 季度Now when you click on the bars or lines in your chart, it drills down to the next level of time hierarchy, for example from years to quarters. 你可以继续深化,直到到达层次结构的最精细级别,在本例中即为 You can continue to drill down until you reach the most granular level of the hierarchy, which in this example is days. 若要在时间层次结构中逆向移动,请单击视觉对象左上角的向上钻取To move back up through the time hierarchy, click on Drill Up in the top left-hand corner of the visual.

你还可以深化视觉对象上显示的所有数据,而非只是所选字段,方法是使用钻取全部双箭头图标,它也位于视觉对象的右上角。You can also drill down through all of the data shown on the visual, rather than one selected period, by using the Drill All double-arrow icon, also in the top right-hand corner of the visual.

只要你的模型具有日期字段,Power BI 就会为不同的时间层次结构自动生成不同的视图。As long as your model has a date field, Power BI will automatically generate different views for different time hierarchies.

若要返回单个日期(而不是使用日期层次结构),只需右键单击“ 字段 ”列中的列名称(在下图中,列名称为“ InvoiceDate ”,然后从显示的菜单中选择列名称,而不是选择“ 日期层次结构 ”。To get back to individual dates rather than using the date hierarchy, simply right-click the column name in the Fields well (in the following image, the name of the column is InvoiceDate), then select the column name from the menu that appears, rather than Date Hierarchy. 然后,你的视觉对象会显示基于该列数据的数据,而无需使用日期层次结构。Your visual then shows the data based on that column data, without using the date hierarchy. 需要返回以使用日期层次结构?Need to go back to using the date hierarchy? 没问题!只需再次右键单击并从菜单中选择“日期层次结构”。No problem - just right-click again and select Date Hierarchy from the menu.

后续步骤Next steps

祝贺你!Congratulations! 你已经完成了本部分的 Power BI 引导学习课程。You've completed this section of the Guided Learning course for Power BI. 了解有关数据 建模 的信息后,即可学习下一部分中的有趣知识: 可视化Now that you know about modeling data, you're ready to learn about the fun stuff waiting in the next section: Visualizations.

如上所述,本课程按照 Power BI 中的常见工作流构建知识:As mentioned before, this course builds your knowledge by following the common flow of work in Power BI:

  • 将数据导入 Power BI Desktop,然后创建报表。Bring data into Power BI Desktop, and create a report.
  • 发布到 Power BI 服务,你可在该服务中创建新的可视化效果和构建仪表板Publish to the Power BI service, where you create new visualizations and build dashboards
  • 与他人(尤其是差旅人员)共享你的仪表板Share your dashboards with others, especially people who are on the go
  • Power BI Mobile 应用中查看共享仪表板和报表并与其交互View and interact with shared dashboards and reports in Power BI Mobile apps

虽然你可能不会亲自动手进行所有操作,但你将 理解 这些仪表板的创建方式、其连接到数据的方式...完成本课程时,你将能够创建自己的仪表板。While you might not do all that work yourself, you'll understand how those dashboards were created, and how they connected to the data... and when you're done with this course, you'll be able to create one of your own.

下一部分见!See you in the next section!

恭喜!

非常顺利。You are on a roll. 请继续认真学习 Power BI!Keep up the good work in learning about Power BI! 你已完成 Microsoft Power BI 指导式学习的“建模”部分。You've completed the Modeling section of Microsoft Power BI Guided learning. 接下来是可视化效果。Next up is visualizations.

你已了解操作方式...

下一个教程

可视化Visualizations

作者

  • Davidiseminger
  • olprod
  • Alisha-Acharya