Category Archives: SQLServerPedia Syndication

SSAS: Errors in the metadata manager. The dimension with ID of…

Crazy error today. In Analysis Services, (SSAS), some jobs got hung, sync command got hung, and what ended up happening is a cube on the default instance was totally unusable.

Try to delete, rename, resync, redeploy, anything, and I would get the error..

Errors in the metadata manager. The dimension with ID of..

Couldn’t do anything. The solution? I had to stop the SSAS service, go to the OLAP data directory under my SQL install and remove the folder for that cube. I then started the SSAS Service, and the SSAS Database was gone (finally!). I redeployed and reprocessed and everything is working again. Whew :)



SSIS – Custom Control Flow Component – Execute SQL Job And Wait

Sometimes you have some pretty complex ETL’s going in SSIS, and you might have multiple projects/solutions that need to call other SSIS Packages or SQL Agent Jobs and you have a pretty big production going on. You might have an ETL solution that needs to kick off other packages, and you can either import those into your solution or call them where they lie on the file system/SQL server, etc. You might have to call some SQL agent jobs, and most often they are async calls (you dont need to wait for them to come back) and this works nicely, I do this all the time. The Execute SQL Agent Task in SSIS works nice, or you can just call the SQL statement to execute a job, either way, it kicks off the job and then just comes back successful right away, and doesn’t care if the job actually succeeds. You might want this in some scenarios, and the built in functionality works great.

But what if you want to just call an existing SQL Agent job and actually wait for the job to finish (success or failure)? There isn’t anything that I could see built in to SSIS to do this, sp_start_job is asynchronous, so you are out of luck there. I figured I could call sp_start_job, then create a for loop in SSIS and just check the status every X seconds/minutes, but I would have to either make this a package I could use everywhere or reproduce the same logic in multiple solutions, so I shied away from that solution.

What I decided to do was build a custom SSIS control flow task in .NET that will execute a SQL agent job and check the status and wait until it finishes. A disclaimer: This is going to be a lot of code :) also, it could be improved (but what couldn’t?) – this was a 1.5-2 hour experiment.

First, I created a VS2008 C# class library. I tried adding a UI to my task, but I couldn’t get it working so there is some code there for that but it’s commented out.

here is what my solution looks like:

Capture

import the correct namespaces:

 
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Net;
using System.Net.NetworkInformation;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime.Design;
using System.Data.SqlClient;

Next, you need to create the actual skeleton/wrapper for your component. You can see I have two properties, job name, server name. It could be expanded to have the connection string or use an existing connection in SSIS, I wasn’t that ambitious. The “Execute” method basically just calls some functions and waits for result.

 
namespace ExecuteSQLJobAndWaitControlTask
{
 
    [DtsTask(
        Description = "Execute SQL Job And Wait", 
        DisplayName = "Execute SQL Job And Wait", 
        TaskContact = "Steve Novoselac",
        TaskType = "SSIS Helper Task",
        RequiredProductLevel = DTSProductLevel.None)]
    public class ExecuteSQLJobAndWaitControlTask : Task, IDTSComponentPersist
 
    {
        private string _jobName;
        private string _serverName;
 
        /// <summary>
        /// The sql job name
        /// </summary>
        public string JobName
        {
            get { return _jobName; }
            set { _jobName = value; }
        }
 
        /// <summary>
        /// The sql server name
        /// </summary>
        public string ServerName
        {
            get { return _serverName; }
            set { _serverName = value; }
        }
 
        public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
        {
            try
            {
 
                StartJob();
 
                System.Threading.Thread.Sleep(5000);
 
                do
                {
                    System.Threading.Thread.Sleep(5000);
 
 
                } while (IsJobRunning());
 
                if (DidJobSucceed())
                {
                    return DTSExecResult.Success;
                }
                else
                {
                    return DTSExecResult.Failure;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return DTSExecResult.Failure;
            }
        }
 
        public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
        {
 
            if (string.IsNullOrEmpty(_serverName) || string.IsNullOrEmpty(_jobName))
            {
                componentEvents.FireError(0, "You must specify a JobName and ServerName in the properties", "", "", 0);
                return DTSExecResult.Failure;
            }
            else
            {
                return DTSExecResult.Success;
            }
 
        }
 
        void IDTSComponentPersist.LoadFromXML(System.Xml.XmlElement node, IDTSInfoEvents infoEvents)
        {
            if (node.Name != "ExecuteSQLJobAndWaitTask")
            {
                throw new Exception(string.Format("Unexpected task element when loading task - {0}.", "ExecuteSQLJobAndWaitTask"));
            }
            else
            {
                this._jobName = node.Attributes.GetNamedItem("JobName").Value;
                this._serverName = node.Attributes.GetNamedItem("ServerName").Value;
            }
        }
 
        void IDTSComponentPersist.SaveToXML(System.Xml.XmlDocument doc, IDTSInfoEvents infoEvents)
        {
 
            XmlElement taskElement = doc.CreateElement(string.Empty, "ExecuteSQLJobAndWaitTask", string.Empty);
 
            XmlAttribute jobNameAttribute = doc.CreateAttribute(string.Empty, "JobName", string.Empty);
            jobNameAttribute.Value = this._jobName.ToString();
            taskElement.Attributes.Append(jobNameAttribute);
 
 
            XmlAttribute serverNameAttribute = doc.CreateAttribute(string.Empty, "ServerName", string.Empty);
            serverNameAttribute.Value = this._serverName.ToString();
            taskElement.Attributes.Append(serverNameAttribute);
 
            doc.AppendChild(taskElement);
        }

And then I have some helper methods, this is where the meat and potatoes are for this task. Now of course I could have the connection string once, etc. Like I said, it was a quick thing :). The heart of it is though, starting the job, checking if it is still running, and then after, if it succeeded. Pretty simple.

 
 
        private bool DidJobSucceed()
        {
            SqlConnection dbConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" + ServerName);
            SqlCommand dbCmd = new SqlCommand("exec msdb.dbo.sp_help_job @job_name = N'" + JobName + "' ;", dbConn);
            dbConn.Open();
 
            SqlDataReader dr = dbCmd.ExecuteReader();
            dr.Read();
            int status = Convert.ToInt32(dr["last_run_outcome"]);
            dr.Close();
 
            dbConn.Close();
 
            if (status == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
 
        private bool IsJobRunning()
        {
 
            SqlConnection dbConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" + ServerName);
            SqlCommand dbCmd = new SqlCommand("exec msdb.dbo.sp_help_job @job_name = N'" + JobName + "' ;", dbConn);
            dbConn.Open();
 
            SqlDataReader dr = dbCmd.ExecuteReader();
            dr.Read();
            int status = Convert.ToInt32(dr["current_execution_status"]);
            dr.Close();
 
            dbConn.Close();
 
            if (status == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
 
        }
 
        private void StartJob()
        {
            SqlConnection dbConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" + ServerName);
            SqlCommand dbCmd = new SqlCommand("EXEC dbo.sp_start_job N'" + JobName + "' ;", dbConn);
            dbConn.Open();
            dbCmd.ExecuteNonQuery();
            dbConn.Close();
        }
   }

Now, to install this you need to register it in the GAC (global assembly cache), and then copy to the DTS/Tasks folder. Depending if you have VS2005 or VS2008 (or both) your gacutil path might be different.

cd\
c:
cd C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin
gacutil /uf "ExecuteSQLJobAndWaitTask"
gacutil /if "C:\Projects\SSISCustomTasks\ExecuteSQLJobAndWait\bin\Debug\ExecuteSQLJobAndWaitTask.dll"
copy "C:\Projects\SSISCustomTasks\ExecuteSQLJobAndWait\bin\Debug\ExecuteSQLJobAndWaitTask.dll" "C:\Program Files\Microsoft SQL Server\90\DTS\Tasks"

I have found once you have done that, you need to actually restart your SSIS service to make it work, but then you can use it in new Visual Studio SSIS packages.

Capture

Once you drag it on your package, you can set the JobName and ServerName property (from the properties window – remember, no GUI). and it should run.

Some notes:

If you kill the job, the SSIS task will fail (obviously). If you kill the SSIS package, the job will keep running. Maybe a future enhancement will be to capture the SSIS package fail/cancel and kill the job. Maybe :)

Attached is the source code for the task (Vs2008 C#) http://stevienova.com/ExecuteSQLJobAndWait.rar

This has been testing with BIDS VS2005. I take no responsibility if this blows up your system, computer, server, the world, etc.

Happy ETL’ing!



SQL 2005: SSIS – Pushing Data to MySQL using Script Component Destination

Sometimes, I just wonder why things that are useful in previous versions of things get removed. In SQL 2000, in DTS, there was an ODBC destination, you could write (insert, update, etc) to a foreign (non MSFT SQL) system easily. DB2 – no prob, MySQL – heck yea. For whatever reason, in SQL 2005 Integration Services (SSIS), that ability was removed (in SQL 2008 SSIS there is an ADO.NET Destination that can update ODBC sources, so they brought some functionality back).

I need to write to a MySQL database pulling data from a SQL Database, using SSIS 2005. What are the options? Well, the best I could come up with was a Script Component Destination in my DataFlow, this is how I did it:

1) Create a new SSIS Package, throw a DataFlow on the Control Flow, Add your connections (let’s say SourceDB_SQL – your source data, and then DestDB_MySQL – your MySQL destination, it needs to be a ADO.NET Connection, you need to install the MySQL connection – I installed this http://dev.mysql.com/downloads/connector/odbc/5.1.html)

2) In your DataFlow, create your OLEDB Source and get your query returning data, throw a Script Component on the Data Flow and make it a Destination.

ms_01

3) Open the Script Component, set your input columns up, and then your Connection Manager

ms_02

4) Open the actual script, and you just have to add a few lines of code:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc
 
Public Class ScriptMain
    Inherits UserComponent
 
