在 PowerPivot 中组合多个数据集
本质上，我想做的是使用 Excel 2010 PowerPivot 中的切片器创建交互式仪表板。
我有来自 sql 数据库的 3 个不同的数据集，它们共享“维度”。
- 收到的订单
- 销售订单积压 - 主要是当前正在生产和等待的产品 发货或开票
- 发票销售
每个数据集共享某些“维度”，例如客户、产品、时间段、类别等。
我希望为每个业务流程都有一个特定的图表，并让切片器同时过滤每个图表时间。当有人选择 ACME Rockets 作为客户时，我希望所有三个图表都更新并仅显示有关 ACME Rockets 的信息。
我可能可以使用 union 语句来完成此操作，然后对每个图表进行硬过滤以仅显示其数据的一部分，但我希望得到更直观的东西。
谁能告诉我如何使用 PowerPivot 实现这一目标？
Essentially what I want to do is to create an interactive dashboard using slicers in Excel 2010 PowerPivot.
I have 3 different data sets from a sql database which share "dimensions."
- Incoming Orders
- Sales Order Backlog - essentially what products are currently in production and waiting
to ship or invoice - Invoiced Sales
Each data set shares certain "dimensions" such as Customer, Product, Time Period, Category, etc.
I want to have a specific chart for each of the business processes and have the slicers filter each chart at the same time. When someone chooses ACME Rockets as a customer, I want all three charts to update and show only information about ACME Rockets.
I could probably accompish this wiht a union statement and then hard filter each chart only to show it's subsection of the data, but I was hoping for something more intuitive.
Can anyone point me toward how I would accomplish this with PowerPivot?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以一次创建四个图表并删除一个，默认情况下它们将全部连接到相同的切片器。或者，如果您已经创建了三个单独的图表并拥有一个切片器，您希望将它们全部连接到：
You can create four charts at once and delete one and they will all be connected to the same slicers by default. Or, if you have already created three separate charts and have one slicer you want to connect them all to:
如果这些表与底层模型中的事实表相关，那么共享切片器应该很容易。您要做的是将表示共享维度的切片器“连接”到您感兴趣的所有数据透视表/图表。
例子：
1. 在sheet1上插入数据透视图
2. 将共享维度属性添加到切片器窗格
3. 将另一个数据透视图插入同一张工作表 (sheet1)
4. 将您有兴趣切片的另一个事实的任何度量添加到该图表的值窗格中
5.选择代表共享dim属性的切片器
6. 单击“选项”选项卡，然后单击“数据透视表连接”，或者右键单击切片器并单击“数据透视表连接”
7. 在您想要此切片器“切片”的所有数据透视表上放置一个复选框:) - 在本例中，它将是“PivotTable1 - Sheet1 Chart1 的数据”或类似的内容
另外检查 Rob Collie 提供的很棒的 powerpivot 资源（@ powerpivotpro）位于http://powerpivotpro.com，了解使用主工作表的示例，其中所有切片器都连接到其他上的所有其他枢轴床单。
最好的问候，
@HrvojePiasevoli
It the tables are related to the fact tables in the underlying model then it should be really easy to have shared slicers. What you have to do is "connect" the slicers representing shared dimensions to all pivottables/charts that you are interested in.
Example:
1. insert pivot chart on sheet1
2. add a shared dimension attribute to the slicers pane
3. insert another pivotchart to the same sheet (sheet1)
4. add any measure from another fact that you are interested in slycing to the values pane for that chart
5. select the slicer representing the shared dim attribute
6. click on the "Options" tab and then on "PivotTable Connections" or right-click on the slicer and click "PivotTable Connections"
7. put a checkbox on all pivottables that you want for this slicer to "slice" :) - in this example it would be "PivotTable1 - Data for Sheet1 Chart1" or something like that
Additionally check a great powerpivot resource by Rob Collie (@powerpivotpro) at http://powerpivotpro.com for examples of using a main sheet with all slicers connected to all other pivots on other sheets.
Best regards,
@HrvojePiasevoli
您使用的是 PowerPivot 2010 还是 PowerPivot 2012？它是什么样的数据源？
我会在导入数据集时编写一个联合。我看到您将三个数据集合并到一个事实表中，其中“状态”类别包含“传入”、“积压”、“已开票”。您需要一个字段来过滤，即状态。这将基于记录来源的数据集。
您希望所有图表都在同一张纸上吗？在这种情况下，请选择“四个图表”选项，然后删除其中一个图表。如果添加切片器，它将自动应用于工作表上的所有图表。如上所述，按“状态”类型过滤每个图表。为“客户”创建一个切片器。这应该会给你上面描述的内容。
如果这没有回答你的问题，请大声喊叫。
Are you using PowerPivot 2010 or PowerPivot 2012? What kind of data source is it?
I would write a union when importing my data sets. I see you merging the three data sets into one fact table with a 'status' category containing 'incoming', 'backlog', 'invoiced'. You need a field to filter on, i.e. status. This will be based on the dataset the record is sourced from.
Do you want all the charts on the same sheet? In that case, choose the 'four charts' option, and delete one of the charts. If you add a slicer, it will automatically apply to all the charts on the sheet. Filter each chart by a 'status' type as described above. Create a slicer for 'customer'. That should give you what you described above.
Shout if this didn't answer your question.
除了已经说过的内容之外，如果您使用 PowerPivot 2012，请务必执行以下操作：
1) 使用新图表视图验证所有适当的关系在数据导入时检测并创建
2) 添加或更新 PowerPivot 中表之间的任何有效关系
注意：您可能必须“修复”源数据，以便连接的结果符合业务要求。您可以在导入之前编写 T-SQL 查询检查进行验证，或者可以将 SSIS 与数据分析任务结合使用，然后在开始菜单中的数据分析查看器工具（在 SSIS 下列出）中查看配置文件的输出。
In addition to what's already been said, if you are using PowerPivot 2012, be sure to do the following:
1) Use the new diagram view to verify that all of the appropriate relationships were detected and created on data import
2) Add or update any valid relationships between the tables in PowerPivot
Note: you may have to 'fix' your source data so that the joined results conform to business requirements. You can just write a T-SQL query check to verify prior to import, or you could use SSIS with Data Profiling Task and then view the output of the profile in the Data Profile Viewer tools (listed under SSIS) in the start menu.