March 27, 2015 at 11:10 am
We are seeing the MDX Designer take a long time to open and to select filters. Running a Query is fast, but selecting filters from dimension members that only have 2 to 10 members can take up to a minute to display and the same is true when dragging the member into the Filter pane. Executing the query is not an issue and the returned results even 1000's of rows is fairly quick. This applies to PowerPivot and Report Builder.
March 30, 2015 at 10:13 am
The problem with the MDX designer in those applications is that it often brings back more records than you need because it always uses the lowest level of granularity in a hierarchy. You're better off just writing an MDX query to return what you actually need and then using that to build your report.
If you are worried about it being a network issue (you mentioned a WAN) then you can look at the DISCOVER_COMMANDS, DISCOVER_CONNECTIONS, and DISCOVER_TRANSACTIONS DMVs and also run a profile whilst these slow queries are running - this will tell you whether or not the problem is with the OLAP server or a slow network.
https://msdn.microsoft.com/en-us/library/bb934103.aspx
https://msdn.microsoft.com/en-us/library/bb934105.aspx
https://msdn.microsoft.com/en-us/library/hh270263.aspx
http://blogs.technet.com/b/josebda/archive/2009/03/31/sql-server-2008-i-o-performance.aspx
March 30, 2015 at 10:59 am
Thanks for the reply. I will take a look at the commands as you suggested. We can't just write MDX and actually depend on the Designer to allow our business users to build their own queries for Power Pivot. What's interesting is the data returned to the results pane is quick, it's building the filter part of the query which is painful. Even a member with 2 or 3 values like True/False from the Date Dimension will take a full minute to display the 2 or 3 options within the Expression area. Or, just dragging the member to the Filter pane will take almost a minute to show up. It almost seems like the Designer is getting hung up somewhere.
March 31, 2015 at 1:34 am
rayishome (3/30/2015)
We can't just write MDX and actually depend on the Designer to allow our business users to build their own queries for Power Pivot.
Tread carefully there. Perhaps consider using a tabular model for smaller business areas that need PowerPivot to get at their data (actually if it's a tabular model you can bypass PowerPivot and go directly to the fancy stuff, like PowerView/PowerMaps etc.) and use your multidimensional cube for higher-level "corporate" needs. Either way if they are looking at very granular data then perhaps OLAP isn't the optimal solution.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply