Posts Tagged Excel

Software Development Tools Taxonomy


Introduction

This is a blog post which describes the taxonomy mentioned in the preceding Display Hierarchical Data With Visio and Excel.

This taxonomy is very much a work in progress. I can guarantee that this taxonomy will evolve and change. The taxonomy will grow as the classes of tools classified expands to meet all of the “niches” which the development software environment contains.

Why Produce a Taxonomy of Software Development Tools

This represents one of the starting steps in the development of Software Architecture for area I’m working in. My view on a Software Architecture is that encompasses the tools, as well as the architecture of the solutions within the architecture.

What does the Tools Taxonomy lead towards, in the development of an architecture? There are number of elements of software architecture which flow from having a Tools Taxonomy. These elements of a software architecture which include:

  • Some degree of control over the tools used in the group.
  • The taxonomy allows for the identification of the toolset which is preferred for development. A degree of control over the toolset used can be important in any the software development group. There are number of direct benefits which can flow from having a good understanding of the tools portfolio of a development group which include:
    • minimising of the costs of team members moving between project teams, by using the same set of tools is projects is an important goal.
    • Rationalising the number of tools used in the group, and hence the licence costs for tools. This can only be realised when you have the luxury of being able to rationalise the toolset being used.
  • The Tools Taxonomy is needed if you wish to map systems which the teams develops, or maintains, to the tools which are used in the construction of those systems. This is import if you ever need to do an impact analysis of removing, or upgrading, a tool.

The Taxonomy

The following is an image of the taxonomy (a Visio Organisation Chart – produced using the Organisation Chart Wizard – see the previous posts: Display Hierarchical Data With Visio and Excel). The taxonomy is heavily focused on the Software Development process and tools.

Development Software Taxonomy

The Excel source for this can be found here (Software Development Tools Taxonomy).

Conclusion

This is very much a work in progress.

This taxonomy could provide a starting point for the development of a tools taxonomy which is specific to your requirements.

Advertisements

, , , , , ,

1 Comment

Display Hierarchical Data With Visio and Excel


Introduction

This is a post on how to display any hierarchal data in Visio. It is one of those post which shows the benefit of using the Microsoft Office suite, when you know how to make them “dance to your tune”.

This shows how to “bend” the “Organization Chart Wizard” (the US spelling on the screen) to display an arbitrary set of hierarchical data. In this case I have been using it go generate a presentation of a “Taxonomy of Software”, or a “Software Portfolio Taxonomy” (if you prefer – I’m not exactly what title to give it yet). I’ll blog about the taxonomy, and some of the design decisions, I’ve built in a subsequent blog post.

The Data

The data which is “presented” by Visio, is a Excel Table in an Excel Work Book. The columns I am using are shown beside. This is the top of theExcel_Visio_data taxonomy, “Software Portfolio” is the one top element, which has a couple of children shown. You can probably go to any depth (number of levels down from the top), or width (I’ve only tried one top element, but I’ve only tried one top thus far), I’ve not found where the limits are in this approach.

One Tip For Creating the Data

The hierarchical relation “hangs together” on the basis of the strings in the “Parent” and “Name” columns. To make sure that these are going to link together correctly, use Excel’s copy and paste functions to duplicate the strings between the columns. Being a lazy typist, I always do this.

You do need to have you wits about you when you are setting up the data. Visio is doing all the hard work drawing the hierarchical tree, any mistakes (things not linking up the way you wanted) and you just go back fix the data, and run the wizard again.

The Method

I’ll show the individual steps here, so that you should be able to follow.

Step 1 – Select the Organization Chart WizardVisio_OrgChartWizard

This is under the Business Templates in the “Create New” part of Visio. This is the starting point for the process.

Step 2 – Create Organization Chart FromVisio_Wizard1

Here select the “Information that’s already stored in a file or database”.

Tip: Make sure you have saved and closed the Excel workbook which has the data you about to use. If you don’t you’ll get an error from one of the following wizard screens.

Step 3 – Select the Data Source Visio_Wizard2

This is where we start down the data from Excel path.

I have not tried any other data source, but I’m certain that those should also work.

Step 4 – Select the Excel Workbook file Visio_Wizard3

Just select the Excel Workbook which you have set up with the data to be presented in the hierarchical structure.

Step 5 – Selecting The Name and “Next Up” Fields“Visio_Wizard4

This is the point where using an Excel Table in the Workbook brings a benefit. The system knows how to get the names form the columns of the table.

Step 6 – Selecting The Field which is Displayed Visio_Wizard5

This is where we are setting the field which is “put in the boxes” which Visio is going to draw is defined.

Step 7 – Selecting Shape Data FieldsVisio_Wizard6

If you other data columns you would like to have attached into each of the object created by this process, this where you specify them.

I’ve not used this option, but it should work. If you are using people, then phone numbers and locations could be extra data which you would attach.

Step 8 – The Pagination of the OutputVisio_Wizard7

I have found that what I’ve wanted is all of the hierarchy on the one Visio page. I do some shuffling around to get it to “fit”, but that’s simple stuff to do.

Step 9 – The EndVisio_Wizard9

This is the end wizard screens. Visio will next “whir it’s cogs”, and present you with a hierarchal display of the data you have fed into it.

Step 10 – The ResultsVisio_OrgChartSnap

The opposite is a screen grab from Visio. All of the text and lines are generated by the wizard. A bit of quick shuffling and I get it all onto one page.

Applying a Theme and Effects takes the simple monochrome present into something which the “boss” or “client” will like.

Conclusions

I hope this saves you some time when next confronted with lots of data which you need to build a presentation from.

This approach saves a lot of work in Visio, and the sort of work which I dislike doing.  I really hate drawing boxes, adding text to them and drawing lines between them, especially when there is a smarter way to achieve the result.

, , , , , ,

4 Comments

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

, , , , ,

4 Comments

%d bloggers like this: