.NET with BI development tips, tricks and experiences.

A colleague of mine found a handy way to run a single unit test in Visual Studio 2010. Go to Test -> Windows -> Test View, select the test you want to run and right-click. Hit Run Test and away you go.

Always remember to run all your tests though before you commit your changes.

Been busy creating a Silverlight/SSAS mashup recently to show off their potential when used together to both colleagues and clients. While attempting to build the measure selection/filtering interface I thought it would be pleasant if they were grouped by their… measure groups, hierarchically like they are when browsing a cube in SSMS or Visual Studio. Measures are easy enough to find as a collection on the CubeDef object as a MeasureCollection. But where are measure groups hiding?

Naturally I would have thought there would be a MeasureGroup or MeasureGroupCollection off the CubeDef – no such luck. Debugging and taking a closer look at the Measure object, in the PropertyCollection (the Properties member) I spyed the property MEASUREGROUP_NAME. The sweet smell of victory. So to get your measure’s measure group here is the C#:

Property measureGroupProperty = measure.Properties["MEASUREGROUP_NAME"];
string measureGroupName = measureGroupProperty.Value.ToString();


I have been repeatedly asked to produce queries in MDX across a number of SSAS cubes that use the query execution date/time and I have reused this little gem repeatedly:

StrToMember("[Date].[Date].[" + Format(now(), "yyyyMMdd" + "]")

Here it is in a complete query that returns a measure over the last 4 days from the time of execution:

SELECT
    [Measures].[Menace]
ON COLUMNS,
    LastPeriods(4,
        StrToMember("[Date].[Date].[" + Format(now(), "dd-MM-yyyy") + "]")
    )
ON ROWS
FROM [CubeOfDoom]

MDX has no GETDATE() function to obtain the member associated with the current date/time as it is up to each implementation how the date/time dimension is populated and how its members are named. If you populate your date/time dimension up to the time period of execution you can make use of DefaultMember or CurrentMember but what if the way the dimension is populated has to change in the future or the dimension is populated ahead of time?

This method may not be the most efficient but it is the most robust I have come across to date. If anyone has a better way drop me a comment. Always looking to improve/evolve.

In this post I outline how I got a custom font to work in Silverlight after having some issues with compatibility.

While the winter here in the UK seems to have no end in sight this year I have been trying some new things out with Silverlight 3. I got quite bored of using the standard fonts and decided to start looking around for some slick fonts to use in my projects. Using the awesome Identifont I came across AUdimat by Jack Usine.

To get your font into your Silverlight project all you have to do is add the file and make sure the project has marked it as a resource. Then, to apply the font in your page just set the FontFamily attribute like so: FontFamily="filename#fontname". In my case this was: FontFamily="AUdimat-Regular.otf#AUdimat". Too easy? You bet ya.

Ran my project and got a load of garbage output from my test TextBlock “This is some text”. Doing some digging I started to discover the world of fonts and Silverlight is not one of rainbows and fluffy bunnies. This article from Silverlight Spy is the most comprehensive at outlining font support with Silverlight and provides a brief history of fonts.

The outline format of the AUdimat font was causing the problem so I had two options; find another font, convert it to a compatible format. Given the fact I had spent a while searching for the exact font I wanted I went with the second option. To accomplish this I was lucky to find the FREE Online Font Converter. Now I had my font in TrueType format I added it back to my project, changed my FontFamily attributes to FontFamily="AUdimat-Regular.ttf#AUdimat" et voila. A beautiful rare font in my Silverlight app. A big thank you to those sites I made use of.

Indexes on your tables are not always a good thing. For example, a clustered index in the wrong place can dramatically slow down your update, insert and delete speeds. Indexes that are not being used sap valuable resources away from your database engine to maintain them. There are many instances where indexes will not enhance performance but for now I just want to deal with redundant ones.

I cannot take credit for this script and I cannot remember where I found it (please leave a comment if you know the original source and I will add the credit in). It finds all indexes (in the context of the database you are connected to) and orders them according to the total of index seeks, scans and lookups against them. Any of those with zero to very little should be dropped if resources are skant. It is the best one I have found to date. Again if you have found a better one please leave a comment.

Enjoy…


SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS TableName,
i.name AS IdxName,
i.type_desc AS IdxType,
ius.user_seeks,
(ius.user_seeks*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Seek],
ius.user_scans,
(ius.user_scans*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Scan],
ius.user_lookups,
(ius.user_lookups*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Lookup],
ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
AND database_id = DB_ID()--Current DB
INNER JOIN sys.tables t
ON t.object_id = i.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.type = 'U'
AND t.is_ms_shipped = 0
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups ASC


I was recently playing about with hiding the All member for several dimension attributes in my SSAS project. In order to hide the All member we set the IsAggregatable property to False. This immediately had the effect of my queries not returning any results. In hindsight my problem should have been blindingly obvious.

When we no longer aggregate the members on a dimension attribute, SSAS does not know what member/members to use unless you tell it explcitly, or set the DefaultMember attribute. Doing one or the other will stop your measures and calculations from vanishing.

After installing the Office 2010 Beta I continued to develop my SSIS and SSAS projects without interruption. In my spare time (a rare thing) I wanted to keep my web app skills up to date and continue to explore Silverlight and its all-singing, all dancing .NET RIA Web Services. All was well at first. I created a new web application project and started to create my master page and default web form when without cause I was not able to click on the Visual Studio 2008 window. The CPU was idle and I had no other apps open at the time other than Spotify. Tried the ALT+TAB to see if some modal dialog had gone astray but nothing…I was completely locked out.

I scratched my head for a while and tried restarting a few times. The only way I could kill VS2008 was using the Task Manager. The same occured each and every time with a web project. BI projects remained functional. I started to trawl the internet and did not find much but then I stumbled across Martin Hinshelwood’s blog and this article. He hit the nail right on the head.

If you have VS2008 locking you out on web projects after installing Office 2010 your solution awaits you here. Thanks Martin.

When you first install PowerPivot for Sharepoint 2010 and start deploying your workbooks it does not take long to notice that the Sharepoint library preview displays ALL the sheets in your workbooks. This looks pretty awful in what should be a slick gallery, plus the thumbnails are too small to examine data in any meaningful way. As my experience with Excel was pretty limited I naturally thought you would hide the sheets in Sharepoint – WRONG.

Open up your workbook in Excel and right click on the sheet tab and it will have a “Hide” option. Hide all the sheets you don’t want to see and save it back to Sharepoint et voila! No more pesky ugly data sheets. Only nice clean, sexy charts that will make your clients drool.

Immediately the next natural question is how do I get them back in case I have an itchy mouse finger? Right click any remaining sheet and select “Unhide”. You are presented with a list of hidden sheets to re-display.

Pretty simple stuff when you are looking in the right place.

Once again all praise goes to PowerPivot-info.com. This time it is down to their comprehensive installation guide of PowerPivot with SharePoint 2010. I followed their guide meticulously and had no issues whatsoever on my virtual Windows Server 2008 R2 instance. The only problem I had was with the PowerPivot gallery previews being generated once I was installed and deploying PowerPivot workbooks. This was a known issue and covered in their installation guide. I tip my hat gentlemen. Great work guys, you have my gratitude and thanks.

The guy that thinks up my posting titles has caught a flesh eating virus. He could lose some weight though as he was always a bit porky.

Recently I encounted the most infuriating problem with SSIS, SQL Server Agent or Windows Server 2008 to date. What it is, I dont particuarly care I just pray this issue is fixed soon.

I have a set of user defined variables sitting in an SQL Server 2008 database. These are used to generate connection strings, rainbows, pixies and other mythical creatures through expressions in an SSIS project. This works a treat when running the package in the SSIS Package Execution Utility or Business Intelligence Studio (BIS). It saves me having to change variables in each package every time I deploy to different servers. If you try running a package with file connections populated by dynamic variables through SQL Server Agent, it cannot find the specified package (the precise error I will edit in later when I can be bothered to reproduce it again – it comes with the usual completely useless hexadecimal HRESULT nonsense so you won’t be disappointed). What is particuarly aggravating is that if you boot up the very handy Process Monitor you can see as clear as day the SSIS process (dtexec.exe) is a lying git. It only throws the error 50% of the time and yet the SSIS process finds the package quite happily 100%, every time.

I really did not want to define the file connection paths explicitly but I had no alternative. I got the same bull when I tried to run it through the Windows Scheduler to no avail. I tried everything. My futile attempts are way too numerous to list here. There is NO other way around this problem.

I cannot state whether or not this occurs in W2K3 as all our servers have been downgraded to W2K8. Enough cynicism from me for today. Here is something to warm you up: 27bslash6. Enjoy and till next time.