Archive for June, 2010

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

, , , , ,

4 Comments

SharePoint 2010 – Gripes, Groans and Gremlins


Introduction

I’ve been trying to get ADOMD, or AMO, to work from a BDC in SharePoint 2010. I’ve been building the BCD in Visual Studio, which has been working quite well. The problem I ran into was getting an exception when trying to open the connect to Analysis Services on another server.

Gremlins

The exception  I was getting was:

A first chance exception of type 
'Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException' occurred in
Microsoft.AnalysisServices.AdomdClient.dll Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException:
The connection either timed out or was lost. --->
System.IO.IOException: Unable to read data from the transport connection:
An existing connection was forcibly closed by the remote host. --->
System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) --- End of inner exception stack trace --- at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader() at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord() at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType() --- End of inner exception stack trace --- at Microsoft.AnalysisServices.AdomdClient.XmlaClient.EndRequest() at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CreateSession(
ListDictionary properties, Boolean sendNamespaceCompatibility) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.
Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.CreateSession(
Boolean sendNamespaceCompatibility) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open() at AnalysisServicesCubesBDCModel.BdcModel1.SharePointCubeDetails.PopulateListOfCubes()

Which seems to suggest that the client is getting kicked off the server. Or, my connection was being rejected by the Analysis Services Server. This diagnosis proved to be incorrect!

It turned out that SharePoint was also shutting down the connection. NB: Both ADOMD and AMO are probably using the Analysis Services Web Service “under the covers” to get the information.

How to get SharePoint to let the Analysis Services Web Services Call happen?

Solution and Gripe!

Once the “penny dropped” that it was SharePoint “stopping” the connection, a bit of browsing the SharePoint class libraries yielded the following solution.

public static IEnumerable<Cars> ReadList()
 {
 //TODO: This is just a sample. Replace this simple sample with valid code.
 IEnumerable<Cars> carsList = null;
 Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate()
 {
 carsList = Cars.GetList();
 });

 return carsList;
 }
Gripe: I don’t like embedding a “wholesale” privilege elevation in a system. 

I’ll have to look more deeply into SharePoint application security to see if there is a more finer grained way of controlling the security context of an application.

GROAN !

As part of the implementation of the linkage between Analysis Services and SharePoint, I had in mind pass into SharePoint some URL’s. So in the BDC designer in Visual Studio 2010, I tried to use the URI type.

The following is a screen garb of the types which the BDC “supports”:clip_image002

You can “scratch” Uri. I created a property on the Object being passed back of type Uri. The added that property into the BDC definition. When the BDC was mapped into SharePoint as an External List, the property did not appear (no error, just plain missing in action).

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

Windows Live Tags: SharePoint 2010,Gripes,Groans,Gremlins,ADOMD,Visual Studio,exception,Analysis Services,server,Microsoft,AnalysisServices,AdomdClient,AdomdConnectionException,connection,System,IOException,Unable,data,Sockets,SocketException,NetworkStream,Read,Byte,size,BufferedStream,DimeRecord,ForceRead,Stream,length,ReadHeader,DimeReader,ReadRecord,TcpStream,GetResponseDataType,XmlaClient,EndRequest,CreateSession,ListDictionary,properties,Boolean,AdomdConnection,XmlaClientProvider,IXmlaClientProviderEx,ConnectToXMLA,Open,AnalysisServicesCubesBDCModel,SharePointCubeDetails,PopulateListOfCubes,client,diagnosis,Service,information,Call,Solution,Gripe,libraries,IEnumerable,Cars,ReadList,RunWithElevatedPrivileges,GetList,elevation,context,GROAN,Object,definition,External List,csharpcode

Digg This

Leave a comment

ADOMD Connection Strings


, ,

Leave a comment

%d bloggers like this: