I am accessing OLAP SSAS Cubes on a 2005 SQL Server using Excel 2007 pivot tables and finding that refreshing some of the tables is taking >10 minutes. My coworkers seem to think it is a sad reality, but I am wondering if there are alternatives I should be looking into.
我使用Excel 2007数据透视表访问2005 SQL Server上的OLAP SSAS多维数据集，并发现刷新某些表大于10分钟。我的同事似乎认为这是一个悲伤的现实，但我想知道是否有其他我应该研究的选择。
Some thoughts I have had:
我有过一些想法：
Obviously if I could upgrade the server hardware I would, but I am merely an analyst with no such powers, so I don't think hardware improvements are a great option. The same is true of moving to a newer SQL server, which I imagine would also speed up the process.
显然，如果我可以升级服务器硬件，但我只是一个没有这种能力的分析师，所以我不认为硬件改进是一个很好的选择。迁移到更新的SQL服务器也是如此，我想这也会加快这个过程。
Would updating to a newer version of excel speed up the process?
更新到更新版本的excel会加快这个过程吗？
I came across this: http://olappivottableextend.codeplex.com/, which gives me access to the MDX, which is apparently comically inefficient (Sounds like the macro recorder for VBA to me), so would changing the MDX around (I know a bit of it and the queries it gives for the pivot tables don't seem that complicated) be an option?
我遇到了这个：http：//olappivottableextend.codeplex.com/，它让我可以访问MDX，这显然是非常低效的（听起来像VBA的宏录音机给我），所以会改变MDX（我知道）一点点，它为数据透视表提供的查询似乎并不复杂）是一个选项吗？
Would running MDX outside of excel be an option? I can write the queries, but I imagine it would not be as simple as the pivot table is.
在excel之外运行MDX是一种选择吗？我可以编写查询，但我想它不会像数据透视表那样简单。
It just seems like OLAP Cubes are a great solution in a lot of ways and these are some massive pivot tables processing quite a bit of information, but if there is a reasonable way to speed up the whole process I would love to know more about it.
看起来OLAP Cubes在很多方面都是一个很好的解决方案，这些是一些处理相当多信息的大型数据透视表，但是如果有一种合理的方法可以加快整个过程，我很想知道更多关于它的信息。 。
Thanks for your thoughts SO.
谢谢你的想法。
1 个解决方案
#1
7
There are many ways to access SSAS cubes, but it depends on what you are trying to achieve.
有很多方法可以访问SSAS多维数据集，但这取决于您要实现的目标。
Excel tends to be used by business because
Excel倾向于被业务使用，因为
- Its already installed
- 它已经安装好了
- It is a familiar business tool
- 这是一个熟悉的商业工具
- Easy to use
- 使用方便
- Requires no developer intervention
- 不需要开发人员干预
Other alternatives to Excel to access the cube include
Excel的其他替代方法包括访问多维数据集
- SQL Server Analysis Services (management studio) via cube browser or mdx directly
- SQL Server Analysis Services（管理工作室）通过立方体浏览器或mdx直接
- SQL Server Reporting Services
- SQL Server Reporting Services
- Bespoke development (such as c#) utilising AdomdConnection
- 使用AdomdConnection进行定制开发（例如c＃）
- SQL Server (management studio) via OpenQuery
- SQL Server（管理工作室）通过OpenQuery
If you have been using Excel to access the cube so far, you will probably decide that none of the other tools quite cover your needs and you will end up sticking with it.
如果到目前为止您一直使用Excel访问多维数据集，您可能会认为其他任何工具都不能满足您的需求，您最终会坚持使用它。
Assuming that Excel is the right tool for you, you should then move on to why is it slow. The list of possibilities (not including hardware / software) is long, but here are some;
假设Excel是适合您的工具，那么您应该继续讨论为什么它很慢。可能性列表（不包括硬件/软件）很长，但这里有一些;
- It could be that it is external contention (to your project) on network / database / disk resource. The colume of data may be accumulating over time.
- 可能是网络/数据库/磁盘资源上的外部争用（对您的项目）。数据的长度可能随着时间的推移而累积。
- The cube may not be paritioned.
- 立方体可能没有分区。
- The questions you ask of it may be getting more complex.
- 你问的问题可能会变得更加复杂。
- The cube aggregations may not be utilised for your needs.
- 多维数据集聚合可能无法满足您的需求。
- Cube partitioning may be missing
- 多维数据集分区可能会丢失
- Cube structure may be inefficient as its supporting many-to-many relationships
- 多维数据集结构可能效率低，因为它支持多对多关系
- User / query volume may have increased
- 用户/查询量可能已增加
To try to address the problem I would
试着解决这个问题
- Assess the data that you require within the cube (and maybe limit the cube to a rolling x month window)
- 评估多维数据集中所需的数据（并可能将多维数据集限制为滚动的x月窗口）
- Log your queries and apply Usage Based Optimisation
- 记录查询并应用基于使用情况的优化
- Monitor cube usage via SQL Server Profiler
- 通过SQL Server Profiler监视多维数据集的使用情况
- Review the structure of your cube design
- 检查多维数据集设计的结构
- Attempt similar queries with other tools (both across the network and local to the cube) to establish where the issue lies
- 尝试使用其他工具（通过网络和多维数据集本地）进行类似的查询，以确定问题所在
- These two sites may help you if you establish Excel is the week point Excel, Cube Formulas, Analysis Services, Performance, Network Latency, and Connection Strings OR Excel, Cube Formulas, Analysis Services, Performance, Network Latency, and Connection Strings (which is on page 57 of SQLCAT's Guide to BI and Analytics)
- 这两个站点可以帮助您，如果您建立Excel是周点Excel，多维数据集公式，分析服务，性能，网络延迟和连接字符串或Excel，多维数据集公式，Analysis Services，性能，网络延迟和连接字符串（这是在SQLCAT的BI和分析指南第57页上）
#1
7
There are many ways to access SSAS cubes, but it depends on what you are trying to achieve.
有很多方法可以访问SSAS多维数据集，但这取决于您要实现的目标。
Excel tends to be used by business because
Excel倾向于被业务使用，因为
- Its already installed
- 它已经安装好了
- It is a familiar business tool
- 这是一个熟悉的商业工具
- Easy to use
- 使用方便
- Requires no developer intervention
- 不需要开发人员干预
Other alternatives to Excel to access the cube include
Excel的其他替代方法包括访问多维数据集
- SQL Server Analysis Services (management studio) via cube browser or mdx directly
- SQL Server Analysis Services（管理工作室）通过立方体浏览器或mdx直接
- SQL Server Reporting Services
- SQL Server Reporting Services
- Bespoke development (such as c#) utilising AdomdConnection
- 使用AdomdConnection进行定制开发（例如c＃）
- SQL Server (management studio) via OpenQuery
- SQL Server（管理工作室）通过OpenQuery
If you have been using Excel to access the cube so far, you will probably decide that none of the other tools quite cover your needs and you will end up sticking with it.
如果到目前为止您一直使用Excel访问多维数据集，您可能会认为其他任何工具都不能满足您的需求，您最终会坚持使用它。
Assuming that Excel is the right tool for you, you should then move on to why is it slow. The list of possibilities (not including hardware / software) is long, but here are some;
假设Excel是适合您的工具，那么您应该继续讨论为什么它很慢。可能性列表（不包括硬件/软件）很长，但这里有一些;
- It could be that it is external contention (to your project) on network / database / disk resource. The colume of data may be accumulating over time.
- 可能是网络/数据库/磁盘资源上的外部争用（对您的项目）。数据的长度可能随着时间的推移而累积。
- The cube may not be paritioned.
- 立方体可能没有分区。
- The questions you ask of it may be getting more complex.
- 你问的问题可能会变得更加复杂。
- The cube aggregations may not be utilised for your needs.
- 多维数据集聚合可能无法满足您的需求。
- Cube partitioning may be missing
- 多维数据集分区可能会丢失
- Cube structure may be inefficient as its supporting many-to-many relationships
- 多维数据集结构可能效率低，因为它支持多对多关系
- User / query volume may have increased
- 用户/查询量可能已增加
To try to address the problem I would
试着解决这个问题
- Assess the data that you require within the cube (and maybe limit the cube to a rolling x month window)
- 评估多维数据集中所需的数据（并可能将多维数据集限制为滚动的x月窗口）
- Log your queries and apply Usage Based Optimisation
- 记录查询并应用基于使用情况的优化
- Monitor cube usage via SQL Server Profiler
- 通过SQL Server Profiler监视多维数据集的使用情况
- Review the structure of your cube design
- 检查多维数据集设计的结构
- Attempt similar queries with other tools (both across the network and local to the cube) to establish where the issue lies
- 尝试使用其他工具（通过网络和多维数据集本地）进行类似的查询，以确定问题所在
- These two sites may help you if you establish Excel is the week point Excel, Cube Formulas, Analysis Services, Performance, Network Latency, and Connection Strings OR Excel, Cube Formulas, Analysis Services, Performance, Network Latency, and Connection Strings (which is on page 57 of SQLCAT's Guide to BI and Analytics)
- 这两个站点可以帮助您，如果您建立Excel是周点Excel，多维数据集公式，分析服务，性能，网络延迟和连接字符串或Excel，多维数据集公式，Analysis Services，性能，网络延迟和连接字符串（这是在SQLCAT的BI和分析指南第57页上）