The host with the most

I have successfully moved my blog to http://devmau5.com/blog. I won’t be posting to wordpress.com anymore as I am now using the self-hosted wordpress.org solution. This gives me complete control so I can integrate it properly with lots of new exciting stuff.

See you over there.

Filed under: Uncategorized

To host or not to host?

I have now got to a point where I need much more control over my blog as I want to start posting detailed code. Time to get the self hosted version. I will be following the WordPress guide. Wish me luck and see you soon at devmau5.com/blog.

Filed under: Self hosted, Wordpress , hosted, migrate, Self hosted, Wordpress

The GETDATE() of MDX

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.

Filed under: Analysis Services (SSAS), Date/Time, Dimensions, MDX, SQL Server 2008, Tips and Tricks , current, date, Date/Time, dimension, execution time, GETDATE, MDX, now, time, today

Region lock begone

When at home I like to have some of my favourite shows in the background while I type away. Some of my favourites animated shows include Aqua Teen Hunger Force, Sealab 2021 and Robot Chicken off Cartoon Network’s Adult Swim. Living in the UK means my DVD players are typically locked on Region 2 with a max of 5 changes – thanks Sony. Almost all these shows are only available as Region 1 discs – you see my dilemna? I have unlocked the region protection on previous drives I have owned and have now hunted down a solution for my Dell desktop and laptop.

If you have a Lite-on, which is sometimes disguised as a PBDS drive, download the simple program located here and run the executable. The little app resets all counts at the click of a couple of buttons. Fantastic. Now you can have all the Stimutacs all you want. BE WARNED though you still have the OS counter and any software player counters to contend with as outlined here.

If you want to get your hands on some Aqua Teen or Sealab discs in the UK you can find them at www.playusa.com

Filed under: Random, Stuff , count reset, Dell, Dell DVD drive, Lite-on, PBDS, region free, region unlock, utility

Silverlight and fonts

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.

Filed under: Fonts, Silverlight, Tip and Tricks , AUdimat, compatibility, custom, embedding fonts, font conversion, font problem, fonts, garbage, identifont, opentype, Silverlight, tip, trick, truetype, workaround

Indexes are bad?

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


Filed under: DBA, Indexes, Optimization, SQL Server 2008 , index, irrelevant, lookup, Optimization, performance, redundant, scan, script, seek, statistics, unused, usage

IsAggregatable = False

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.

Filed under: Analysis Services, Analysis Services (SSAS), Dimensions, SQL Server 2008, Visual Studio 2008 , Analysis Services, attribute, cube, DefaultMember, dimension, dimension attribute, IsAggregatable, member, SSAS

VS2008 freezes after installing Office 2010

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.

Filed under: Office 2010, Visual Studio 2008, Web Authoring Component , crash, freeze, lock out, Office 2010, Visual Studio, VS2008, web application, Web Authoring Component, web project

Hide your sheets

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.

Filed under: Excel 2010, Office 2010, PowerPivot for Excel 2010, Sharepoint 2010 , carousel, Excel 2010, gallery, hide, PowerPivot, preview, Sharepoint 2010, Sharepoint 2010 library, sheets, theater, thumbnail

PowerPivot for SharePoint 2010 installation

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.

Filed under: Excel 2010, Office 2010, PowerPivot for Excel 2010, Sharepoint 2010, SQL Server 2008 R2, Windows Server 2008 R2 , Excel, PowerPivot, Sharepoint, Sharepoint 2010, SQL Server 2008 R2, Windows Server 2008 R2