September 21, 2007 at 11:29 am
I am extremely new to MDX and I am having real troubles getting started. It is frustrating because there are so many things which I know the syntax for in TSQL, but can not figure out in MDX.
I have created a cube and want to build a KPI which basically shows sales results for this week. In TSQL I'd add a where clause with something like:
where datepart(ww, sales_date) = datepart(ww, getdate()) and datepart(yy, sales_date) = datepart(yy, getdate())
That would limit my results to just this calander week. I can't figure it out in MDX. The cube does have a time dimension, cleverly called [Dim Time].
It doesn't help that I don't have a good MDX reference book either, so a recomendation wouldn't hurt.
cheers
September 24, 2007 at 1:25 am
Sean,
If you are wanting the entire cube to just have 1 weeks worth of data I would suggest doing the folowing. I would recomend that the fact table you are using be a database view and you use the SQL code you usually do to limit the data there. This will have several benefits in that you are more experienced in SQL than MDX, the view can be changed much easier than a table and the amount of data you are loading into teh AS cube will be reduced which will improve performance.
As for MDX books my first port of call would be Amazon and read the reviews. I'm sure t6hat there are people out there that could stear you on a better path for books though.
September 24, 2007 at 4:54 am
No, I do need all the data in the cube (not just this week), but I wanted the KPI to filter down to the current week. It may well be that there are other ways of doing it from withing the KPI or reporting services. The cube is actually being used for other things as well, I was just hoping to get this out of it too.
Currently I am using TSQL and loading the results into an excel table. Then I am using Trusted Excel, and SharePoint KPIs to display it correctly. But changing this is a lot of work because it involves trusted excel, SSIS, SharePoint, etc.
cheers
September 25, 2007 at 3:32 am
Good book to get for understanding MDX is
"Fast Track to MDX" ISBN 1852336811.
Also checkout the on-line series of articles by William E Pearson starting with the 1st one at
http://www.databasejournal.com/features/mssql/article.php/1429671
Although they are Analysis Services 2000 based there is a lot of useful information.
September 25, 2007 at 5:46 am
I thought it might be helpful to see what I am trying to do....
select [Measures].[Fact Activity Contact Count] on 0,
[Fact Activity].[Sales Person].&[Bentley, Billie] on 1
from [CRM Support]
where ([Dim Time].[Week].&[38] ,
[Dim Time].[Year].&[2007])
Basically in this I want to replace the 38 with the current week number, and the 2007 with the current year number.
September 25, 2007 at 6:54 pm
Basically, there's a couple of things you should do:
- ensure that only dimension records with valid data is flowing into your cube. Some people mistakenly load all (future) periods, which makes thinks a little more complicated.
- There's MDX navigation references which'll help. For example, you could use [Dim Time].[Year].[All].LastChild to get the current year. However, that's unlikely to help you with your week reference.
- Carefully review your time dimension. There's lot's of different ways to represent / reference time. (ie W200738). I'll assume you don't want to invest time in redesigning this data, so create a hierarchy, Years, Weeks (it won't be an optimized hierarchy). Then you'll be able to use a reference like:
[Dim Time].[by Weeks].[All].LastChild.LastChild
This'll reference both the year and the week.
September 26, 2007 at 4:08 am
We had a similar requirement.
We created a named set within the cube that sat within the Time dimension that searched the cube data for the last recorded week. We called this set 'Current Week'. You can then utilise this set in your selections.
Named Set 'Current Week'
Tail(Filter(Time.[Week].Members, Not IsEmpty(Time.CurrentMember)),1)
Example of use
SELECT { [Current Week] } ON COLUMNS ,
etc
Hope this maybe of help.
October 10, 2007 at 9:18 pm
We used to use yet another method, ensuring we always used a specific date dimension key format (integer, YYYYMMDD format) we would use the Excel library to get the current date (keep in mind, this is the date according to the server, if you're user community crosses timezones this could prove problematic), once we had the current date we could then use string functions to build up a reference to a date dimension member. This would all reside in a calculated member hanging off the time dimension (therefore applicable to all measures). Although it has the downside of not being great for cross timezone boundaries, it does have the advantage of still working when you've got forward looking data that has to be loaded in to your cube (e.g budget/plan data) as these records will mean that your 'tail' function will return a time period in the future. I guess with the ability to SCOPE now in AS2005 MDX statements, you could avoid this by only scoping to get a non-empty time member for a measure that you know is not forward looking. Not also that if you have no sales (or in particular, no transactions) yet in a given time period (e.g. week) you're "current" week will still seem to be last week (as it was/is the max time period that's got data).
Steve.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply