Excel add-in for Olap cubes/analysis Services & PowerPivot

A few months ago, I stumbled across an Excel plugin Smartpivot for the analysis of SSAS OLAP data, which is a very useful feature, and if your BI solution is using SSAS, then look at this stuff and be sure to have a lot of inspiration for you if your bi The report client uses Excel, so this is simply a must-have tool in the home, in recent months I have used piecemeal time to imitate and improve some of the basic functions, and according to specific project customization to expand the more user-friendly features, no technical difficulties, Mainly for the existing project to do the customization function is not convenient to open, here only to introduce common basic functions, crossing after reading if you feel useful to leave the mailbox, I sent the installation package to you.

Here is the official introduction of Smartpivot, you can download the trial version

Http://www.devscope.net/products/SmartPivot

The following describes the general features of my current version of Xpivot, and also welcomes suggestions for improvement.

1. Logon cross-domain authentication, which mainly solves the client and the server is not in the same domain environment, but also want different client users with different dimension access rights, in the plug-in implementation of cross-domain authentication, specifically not detailed, please look at the previous blog, then the version and now there is not much difference

2. source config configuration data source, the configuration of multiple data sources with a vertical line split, the first configuration item "Cube" is unique, the following several configuration items everyone knows, need to explain is "Integrated Security", if configured it equals SSPI, Do not prompt you to login, will default to use the current Windows user identity cube or DW, if either cube or DW does not have a "Integrated Security" configuration item, will pop up the login box asks for the identity password, if the cube and DW " Integrated Security "are not configured, will use the same input identity log cube and DW, the server needs to be the cube and DW configuration of the same login identity and password

3. New PivotTable, which is nothing special, similar to "Existing Connections" in the "Data" menu, simply simplifies the procedure by inserting a PivotTable directly into the current cell, in addition, If the new workbook does not have "Existing Connections", it will be created according to the currently selected data source and the identity of the login pivottable

4. New Table, this is similar to the previous function, is inserted in the current cell QueryTable, but will pop-up window to let you enter SQL, the only thing is that you can use the SQL script annotated/**/configuration of the parameters, script examples:

Select Top /* A1 */  - /* A1 */ *  from DimDate

If the A1 cell of the current QueryTable sheet is empty, the record of top 100 is removed by default, and when you modify the contents of the A1 cell, the QueryTable script is triggered and modified, and you are prompted to refresh immediately, rather than excel QueryTable has a much more flexible way of sending parameters by default. By the way, the data source of Excel QueryTable is not supported if it is from SQL Server, if it is from Microsoft query, although it supports the parameter, there are many restrictions on the executed script.

5. Toggle fields, shrinking folding PivotTable Field List, as shown, this feature is compatible with Excel2010 and 2013, because Excel2013 itself has similar functionality, so this is only meaningful on Excel2010 version

6. Copy address, copy the selected cell range address (which can be contiguous or discontinuous multiple cells) to the Clipboard to paste the data reference address on the pinned report template to enable automatic synchronization of raw after PivotTable or QueryTable refresh Data to the fixed template sheet.

7. Filter automatic filter, as shown in the left image below, if you need to do a multi-dimension, and the number of options is limited, it is still manual operation, and if too many options, you can use this function, as shown in the figure below, locate the current cell on a PivotTable filter, Then click the Filter button in the menu, the pop-up window lists the currently selected options, in the edit box to enter all the options to select the confirmation, you can automatically complete the filtering work.

8. Synchronize Filters synchronous filter, similar to the function of slicer slicer, but the Slicer has its functional limitations, it is not as flexible as this way.

As shown below three PivotTable filter different country respectively, modify the filter name of a and C to country AC, modify the filter name of B to country B

Then clicking "Synchronize Filters" in the menu will create a PivotTable for you to set the global filter, add a country filter and modify the filter name to country AC

Click "Synchronize Filters" in the menu again to complete the sync results as

9. Drill down, when a PivotTable row and column are occupied by various dimensions, if you want to continue to drill down to a new dimension, not only visually feel cluttered, and drill down performance will be affected, as shown in the figure below to locate the current cell to B7, and then click "Drill Down "will create a new PivotTable for you as shown below, the dimensions on all columns are migrated to the filter area, then you can put other dimensions you want to observe in the row and column area, the service side of the calculation is greatly reduced, so the front-end data loading efficiency will increase a lot

Excel add-in for Olap cubes/analysis Services & PowerPivot