    Dim mySQLConn As OdbcConnection
    Dim sqlCmd As OdbcCommand
 
    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        mySQLConn = CType(Me.Connections.MySQLDatabase.AcquireConnection(Nothing), OdbcConnection)
    End Sub
 
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
 
        sqlCmd = New OdbcCommand("INSERT INTO steve_test(ShipTo, YearValue) VALUES(" & Row.ShipTo & ", '" & Row.YearValue & "')", mySQLConn)
        sqlCmd.ExecuteNonQuery()
 
    End Sub
 
    Public Overrides Sub ReleaseConnections()
        Me.Connections.MySQLDatabase.ReleaseConnection(mySQLConn)
    End Sub
End Class

Run it and you are done! Easy. Now you can write data into MySQL using SQL 2005 SSIS (or any ODBC destination if you can get it to work :))



Office 2010: Excel 2010, What-If Analysis aka Microsoft Finally has Built in Cube/OLAP Writeback!

Digging into the blog post from earlier this summer I wanted to see what was new and exciting in Excel 2010.

Recently I have been working on an cube and we want to be able to budget right from the cube. There are also many other cubes/scenarios where the ability to writeback to the cube would be awesome. Some BI tools have had this for many years! Microsoft had something similar with a Excel 2002/2003 add-in, but it has been removed. Also, there are many 3rd party tools to allow this. You could also write your own macros or VBA/.NET code to do this as well, but what was always missing was the ability to do writeback directly from an Excel (OLAP) PivotTable. With Excel 2010, this functionally finally shows up.

First, you need a cube, and you need to enable writeback on a partition. This will automatically create a table in your database where you have your data warehouse. Lets say you have a table FactBudgets, and you enable writeback, SSAS will create a table WriteTable_Budgets. This is a trivial example I went through to test this functionality, but I just wanted to exhibit the feature of the writeback.

01_writeback

Turn on writeback, deploy and process your SSAS cube, and then open Excel 2010. Connect to your cube, and then in the PivotTable ribbon menu, on options, there is a button to turn on “What-If Analysis”. Turn it on. :)
02_whatif

Once you turn on the setting, you can then begin writing back values to your cube, right from Excel. If you click on a cell in your writeback value, you can just change it. See on the screen shot below, the value I changed, the little purple triangle, tells me it has a changed value from what is in the data source.

03_valuechanged
04_valuechangedmenu

You can see its telling me the value changed, I can Discard the change and other options. After you have your values set, you want to publish them, which you do back on the PivotTable ribbon menu:

05_publishchanges

As you can see, Microsoft has finally created a viable solution for writing back values into your OLAP cubes without the need for 3rd party software or coding. Finally!

One thing to note, if you try to write back to a value that is in a partition that doesn’t have writeback enabled, you will get an error.

06_error

Now, think of the possibilities with SSAS OLAP writeback and Excel, now that we can actually use it out of the box!



SSASMeta – C# App to Log Info About SSAS Objects

I manage some servers that have many cubes. OK, a lot of cubes (60+ on one). I needed some way to output a report of last processed time, last schema update, etc. Now, there are about 5 different ways to do this (one being the SSAS Stored Procedure Project), but this is what I came up with. I wrote a 100 line C# app to take a server name, loop through the SSAS DB’s, cubes, measures, partitions, and dimensions and log info about them.

Here is a c# code snippet of a function that just outputs to the console, the app I have actually logs the info to a SQL Server database and then I can write reports off that.

     private static void LogSSASInfo(string serverName)
        {
            var server = new Server();
            server.Connect(serverName);
 
            foreach (Database database in server.Databases)
            {
                Console.WriteLine(database.Name + " " + database.LastUpdate + " " + database.EstimatedSize / 1024 + " " + database.CreatedTimestamp);
 
                foreach (Cube cube in database.Cubes)
                {
                    Console.WriteLine("     Cube: " + cube.Name + " " + cube.LastProcessed + " " + cube.LastSchemaUpdate);
 
                    foreach (MeasureGroup measureGroup in cube.MeasureGroups)
                    {
                        Console.WriteLine("         Measure Group: " + measureGroup.Name + " " + measureGroup.LastProcessed);
 
                        foreach (Partition partition in measureGroup.Partitions)
                        {
                            Console.WriteLine("             Partition: " + partition.Name + " " + partition.LastProcessed);
                        }
                    }
                }
 
                foreach (Dimension dimension in database.Dimensions)
                {
                    Console.WriteLine(" Dimension: " + dimension.Name + " " + dimension.LastProcessed);
                }
 
                Console.WriteLine("");
                Console.WriteLine("------------------------------------------------");
                Console.WriteLine("");
            }
 
            server.Disconnect();           
        }

As you can see, it isn’t the most elegant code in the world, but it works. In order to get this to work in your project, you need to reference the Microsoft.AnalysisServices assembly.

ssasmeta

Use your imagination, you could make an app wrap that function above and log info for all the SSAS instances on your network. There have been a few times already in the last year where I have found some cube or measure group not updating correctly and a report like the one I can get now will help dealing with that challenge.



Office 2010: Excel 2010 – New Buttons on Ribbon for Pivot Tables – Custom Named Sets!

Just this week I blogged about adding Named Sets in VBA. Well lo and behold, in Excel 2010, there is a button “Fields, Items, & Sets” that lets you define your own Named Sets. Either with MDX or based on rows/columns you have on your pivot table

image

Works pretty slick! There goes the need for the custom VBA solution, which is fine by me. Although I am disappointed you still can’t create your own calculated measures. The OLAP Pivot Table Extensions add-in lets you, so I wonder why the built in functionality still doesn’t let you.



T-SQL: Using CROSS APPLY to Turn 2 Queries Into 1

Parent/Child. Order/Line. Header/Detail. Report/Sub-Report. We have all ran across these database designs and have had to write queries off of them. You usually end up having the parent id, and you need to get the children. Or you have a list of parents and need to loop through them, looking up the children records and doing something with them. But what if you just want a list of the children id’s (or names, or whatever). Do you really need to loop through the parents and lookup all the children, (and possibly look through those)? You can do JOIN’s and you can get the data in a tablular format, but how do you rollup those children records?

Using the AdventureWorks DB in SQL 2005, an example using Manager/Employee:

SELECT DISTINCT mgr.ManagerId, e.EmployeeId
	FROM HumanResources.Employee mgr
	INNER JOIN HumanResources.Employee e ON mgr.ManagerId = e.ManagerId

Results:

image

But really we want to rollup those employees, ending up with one manager/employee record, ex: 3, [4,9,11,158,263,267,270] … for this, try CROSS APPLY

SELECT DISTINCT ManagerId, Employees = LEFT(emp.list, LEN(emp.list)-1) 
	FROM HumanResources.Employee mgr
	CROSS APPLY
	(
	SELECT CONVERT(VARCHAR(4),EmployeeId) + ',' AS [text()] 
		FROM HumanResources.Employee e
		WHERE mgr.ManagerId = e.ManagerId
 
		ORDER BY EmployeeID
		FOR XML PATH('')
	) emp (list)
WHERE mgr.ManagerId IS NOT NULL

Results:

image

As you can see from the results, we rolled up our employees into one record per manager, into a comma delimited list. Think of some possibilities of using CROSS APPLY in your apps or stored procs/reports to reduce the number of queries you might have to write, or number of trips to the database you might have to do. Happy T-SQL’ing :)



SSAS 2005/2008: Creating Sub-Cubes Using XMLA, Variables, and Named Query Where Statements

I blogged a few weeks ago about creating Local Cubes with XMLA and ascmd.exe, and that is pretty cool, works great. There are some snags though if you use a server that is 64 bit. You can’t connect to the local cubes via .NET, Excel, or any other way. There are no drivers for local .cub files for 64-bit Windows. What a downer, and I am not going to wait around for them to come out. So in the mean time, you can easily create “sub cubes” as well in SSAS.

One easy way to create sub cubes is using this method. If you have one dimension that all your measures relate to (or two, usually they all relate to date), but say one dimension that relates to everything is Location, DimLocation. And you want to create sub cubes based on a set of locations. Well what I did was this.

1) In my Data Source View (.dsv) in my SSAS solution, I replaced all views/tables with Named Queries. (you will see why later)

2) Once I have my “main” cube deployed (and processed if you like, doesn’t matter), I need to create an XMLA to create a sub cube.

b4127dbd2c0563eb093464d312c87269

3) In the newly created XMLA you have to edit some things

a) I edit the Database name, and Id

b) search for msprop:QueryBuilder="SpecificQueryBuilder" – this is where your named queries for all your tables are. You can go ahead and add a where statement to each one (eg: WHERE LocationId IN (1,2,3))

c) I usually wrap the whole XMLA in a Batch, and then at the bottom after the </Create> I put a process XMLA

  <process>
    <type>ProcessFull</type>
    <Object>
      <DatabaseID>MySubCubeDatabase</DatabaseID>
    </Object>
  </process>

4) Run your XMLA and it should create and process a new SSAS db, creating a pre-sliced version of your original cube.

 

Now, there are other ways to do all this stuff, one of them being <Filter> in XMLA, but I couldn’t get it to work the way I wanted, that is why I went this route, and it just so happens that I am lucky enough for one dim to relate to all measures :)



Using CTE’s to Create Dynamic Pivot Tables in SQL 2005/2008

One of the cool new commands introduced in SQL 2005 was the PIVOT command (and UNPIVOT). One thing that has always irked me about PIVOT was that you need to “hard code” the column names you want to actually pivot on. This is usually fine for a static report, say you want to pivot on Apples, Oranges, Bananas. Great. But what if you want to pivot on Month/Year? “Apr 2009”, “May 2009”, etc, etc. Still ok, if you want to hardcode your month years and they never change. But in some cases, you need to keep adding the current “Month Year” combo to the query.

You can go the old fashioned route and create a dynamic SQL statement and do an EXEC (@dynamicSql) , or you can try to figure out a way to do your PIVOT dynamically.

What I did here is create a dataset and added a numeric ordering and then pivoted on that instead of the the actual value “Month Name Year” (eg: “May 2009”). That way, my pivot is always the same, 6 months worth, or whatever you like, and you can pass in parameters for the date filters in the top query to get your correct range.

-- get your distinct month/year vals into a temp table
-- need temp table here because CTE doesnt like a subquery and row_number
SELECT DISTINCT CalendarMonthYearName, MonthOfYear,CalendarYear
      INTO #tmp_monthyears
      FROM dbo.DimDate 
      WHERE DATE > '02/01/2009' AND DATE < '07/01/2009'
      ORDER BY CalendarYear DESC,MonthOfYear DESC;
 
-- create a CTE that adds a "Row Number" which is number 1-6
WITH CalMonthYears AS
(
-- add your number
SELECT CalendarMonthYearName AS 'CalendarMonthYearName',
      MonthOfYear,
      CalendarYear,
      ROW_NUMBER() OVER (ORDER BY CalendarYear DESC) AS 'YearMonthNum'  
      FROM  #tmp_monthyears
 
)
SELECT ItemNumber,
      [1],[2],[3],[4],[5],[6]
FROM
(
-- your query to get your data, pivot this data
SELECT YearMonthNum,s.ItemNumber,
      SUM(Quantity) AS 'Quantity'
      FROM DataHistory s
      INNER JOIN dbo.DimDate d ON s.DateKey = d.DateKey
      INNER JOIN CalMonthYears cmy ON d.CalendarMonthYear = cmy.CalendarMonthYearName
GROUP BY YearMonthNum,s.ItemNumber
) AS SourceTable
PIVOT
(
SUM(Quantity)
FOR YearMonthNum IN (
      [1],[2],[3],[4],[5],[6]
      )
) AS PivotTable;
 
DROP TABLE #tmp_monthyears

The sky is the limit with this, you can modify this or think of other ways to use this logic to make your PIVOT commands dynamic, so you don’t have to keep editing stored procedures every time your pivot columns change :)



Excel 2007, OLAP Cubes: Customizable, User Defined Named Sets in Excel 2007 using VBA

In a perfect world, your master data and master data management (MDM) is set up so everyone can see things how they want. Categories of Items, Regions, etc, etc are all defined in your dimension data, and you can create hierarchies, etc that make sense and everyone is happy. But, this is not a perfect world :)

Some users want to see “their” items, “their” regions, etc. And they ask and ask for you to add it to the cube, but you have to deny them. Why? Because if you add their named set, you have to add all of them that are requested, that don’t make sense to 99% of the other people using the cube.

You probably could even go about doing some crazy MDX or something in your cube to create the named sets per user, but then you still have to manage it all in your MDX script in your SSAS project.

I blogged a while ago about the OLAP Pivot Table Extensions on CodePlex , which, is an awesome toolkit, but it is geared around Calculated Measures. I downloaded the code and took a gander here and there and it would be pretty easily modified to work with Named Sets (change xlCalculatedMember to xlCalculatedSet , add a radio button on adding a new calc, etc) – but, its in VS2005, and I have VS2008 (2005 BIDS, not C#), and right now I don’t feel like mucking around with that, maybe someone with more time, and more ambition can do it :) – Or maybe when I get some free time I will take a look, but for now here is a solution.

I took the idea outlined at the bottom half of this blog http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx  about named sets and creating them in macros..

Sub AddNamedSet()
 
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Dim cbf As CubeField
 
Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[My Mountain Bikes]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes")
 
End Sub

That macro is pretty sweet, you can add a named set to your workbook. But, its pretty static. You have to edit the macro every time you want to add more items, it doesn’t update or remove the sets you might have, etc.

What I whipped up quick was an updated macro that lets you build customizable named sets based on data in another worksheet in your Excel file (you can imaging it coming from other sources – another Excel file, a SQL table, etc, etc). I did this with a cube we use at work, but for this example, I changed it to pull off the AdventureWorks DW SSAS DB, AdventureWorks Cube, (SQL 2005 edition)

First off, here is the macro: 

Text File of Macro: Custom_NamedSets_Macro.txt

Excel 2007 Workbook with Macro: Custom_NamedSets.xlsm

Sub AddNamedSet()
 
    ' data sheet top row must be set name [Set Name] and then item numbers after
    Dim sourceData As Worksheet
    Set sourceData = Worksheets("Data")
 
    Dim strName As String
    ' get the name of the set, including brackets []
    strName = sourceData.Range("A1").Value
 
    ' used range is the whole column, rangeVals is the item numbers
    Dim usedRange As Range
    Dim rangeVals As Range
    Dim maxRow As Integer
 
    Set usedRange = sourceData.usedRange
 
    maxRow = usedRange.Rows.Count
 
    ' get the item numbers
    Set rangeVals = sourceData.Range("A2:A" & maxRow)
 
    Dim strFormula As String
    Dim i As Integer
 
    ' loop through and build forumla
    strFormula = "'{"
 
    For i = 1 To rangeVals.Count
       strFormula = strFormula & rangeVals(i, 1) & ","
    Next i
 
    ' remove last comma and add last curly and tick
    strFormula = Left(strFormula, Len(strFormula) - 1) & "}'"
 
    ' get pivot table object
    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)
 
    ' Add a calculated member titled "[MySet]"
    pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
 
    ' Add a set to the CubeField object.
    Dim cbf As CubeField
    Set cbf = pvt.CubeFields.AddSet(Name:=strName, Caption:=Replace(Replace(strName, "[", ""), "]", ""))
 
 
End Sub
 
Sub DeleteNamedSets()
 
    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)
 
    Dim i As Integer
 
    For i = 1 To pvt.CalculatedMembers.Count
        pvt.CalculatedMembers.Item(i).Delete
    Next i
    pvt.RefreshTable
 
End Sub

Now, this was a 15-20 minute VBA hack. It could really use some cleanup, but works. What I did to make it work with Advendture Works was just put the whole “member” string in the data tab of the spreadsheet. In my case at work, I just was using item number and had some of the member string in the macro. But really you can see you could add a named set on each column, or something, you really could make this powerful. And since I added the delete, if they re-run the add, it will recreate the set, so they can modify the data and re-add. It should be expanded on and made a little more robust, but you get the idea. Now, think of how you could use this with your cubes, and how you could get your user’s thinking about ways to use custom named sets!