Build Pivot Tables from Analysis Services Cube in C#


Introduction

This post details some of the sorting out I’ve done to automatically generate (with C# code) a Pivot Table from an Analysis services Cube.

Why?

Well I have 500+ analysis services cubes I want to make multiple Excel sheets for. Each Excel work (one workbook for each cube), will have multiple sheets. These Excel workbook will be the primary display

“School of hard knocks” lessons

SNAGHTML810733Make sure that your using the right interop for Excel

using Excel = Microsoft.Office.Interop.Excel;

The above is a variation of the C# using statement (or is that a directive – I’ll look that up later), which is a bit of a sanity saver, all of the rest of the code identifies the Excel objects succinctly.

  • Make sure that your using the correct interop assembly. There are a number of these installed on my work machine and Visual Studio 2010 is not exactly helpful with the way it loads up all of the available assemblies.
    The key point in the screen grab are the Version 14.0.0.0 is the one you want.

 

SNAGHTML319ede

  • Make use of the Object Browser in Visual Studio. This has been invaluable in sorting out how to do some of the things I needed to implement. The Excel library is well documented, the information on the arguments and return type are invaluable when working with the Excel Automation Layer.
  • This one is a “style” lesson (probably learned working with COM automation in C++ many years ago, but still relevant in C# today). I always use this sort pattern, the key point follow:
    Excel.Application oXL = null;
    Excel.Workbook oWB;
    Excel.Worksheet oSheet;
    try
    {
        // Start Excel Automation Layer
        oXL = new Excel.Application();
        // Let me see what's happening
        oXL.Visible = true;
        // Need a workbook
        oWB = oXL.Workbooks.Add();
        // Select the active sheet - default you get 3 sheets
        oSheet = oWB.ActiveSheet;
    

    • Minimise the casting. This lets the compiler and intelsense help a much as
      possible when your writing the code.
    • Always declare variables as intermediate values where possible. This again makes the compiler work for you.
    • Always use “early-bound” calls (the is a COM automation concept, basically early bound are resolved at compile time, late bound at run time). The above two point keep things in the “early-bound” space, which performs better (at runtime), and is easier to code (IMHO).
  • Make use of the new C# 4.0 Name Parameters feature. The following is a fragment which is from the functioning code in which using the feature adds to the readability of the code.
    Excel.PivotTable tab = cache2.CreatePivotTable(
        TableDestination: oTargetSheet.Range["A3"], 
        TableName:"Guess What", 
        ReadData:false);
    

 

The Main Method 

        static void Main(string[] args)
        {
            List<String> Dimensions = new List<String>();
            List<String> SheetNames = new List<string>();
            string odc_Sample = @"C:UsersCraigDocumentsMy Data Sourceslocalhost SimpleCube Samples 2.odc";
            Excel.Application oXL = null;
            Excel.Workbook oWB;
            Excel.Worksheet oSheet;
            try
            {
                // Start Excel Automation Layer
                oXL = new Excel.Application();
                // Let me see what's happening
                oXL.Visible = true;
                // Need a workbook
                oWB = oXL.Workbooks.Add();
                // Select the active sheet - default you get 3 sheets
                oSheet = oWB.ActiveSheet;
                // Open the ODC file 
                Excel.WorkbookConnection wbCon = oWB.Connections.AddFromFile(odc_Sample);
                // Get hold of the Pviot Cache
                Excel.PivotCaches pch = oWB.PivotCaches();
                // Create the target sheet
                Excel.Worksheet oTargetSheet = oWB.Worksheets.Add(Type: Excel.XlSheetType.xlWorksheet);
                // Create the Pivot table data relationship
                Excel.PivotCache cache2 = pch.Create(
                        SourceType: Excel.XlPivotTableSourceType.xlExternal, 
                        SourceData: wbCon); 
                // Create the Pivot Table
                Excel.PivotTable tab = cache2.CreatePivotTable(
                    TableDestination: oTargetSheet.Range["A3"], 
                    TableName:"Guess What", 
                    ReadData:false);
                // This finds the dimensions, 
                // but does not work for dimensions with Hierarchies 
                foreach(Excel.CubeField field in tab.CubeFields)
                {
                    if (field.Name.Contains("[Measures]"))
                        continue;
                    Dimensions.Add(field.Name);
                }
                // Layout a sheet with each of the dimensions as the row labels.
                for (int i = 0; i < Dimensions.Count; i++)
                {
                    // Pulls apart the ODC file description into a "user friendly" form
                    string Title = ParseDescription(wbCon.Description);               
                    // First layout has the Pivot Table already created
                    if (tab != null)
                    {
                        // Make a sheet name which is descriptive of the content
                        SheetNames.Add(MakeSheetName(Dimensions[i]));
                        oTargetSheet.Name = SheetNames[i];
                        // Layout the Analysis Cube into a Pivot table
                        LayoutCube(tab, Dimensions[i], SheetNames[i], Title, oTargetSheet);
                        tab = null;
                    }
                    else
                    {
                        // Make a sheet name which is descriptive of the content
                        SheetNames.Add(MakeSheetName(Dimensions[i]));
                        oTargetSheet = oWB.Worksheets.Add(Type: Excel.XlSheetType.xlWorksheet);
                        oTargetSheet.Name = SheetNames[i];
                        // Create the data linkage for the new pivot table
                        Excel.PivotCache pivCache = pch.Create(
                                SourceType: Excel.XlPivotTableSourceType.xlExternal,
                                SourceData: wbCon);
                        // Create the pivot table
                        tab = pivCache.CreatePivotTable(
                            TableDestination: oTargetSheet.Range["A3"],
                            TableName: SheetNames[i], 
                            ReadData:false);
                        // call the layout routine
                        LayoutCube(tab, Dimensions[i], SheetNames[i], Title, oTargetSheet);
                        tab = null;
                    }
                }               
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                Debug.WriteLine(ex);
                throw;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
                throw;
            }
            finally
            {
                if(oXL != null)
                    oXL.Quit();
            }
        }

Key Features of the Main:

  • This version is specifically for SQL Server Analysis Services Cubes. The cubes which this version will layout are only ones with:
    • Simple Dimensions. That is no hierarchies in the dimensions, the logic for the identification of dimensions needs significant modifications to cater for hierarchies.
    • More than one Measure. There is a “bug” (not sure where) in that the layout logic (which follows) does not “like” one Measure that PivotTable.DataLabelRange does not work for a single Measure (code following).
  • Pivot tables are built from the PivotCache object. The PivotCache, which uses a WorkbookConnection object as a data source.
  • The WorkbookConnection (in this example) is coming from an ODC file on the file system. The ODC file can also come from a SharePoint 2010 Data Connections library, if you want this simple substitute the URL to the connection for the file name.
  • This version uses ODC (Office Data Connectivity) Files. I’ve made this choice because all of the generated Excel workbooks will end up in SharePoint, and I don’t want to encode the connection strings into the workbooks, rather I’ll be using the SharePoint Data Connections Library to hold them. This leaves one place to change connection details for all of the BI application.

The Layout Method

 

        /// <summary>
        /// Generates the layout of a pivot table
        /// </summary>
        /// <param name="tab">The excel pivot table object</param>
        /// <param name="stubField">The dimension which will be put in the row labels zone</param>
        /// <param name="SheetName">The descriptive lable for the sheet</param>
        /// <param name="Title">The title for Pivot table, not part of the pivot table, but a cell on the sheet providing information to the user</param>
        /// <param name="sheet">The sheet which contains the Pivot Table</param>
        private static void LayoutCube(Excel.PivotTable tab, string stubField, string SheetName, string Title, Excel.Worksheet sheet)
        {
            // Set some of the Pivot table options
            tab.DisplayEmptyRow = true;
            tab.DisplayEmptyColumn = true;
            // "Style" the table
            tab.ShowTableStyleColumnStripes = true;
            tab.ShowTableStyleColumnHeaders = true;
            tab.TableStyle2 = "PivotStyleMedium23";
            // Generate the layout
            foreach (Excel.CubeField field in tab.CubeFields)
            {
                if (field.Name.Contains("[Measures]"))
                {
                    // Measures are the data fileds
                    field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
                }
                else
                {
                    if (field.Name == stubField)
                    {
                        // Make the selected dimension the row field
                        field.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                    }
                    else
                    {
                        // Page field = filters on the pivot table.
                        field.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
                    }
                }
            }
            // Make the column headings (data labels) look "better"
            Excel.Range DataLabels = tab.DataLabelRange;
            DataLabels.ColumnWidth = 11;
            DataLabels.WrapText = true;
            DataLabels.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            // Drop the title into the sheet
            Excel.Range titleRange = sheet.Cells[2, 4];
            sheet.Cells[2, 4] = Title;
            titleRange.Value2 = Title;
            titleRange.Font.Name = "Calibri";
            titleRange.Font.Size = "14";
            titleRange.Font.Bold = true;

        }

The Layout Method : Key Features

  • The XlPivotFieldOrientation enum does all of the “hard work” in generating the layout.
  • The ordering of the Data Columns is not “controlled” at present. Enhancing this will be another voyage of discovery.
  • PivotTable.DataLabelRange seems to fail when only one Measure is in the Pivot Table.

 

Technorati Tags: ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Windows Live Tags: Build,Pivot Tables,Analysis Services,Cube,Excel,Microsoft.Office.Interop.Excel,Visual Studio,Object Browser,Automation,Layer,Application,Worksheet,Visible,Workbooks,ActiveSheet,PivotTable,CreatePivotTable,TableDestination,Range,TableName,ReadData,Dimensions,SheetNames,Sources,SimpleCube,Samples,WorkbookConnection,Connections,AddFromFile,Pviot,Cache,PivotCaches,Create,Worksheets,Type,XlSheetType,PivotCache,SourceType,XlPivotTableSourceType,SourceData,Hierarchies,CubeField,CubeFields,Measures,Title,LayoutCube,Runtime,Debug.WriteLine,Quit,Features,Server,Cubes,modifications,Measure,DataLabelRange,SharePoint,connection,Generates,dimension,SheetName,cell,options,DisplayEmptyColumn,Style,ShowTableStyleColumnStripes,ShowTableStyleColumnHeaders,Orientation,XlPivotFieldOrientation,Page,DataLabels,ColumnWidth,WrapText,HorizontalAlignment,XlHAlign,Drop,Cells,Font,Calibri,Bold,Columns,xlWorksheet,xlExternal

Digg This

Advertisements

, , , , ,

  1. #1 by Raghavendra on May 13, 2011 - 12:24 am

    Hi,

    First of all Great Post .

    I was trying to do a similar thing in SharePoint 2010, when i try to add odc from local folder it works like charm, but when i try to add odc from DataConnetionLibrary using oWB.Connections.AddFromFile(@”http://svvc1vm10/4/DC/CME.odc”); it throws an exception “Exception from HRESULT: 0x800A03EC”.

    I have tried using elevated privileged and enabling anonymous access but no luck. Any idea why this error is coming up.

  2. #3 by Mriganka on May 2, 2011 - 9:24 pm

    Very helpful. But I am unable to find ParseDescription() and MakeSheetName() method.

    • #4 by aussiecraig on June 8, 2011 - 3:15 pm

      They were internal methods, which did “stuff” which is specific to the data (andd organisation I work for) I was processing. You can just code an internal method to replace them.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: