SQL Server BI Quiz – 2011- Display "Breadcrumbs" for members of a user hierarchy

Recently, I’ve been provided with a generous offer to participate in the SQL Server BI Quiz – 2011 (http://beyondrelational.com/quiz/SQLServer/BI/2011/default.aspx) as a Quiz Master.

You can read my full question here http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/152/display-breadcrumbs-for-members-of-a-user-hierarchy.aspx.

The task was to fill in the MDX expression for a calculated measure ‘Parent Breadcrumbs’ that should display a string consisting of all parent names separated by a ‘ –> ’ for a user hierarchy. So for example for product member “Fender Set – Mountain” (user hierarchy [Product].[Product Categories]) the measure value should be the following string “All Products -> Accessories -> Fenders”.

WITH
MEMBER [Measures].[Parent Breadcrumbs] AS
/*
TODO:
*/

SELECT
{
    [Measures].[Order Count],
    Measures.[Parent Breadcrumbs]
} ON 0,
TopCount
(
    [Product].[Product Categories].[Product]
    ,10
    ,[Measures].[Order Count]
) ON 1
FROM [Adventure Works]
;

Here is the screenshot of the desired result:

Sadly, my question received only a single answer and now that the Quiz is closed I wanted to comment both on the provided answer and my solution for it. Thank you Guenter for participating and providing a very good answer and explanation.

Here is the proposed answer to the question by Guenter but I recommend you read his whole answer as he provided a very good explanation:

member Measures.[Parent breadcrumb] as
generate(
  generate(
    ascendants([Product].[Product Categories].currentmember) as X,
    X.Item(X.Count-X.CurrentOrdinal)
  ),
  [Product].[Product Categories].currentmember.Name,
  " -> "
)



The answer provided was quite satisfactory and contained only a minor difference to the task definition (the returned string would contain the current member’s name also) and a pair of curly braces were missing in the inner generate.

Here is my preferred solution:

MEMBER [Measures].[Parent Breadcrumbs] AS
Generate
(
	Hierarchize
	(
		Ascendants([Product].[Product Categories].CurrentMember.Parent)
	)
	,[Product].[Product Categories].CurrentMember.Name
	,' -> '
)



As Guenter noted in his answer, the right way to get the parent members is to use the Ascendants() function. However, the order of members returned is reversed and we have to order the set somehow. The difference here is I am using the Hierarchize() function to order the set in hierarchical order while he is using another approach that is commonly used to reverse a set in MDX. Another difference is that I am returning the Ascendants of the current member’s parent to fulfill the request accurately (only parent “path” should be displayed in the returned cell) as Ascendants() function returns both the specified member and it’s ascendants in the result. Generate(set, <iteration value>, <separator string>) is used to concatenate the parent member names into the requested format.

MDX is very rich and powerful language and there is always more then one way to achieve the same thing. For illustration I’ll provide another method that uses Exists() function instead of Ascendants():

MEMBER [Measures].[Parent Breadcrumbs] AS
Generate
(
	Head
	(
		Hierarchize
		(
			exists
			(
				[Product].[Product Categories].MEMBERS,
				[Product].[Product Categories].CurrentMember
			)
		)
		,[Product].[Product Categories].CurrentMember.Level.Ordinal
	)
	,[Product].[Product Categories].CurrentMember.Name
	,' -&gt; '
)



This may be a little harder to understand. Exists() function used on a user hierarchy this way will return a set that contains: same member, parent members from all levels above, and child members from all levels below the specified member. Therefore we need to take just a certain number of hierarchically ordered elements from this set. This is achieved by using Head() function with a parameter derived from the current member’s level – the Ordinal of the level. As the Ordinal function is 0-based it will not only get the parent members from this set.

So to wrap it a bit, the question and the solution should introduce you to three MDX functions: Ascendants(), Hierarchize() and Generate().

Finally, I want to thank SQL Server MVP Jacob Sebastian (Blog | Twitter) both for providing me with the opportunity to participate in the SQL Server BI Quiz and for his tremendous efforts in the SQL community and to my dear friend Jason Thomas (Blog | Twitter) for introducing me to the SQL Server BI Quiz on http://beyondrelational.com. I’ll also use the opportunity to congratulate Jason for the update in his marital status – Congratulations and best of luck to both of you!

ExcelStartup PowerShell module v1.1 released

If you are new to ExcelStartup PowerShell module please read the previous post Shortcuts to start Excel with or without PowerPivot add-in.

A very good observation from Dan English (blog | twitter) made me do a small but important change in the behavior of the ‘Excel only’ shortcut.

Here’s the excerpt from the Dan’s full post that you can read here http://denglishbi.wordpress.com/2010/10/08/where-did-my-excel-powerpivot-tab-go-follow-up/

“One thing to watch out though is that if you don’t use the shortcuts to access Excel the next time that it will load up the last option that you ran.  This means that if you do the ‘Excel Only’ shortcut, close Excel, and then click on the Excel shortcut in the start menu that Excel will load normally (without PowerPivot add-in).  To enable the PowerPivot add-in you can follow my steps in the posting above or simply close Excel and then double-click the ‘Excel with PowerPivot’ shortcut on your desktop.”

So, as of v1.1 the “Excel only” shortcut has been changed to disable PowerPivot add-in for the current Excel session (process) only. This enables users to start Excel with PowerPivot by clicking on Excel associated files or just typing Excel in run dialog etc.

If for any reason you want to switch to the old behavior edit the ‘Excel only’ desktop shortcut, click on the Shortcut tab and remove the parameter -reset from the shortcut target.

The link to the download is here: ExcelStartup PowerShell module installation guide.

Thank you all for your interest and support.

Shortcuts to start Excel with or without PowerPivot add-in

You love PowerPivot but you hate waiting for Excel to load just to do something quick-and-dirty?

Thought so, me too! Here’s a quote from Dan English’s blog (blog | twitter) I came across while my PowerPivot add-in wouldn’t load (btw., I had to edit the registry to resolve the situation but that’s another topic):

The other day I launched Excel 2010 to do a quick calculation and changed my mind.  By changing my mind I didn’t mean that I didn’t want to perform the calculation anymore, just decided I didn’t want to wait for Excel 2010 to load.  The reason that it takes a while now to load is the additional add-ins that I have installed which includes PowerPivot.  As long as theses are activated they need to be loaded each time.  Needless to say I am somewhat impatient and decided I would simply use another application to perform the calculation so I cancelled Excel during the load process.

You can read the full post here: http://denglishbi.wordpress.com/2010/06/21/where-did-my-powerpivot-excel-2010-tab-go/.

UPDATE:

Dan has been kind enough to write an update of the mentioned post and you can read it over at his blog: http://denglishbi.wordpress.com/2010/10/08/where-did-my-excel-powerpivot-tab-go-follow-up/. Thanks Dan.

Well, it’s been more than a month now since I’ve created some PowerShell scripts that would get called by clicking on my two custom desktop shortcuts. And finally as I’m off work for the last couple of days , I was able to wrap it all up for others to download and use.

ExcelStartup custom desktop shortcuts

Feel free to download the ExcelStartup PowerShell module installer and do provide some feedback over here or contact me on twitter.

I’ve written here the ExcelStartup PowerShell module installation guide to help you through the setup.

Note: As this is my first PowerShell script I probably haven’t done things in the way an experienced scripter would, so If you are such a person I would love to hear your comments and suggestions.

Special thanks goes to Dan who inspired me to write this script and kindly allowed me to use his name in this blog post.

Happy PowerPivoting.

Importing data from 64-bit Excel in SSIS

Last night I was #sqlhelping someone on twitter having an issue with importing data from excel. The data preview dialog returned results correctly, while the package errored at run time with a connection error. The issue was caused by Run64BitRuntime property of the package being set to true and it is in fact a well known and common issue with 64bit SSIS. Tip: install both 32bit and 64bit version of providers for your data sources if they are available.

Then it occurred to me to try it myself on my setup as I’m running 64-bit W7, 64-bit SQL stack and most importantly the first release of 64-bit Excel 2010.

The obvious path

So, I created a new package in a SSIS project and created a dataflow task. Then dragged an Excel Source from Data Flow Sources and clicked on new connection manager to define the connection to an excel file I was trying to import.

End then problems begin:

If you try to select the sheet from the dropdown (in Table or view Data access mode) the following error dialog jumps in to your face:

The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine.

Simlar but more verbose error is thrown if you try the SQL command as Data access mode with something like

select * from [Sheet1$]

It boils down to provider not being registered on the machine.

Why?

As I have already pointed out – the installed version of Excel 2010 on my machine is 64bit and BIDS being a 32-bit application can not load 64-bit stuff. The ‘Microsoft.ACE.OLEDB.12.0′ provider installed is 64-bit.

The next obvious path

So, you think, I’ll find and install the 32-bit version available from Microsoft Download (http://bit.ly/anbrtk). After downloading and running the setup for  AccessDatabaseEngine.exe you get the following error:

So it turns out you can’t have both 32-bit and 64-bit of the provider installed on the same machine.

Note that this is all due to the fact that BIDS (Visual Studio) is a 32-bit application.

So, is it hopeless?

No not at all. In fact you have two solutions to overcome this issue.

Solution 1 – using the SQL Server Import and Export Wizard

Well, this will work but it is kind of a 1 time solution because it will create an unmanageable package. But let’s go through the steps which are quite straightforward if you have used the wizard before with just a minor tweak. The most important thing is to start the  64-bit version (Import and Export Data (64-bit)).

Ok, start the Import and Export Data (64-bit) wizard and select Microsoft Office 12.0 Access Database Engine OLE DB Provider as shown on the following picture.

Now click on Properties and set the path to your excel file in the Data Source text box:

Clicking on the Test Connection button yields the following error dialog.

This is the minor tweak needed that I mentioned earlier. Switch to All tab in the Data Link Properties dialog and double click on Extended Properties.

Insert “Excel 12.0″ in the Property Value (without quotes) and click OK.

Return to the Connection tab and click Test Connection button. It should suceed now.

Next set the destination. Here I have set it to database TEST on my local named instance of SQL server (localhost\SQL2008).

After clicking next, choose from the options. Lets just leave it at the default for now and click next.

Place a check for the sheet that contains data and either leave the default destination on choose from existing tables.

We’ll skip the mappings part for now and just click Preview to test.

In the Save and Run Package step  of the wizard leave checked Run immediately and check Save SSIS Package with the File system destination option and click next.

I had trouble naming the package so just leave it with default values (New Package.dtsx in My Documents) and click next.

A summary of settings is displayed. Click Finish.

Remember we selected the option to run the package immediately, so the package is started and we get the following screen.

You can test it from SSMS.

Now let’s open the package in Visual Studio 2008 or BIDS.  Click on the Error List pane and note the errors.

You will not be able to work with this dataflow in designer. And that leads us to an alternate solution.

Solution 2 – Using OLE DB Source in DataFlow with SQL Server as the Data Source

This solution will result in a maintainable and editable package that has no dependency on the development platform. It will work even if you do not have the ‘Microsoft.ACE.OLEDB.12.0′ provider installed as long as the provider is installed on any remote machine running SQL server 64-bit provided you have access to it.

As this solution uses OPENROWSET to provide data from Excel we have to setup SQL server options.

Fire up SSMS and execute the following script on the instance of sql server 64-bit that you will be using. I was using my local instance of SQL Server 2008 Developer Edition 64-bit. If you are using anything other than test/dev server consult your system admin first.

-- Code start
USE [master]
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' ,  N'AllowInProcess' ,  1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' ,  N'DynamicParameters' ,  1
GO
-- code end

Now test the configuration with a query similar to this (adjust the excel path for your environment):

-- test query 1: change the path to your excel file
select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0;Database=C:\twitter\sqlcheesecake.xlsx'
, [Sheet1$]
)

 

-- test query 2: change the path to your excel file
select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0;Database=C:\twitter\sqlcheesecake.xlsx'
, 'select top 2 * from [Sheet1$]'
)

If the tests pass and results are returned we can move on.

Fire up BIDS or Visual Studio and create a new SSIS Project. Drag a data flow task in the control flow and double click on it to open the Data Flow tab.

Now drag an OLE DB Source component from the Data Flow Sources and double click to configure it. Create the connection manager to the SQL server that you will be using. Set the database to the database that you will be importing the excel data (this is not required but I have used the same connection for the OLE DB destination to keep it simple and stupid).

From the Data access mode dropdown select SQL command (SQL command from variable is also an option but it is not important for the concept).

Type in (paste) your test query in the SQL command text box.

Now click on the Preview button to test your query. It should display results if everything is setup ok.

Now drag an OLE DB destination to the data flow, connect the source component to the destination and configure your destination connection  and  table. I’m reusing the same connection manager.

Configure column mappings and you are ready to go.

Right click somewhere in the data flow and select Execute Task to run the data flow task.

If everything is ok  you should see the beautiful green boxes all around :)

Well, this turned longer to write than I expected but I know I have saved a lot of time to a lot of peeps in the Community.

Cheers,

Hrvoje Piasevoli

@HrvojePiasevoli