Archive for June, 2010

Most measures in the cube are of integer or real values. This is easy. All you need most of the time is to use the SUM aggregation. What if you want to have a measure of datetime with last non empty value aggregation? Since this is a standard edition, the  last non empty value  is not available hence the only aggregation that can be used is SUM or COUNT.
To add a measure of datetime type, first convert the datetime into a text in the source view.
  ISNULL(CONVERT(NVARCHAR(30), aerc.StartTime, 13), ‘N/A’) AS start_time,
  ISNULL(CONVERT(NVARCHAR(30), aerc.EndTime, 13), ‘N/A’) AS end_time
Then create a dimension that has a list of the starttime and endtime. Let say we call it dm_startendtime. Populate this dimension as Start Time and End Time in the cube.
Then populate this to the cube. Add this measure with SUM as the aggregation. Let say we call them as ValStartTimeKey and ValEndTimeKey. The ValStartTimeKey is the unique key of the dm_startendtime for the start_time. This key is of Integer data type.
After that, we need to swap the value i.e. to get the start_time from the dm_startendtime. To do this, create a new calculated member called [Last Start Time] with the following expression:
IIF([Measures].[ValStartTimeKey] = 0, NULL,
FILTER([Start Time].MEMBERS, VAL([Start Time].[Startendtime].CurrentMember.Properties("Key")) = [Measures].[ValStartTimeKey]).Item(0).Item(0).Name)
Done. The above is just to implement how to add a measure of datetime type. I have yet to find a solution to implement Last Non Empty value on Standard Edition.
To use the start time measure:
(   [Activity].[Activities].[Activity].&[O1120],
    [Reason].[Reason Categories].[Reason Category].&[MAINTUNPLND],
    [Measures].[Last Start time])

Read Full Post »

File system error occurred while opening the file ‘\\?\C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\….
If you can connect and browse your cube, but unable to run a report against the cube and encountered the above error, try repointing the Datadir of your Analysis Server to another folder such as D:\MSAS10\Data. After that, restart the SSAS service, then add the users permission to access the cube. Your report should run OK now.

Read Full Post »

%d bloggers like this: