How to show the multidimensional data in MOLAP using Excel querytable in two-dimensional table

In the case of misuse, misuse of MDX and SSAS, here is a reference to a foreigner's article (http://www.bp-msbi.com/2010/03/when-not-to-write-mdx-and-when-not-to/), Please see the last section (2. No aggregations), taking into account that the crossing speed may not be very power, citing this part of the following:

2. No Aggregationsanother to SSAS gets misused is  whenA lot ofTextual data gets storedinchA large Number  ofBig dimensions, andThose get linkedinchA "factTable”. I have previously worked onA solutionwhereThere were no measure columnsinchThe factTableAt All  andThe cube was used toRetrieve information about dimension ofThe largest dimension called "Member", containing4-5Million customers. The rest were dimensions like“ SignUp date "," Date Suspended "," country ofBirth "," Age Band ", etc.inchTheEndThe main report consisted ofThe information about the members. No data was aggregated apart fromA simpleCount. The entire OLAP solution could has been replaced byA SQL query withAWHEREClause andAnIndex.

The performance of MDX is not as good as SQL when there are too many dimension levels in the query result set, such as the example given by the author above, and attributes such as "Registration date", "nationality", "age", "address", "membership number" in the member dimension may exist in a membership table in the OLTP data structure. Now you need to make a statement of the membership information, if it is a two-dimensional storage of OLTP single table SQL query, performance is much higher than the regular MOLAP MDX query, you can try to use Excel PivotTable to access SSAS, a number of dimension attributes are dragged and dropped on the row, The more levels the dimension is found, the less efficient it is to execute the MDX query, because the default PivotTable-generated MDX script is to make aggregate queries for each layer of dimensions (even if powerpivot-supported flattened pivot performance is bad), PivotTable is good at multi-dimensional data aggregation analysis, and this is not a high repetition of the two-dimensional personal information dimension to do aggregate query is usually not much significance, so the author suggests that this situation should use SQL instead of MDX, then there seems to be nothing to discuss, And the special application scenario I'm describing next is using MDX to solve this kind of problem.

Application scenarios are like this, users want to excel one-stop platform can do multidimensional analysis and can do two-dimensional analysis, multidimensional analysis do not have to say, direct access to SSAS, a powerful PivotTable can almost solve all the analysis needs (reasonable design), But two-dimensional analysis has encountered the above mentioned trouble, users want to do some filter filter in the PivotTable, and then pull up the filtered membership details, including membership card number, age, address and so on, if the information is made into a regular dimension, in the PivotTable line area show, Efficiency is very low, and testing often causes Excel to crash, as explained above. Then it seems that only use SQL to solve the problem, using Excel QueryTable access to a stored procedure, return the member details of the result set to Excel, tricky users again to the problem, so it is difficult to solve the following problems:

1. The end user needs to visualize the member information that you want to show, such as this time just want to see the membership card number, next time you want to see the address and Registration date, SQL implementation of the report is difficult to do this.

2. The end user needs to visualize the configuration filters logic to reduce the number of rows in the result set, and the filter condition is unknown. (They are all PivotTable by the habit)

3. The complexity of authority management is increased, and the management of permissions in SSAS is linked to Windows user, and it is now necessary to do queries in db to get through the authentication Logic of SSAS and DB, either to configure the authentication information in DB, or to implement authentication for SSAS and DB, respectively. It's not so easy to come true.

Note, the above three questions are I based on user feedback information summarized, users do not know the technology, but also by self-service bi this idea dazzled, often put forward some maddening demand

To get to the point, the first thing I think of is PivotTable before importing data, it is possible to choose to use table, PivotTable or PivotChart, as a result, we find that it is not possible to choose to show as table when accessing SSAS.

Originally wanted to use PivotTable Field List visualization operation to make the data presented to the two-dimensional table, and later think is also drunk, since called PivotTable Field List, nature is not for table use, blocked

Or use the VSTO plugin to solve it, the steps are as follows

1. First, the user defined various filters in the PivotTable (support multiple selection), and then click on the plug-in function button "Tabular Report", the example is 917 French man.

What you click "Tabular Report" here is to get the MDX script to the current PivotTable, to a pop-up WinForm form, WinForm to go to the SSAS server for the first time to get the whole cube's field List (script below) , like the PivotTable Field list, is displayed on the WinForm in a tree-like menu.

SELECT              '['+[Measuregroup_name]+']'  asTableName,[Measure_unique_name]  asFieldName from$SYSTEM. Mdschema_measuresWHERE [Cube_name]='@Cube'     and [measure_is_visible]     ORDER  by [Measuregroup_name]    SELECT              [Dimension_unique_name]  asTableName,[Dimension_unique_name]+'. ['+[Level_name]+']'  asFieldName from$SYSTEM. Mdschema_levelsWHERE [Cube_name]='@Cube'     and [Level_number]>0    ORDER  by [Dimension_unique_name]

To add, the above script is able to set whether or not to display those hidden dimension or fact fields, which is useful, such as the member address of the dimension does not want to display to PivotTable use, but also want to do "tabular report" when used

2. On the WinForm, select the dimension field that you want to display in the QueryTable, and then confirm.

The thing to do at this point is to put the MDX script in front and the field selected on WinForm together into an MDX script, the main use is the drillthrough function, the script is spelled as follows:

DRILLTHROUGH MAXROWS/*B1*/ +/*B1*/                SELECT  from [Adventure Works] WHERE([Customer].[Country].&[France],[Customer].[Gender].&[M],[Measures].[Customer Count])                RETURN                 [$Measures].[Customer Count],[$Customer].[Home Owner],[$Customer].[Gender],[$Customer].[state-province],[$Customer].[Number of children at Home],[$Customer].[Customer],[$Customer].[Education],[$Customer].[ City],[$Customer].[ City],[$Customer].[Commute Distance],[$Customer].[Occupation],[$Customer].[state-province],[$Customer].[Marital Status],[$Customer].[Postal Code],[$Customer].[Country],[$Customer].[Customer],[$Customer].[Total Children]

A sheet page is then created automatically, and a querytable to make it accessible to SSAS executes the above script to return the result set as follows:

By the end of this task, the three issues mentioned above have been solved perfectly, the entire process is clear and easy to operate, and does not require any code capability from the end user.

How to display multidimensional data in MOLAP in a two-dimensional table using Excel QueryTable