0845 643 64 63

Pivot

Excel Cube Pivot drillthrough limited to 1000 rows

When browsing a cube using Excel 2007, you can drillthrough the measures to display up to 1000 rows of the transaction level source data.

I often get asked whether this limit of 1000 rows is configurable – well the good news is yes it is.

There is an option in the actions tab of the BIDS cube designer which allows you to specify the maximum rows, but helpfully this is ignored by Excel. Instead, you have to set it in Excel when you create a pivot.

Just click “Options” on the “PivotTable Tools” ribon, then in the “Change Data Source” dropdown click on “Connection Properties“. In this screen, just change the “Maximum number of records to retrieve” property.

Excel 2007 Pivot Options

Excel 2007 and SSAS 2008 Error

I was working on a new SSAS 2008 cube today, and came across an error which Google was unable to help with. I thought I’d post the solution here to help anyone else who may encounter it.

The cube in question will be primarily be accessed using Excel 2007, so I’d been dutifully testing it along the way to ensure all was well. And then, after a number of changes the following error appeared when connecting to the cube from Excel to create a pivot table.

Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server.

Contact the OLAP cube administrator and, if necessary, set up a new data source to connect to the cube

Connecting and querying the cube via SSMS or BIDS worked without error (hense I didn’t spot the error sooner!).

A quick Google revealed a number of posts regarding this error, but they all related to attributes containing invalid characters when accessed from Excel 2000 Or problems with translations and locale settings in the .oqy file. Neither of these was the cause here, so I had to go back and recreate every change I had made step by step to track the problem.

Well, I’m please to report that in the end it was nothing more that a simple spelling mistake in a named set. One of the dynamic named sets in the cube calculations referred to a specific member of a dimension, which was spelled slightly incorrectly. (Simplified example..)

CREATE DYNAMIC SET CURRENTCUBE.[Set1]
 AS {[Dimension].[Attribute].[Value1],
     [Dimension].[Attribute].[Value2WithTypo]
    };

When querying calculated measures through MDX in SSMS, the MDX parser just ignored the problem and only uses the valid members, however it appears as though Excel 2007 is slightly more picky with its cubes.

Useful to know, and even more useful when used as a tool to double check for any errors in the MDX calculations.

Extract Datasource and Query from Excel Pivot

Have you ever tried to reverse engineer an Excel pivot table? It’s not as easy as you would think! Whether you just want to find out the datasource details, or identify the query that was used, there is just no simple way of getting Excel to tell you.

The macro below will loop through every sheet in a workbook, and will document the datasources, SQL or MDX queries as well as the page, row, column and data fields.

To use it, add it into your macros, then select a starting cell where you want te report to be placed and run the macro. It’s pretty raw, and may need some tweaks to suit your requirements but it should give you a good starting point. I use it on Excel 2003 MDX pivots from SQL Server Analysis Services 2005, but I presume it will work on other versions of Excel as well.


Public Sub PivotDetails()
   Dim ws As Worksheet
   Dim qt As QueryTable
   Dim pt As PivotTable
   Dim pc As PivotCache
   Dim pf As PivotField

   For Each ws In ActiveWorkbook.Sheets

      For Each qt In ws.QueryTables
        ActiveCell.Value = "Sheet"
        ActiveCell.Offset(0, 1).Value = ws.Name

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Data Source"
        ActiveCell.Offset(0, 1).Value = qt.Connection

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Query"
        ActiveCell.Offset(0, 1).Value = qt.CommandText
      Next qt

      ActiveCell.Offset(2, 0).Select

      For Each pt In ws.PivotTables

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Pivot Table"
        ActiveCell.Offset(0, 1).Value = pt.Name

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Connection"
        ActiveCell.Offset(0, 1).Value = pt.PivotCache.Connection

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "SQL"
        ActiveCell.Offset(0, 1).Value = pt.PivotCache.CommandText

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "MDX"
        ActiveCell.Offset(0, 1).Value = pt.MDX

        For Each pf In pt.PageFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Page"
            ActiveCell.Offset(0, 1).Value = pf.Name
            ActiveCell.Offset(0, 2).Value = pf.CurrentPageName
        Next pf

        For Each pf In pt.ColumnFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Column"
            ActiveCell.Offset(0, 1).Value = pf.Name
        Next pf

        For Each pf In pt.RowFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Row"
            ActiveCell.Offset(0, 1).Value = pf.Name
        Next pf

        For Each pf In pt.DataFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Data"
        Next pf

      Next pt
   Next ws
End Sub
The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon