Creating templates in 2005/2008

Some nice finds

Just wanted to post some nice little sitestools that I have found
 
Create a Time ONLY dimension for SSAS
 
SSAS Activity Viewer for SQL Server Analysis Service 2008 – Use this tool for viewing current activity and queries being run against your SSAS server
 
A different approach to Time dimensions – Great paper for learning how to create and use custom time members
 
MDX Script performance enhancer
 
JumpStart TV – nice collection of instructional videos covering SQL, BI and .NET
 
 
 
 
 
 

Default Measures

Today I started seeing some weird results when I was using NonEmpty function. If I just used NonEmpty without a filtering measure like
   Nonempty([Time].[Time].[Time])
 
then I wasn’t getting any rows returned. But if I put a filtering measure in there like
 
Nonempty([Time].[Time].[Time],[Measures].[MyMeasure]) then I was getting data back. It occured to me that I had not even thought about what my default measure was. So I ran the following query
 
Select measures.defaultmember on 0
from mycube
 
To my surprise it was a measure that would not really be applicable to most of my calculations. I was a little perplexed at why SSAS chose that measure as my default, so after some "Binging", I found this excerpt in one of Mosha’s old SSAS site pages
 
"For the default measure, the first non-hidden measure is chosen. If all measures are hidden, then the first measure is chosen. “First” here refers to the first measure in the measures collection in AMO and DDL (but not in MDSCHEMA_MEASURES, which is always alphabetically sorted). "
 
So I promptly changed the default measure to something that would have more meaning to the calculations I was writing.
 
To change the default measure, just simply put the following code in the bottom of your MDX script
 
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER= <whatever measure you want to use>
 

Handling time from SSAS to Reporting Services – Reporting Services

As part two from my previous post Handling time from SSAS to Reporting Services I had my data ready to go in SSAS and was ready to build my report. Once I got the layout and data sets built I added my time field to the report. I then set it’s format as TIME, saved it and ran the report. Instead of the 4:31am I was expecting for the first row, I got 0.188194444446708. I then remembered that SSRS had been ignoring my SSAS formatting. So after some digging on the web, looking through SSRS functions and posting in the forum, I found the solution. Actually I should say it was presented to me by Isham H in the Reporting Services forum. He suggested I use the function Date.FromOADate. Sure enough I threw it in the expression and VIOLA! I got 4:31 am.
 
To read up on it click here

Handling time from SSAS to Reporting Services – SSAS

Recently I was tasked with building a cube and SSRS reports that displayed time based fact measures. Things like average delivery time, start time, etc for a delivery route. Since I had never dealt with time based measures before I did some scouring on the web and ran across this post http://sqlblogcasts.com/blogs/drjohn/archive/2007/12/03/ssas-working-with-date-and-time-measures-to-calculate-average-elapse-time.aspx . Thank goodness for this because I was having a heck of a time figuring out how I was going to get this data into SSAS and be able to display it since SSAS ignores anything date or time related for a fact measure.
 
So after following the instructions in the post, it worked brilliantly! I was able to browse the data at the date level and it displayed the time the driver loaded his truck and the time the driver made it to his first customer and so forth. I was ecstatic! However I ran into a small issue when I wanted to browse the data using a geography based dimension. This dimension did tie to the fact table using the a non key id, but I noticed that the time measure was doing some funky things when rolling up and down. It finally dawned on me that I had defined my time measure as an AverageofChildren. Since this aggregate function only works with date time, I was stuck. But only for a moment… I decided to load the same time fields again, except this time I would not specify a format of hh:mm. I would just let the field load as a float (decimal). Now I was able to aggregate the time up and then divide it by the row count to get an average start time up and down the geography hierarchy. Again I was ecstatic. That is until I tried to build a reporting service report against the data….