如何从数据透视表缓存中获取数据? [英] How to take data from PivotTable cache?
问题描述
我已经失去了与源分析服务的连接.但是，数据透视表的缓存中仍然有数据(可以从工具提示中看到).
I have lost the connection to source analytics service. However, I still have data in PivotTable's cache (I can see from tooltips).
如何获取源数据?
- 我正在使用Office365
**我无法使用显示详细信息，因为数据透视表已应用了某些过滤器.而且我无法删除过滤器，因为它要求我连接源.
** I cannot use show details since the pivot table has some filteres applied. And i cannot remove filters since it asks me to connect source.
(And yes I have checked this thread but it didn't work for me: Recreate Source Data from PivotTable Cache. It gives 1004 error.)
推荐答案
对于基于表的数据集，我想到的最简单的方法是:
The easiest way I can think of for a table based data set is:
- 在数据透视表上显示字段列表.
- 删除所有过滤器，列标签，行标签和值
- 在值中添加一个字段.这将在数据透视表中显示一个汇总值.
- 双击该值，将弹出一个包含所有数据的工作表.
- 如果没有，请转到数据透视表选项，然后在数据"标签上选中启用显示详细信息".
但是，此方法不适用于OLAP数据. Excel不会下载整个多维数据集.每次对数据透视表/图表的过滤器或布局进行更改时，它都会查询新的数据切片.因此，即使您可以访问数据透视缓存中的数据，它也不会保存整个多维数据集，而只会显示当前布局所需的切片.您可以创建一个快照多维数据集文件来保存在脱机模式下运行所需的所有数据，但是它要求您至少能够连接到服务器一次才能创建该文件.
This method however will not work with OLAP data. Excel does not download the entire cube; it queries for new data slices with every change to the filters or layout of the pivot table/chart. So even if you could access the data in the pivot cache it would not hold the entire cube, but only the slices needed to show the current layout. You CAN create a snapshot cube file to hold all of the data needed to run in offline mode, however it requires you to be able to connect to the server at least once to create the file.
这篇关于如何从数据透视表缓存中获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
