March 3, 2009 at 8:47 am
I have a couple of calculated members rank and rankgroup
rank is customer rank by sales by supplier
and rankgroup is where I have split these into groups top 50, second 50 etc
I want to do this:
supplier
--+drilldown to groups of 50
----+drilldown further to the customers in the groups of 50 based on their rank.
I tried adding the rankgroup to the customer dimension as an attribute but did not work due to hierarchies already present.
What I am trying to acheive must be possible by some method.
Some direction would be greatly appreciated.
March 5, 2009 at 4:03 am
If you moved the rankgroup into the table that supplies the dimension then it would be a lot easier to do.
Sorry this does not answer the question but it is a solution.
Ells.
😎
March 6, 2009 at 12:40 am
Thanks Ells. Any guidance is very welcome.
How do you move a calculated member to a table in the datasourceview?
Using the calculations tab I have tried adding it (calculated member: rankGroup) to a level in the suppliers dimension and then I tried adding to a level in the customers dimension but it then wouldn't allow me to add it to the row or column area in the browser tab.
I can't work out how to add it to the table though.
March 7, 2009 at 2:44 am
where possible in the past I have always stored the calculations inthe the database rather than cube. It is a lot faster then in the cube. In SQL there are rank and dense rank functions. So I would add two columns to the database table and then run an update statement that basically does the same in SQL as the code in the mdx then you end up retrieveing a row that has the suppplier and their ranking in one row. There are references in BOL to partition by and dense_rank and rank but I am sure you know that already.
Hope that helps.
Ells.
March 9, 2009 at 1:42 am
Thanks Ells. I have done this and it works a treat:
I created a view bringing in supplier and customer and ranked the customer partitioned by supplier and created groups of 50 with a case statement and brought the view into the cube.
May main problem was that I was trying to do as much as possible in the cube.
Really appreciate your help.
Dave
March 9, 2009 at 8:42 am
Ells already answered your problem, but I wanted to 2nd the idea, with one minor caveat. We do the same thing: at the end of the ETL process we rank all the customers based on a variety of things using T-SQL, then use that to populate attributes in the customer. I did the groupings into buckets in a calculated member in the DSV, because it was easy. Now the catch: this is one of the slowest parts of our ETL process because it has to do so much /many calculations against so many customers, across so many transactions. It would be nice to get this done against the cube, but it was so much easier to get the logic right in T-SQL as we're much more comfortable with it than MDX.
Just my $.02,
Rick Todd
March 9, 2009 at 9:34 am
I am going to have to look more into the buckets stuff. I did look at Discretizationmethod in the cube dimensions but couldn't get it to do what I want.
Does BIDS allow applying groups via buckets (groups of 50)? in the DSV or is it something that can be done in SQL management studio.
Thanks
March 9, 2009 at 9:44 am
I couldn't figure out how to get it to do it in a controlled fashion. I really wanted to like the way BIDS did it for me, because it was so easy, and I tried to rationalize it that it would tell me something interesting about the data, but it didn't. Here's how we did it:
2 Named calculations in the DSV: one for the key, one for the bucket description.
Length Of Stay Key:
CASE
WHEN LengthOfStay BETWEEN -9999999 AND 0
THEN 1
WHEN LengthOfStay = 1
THEN 2
WHEN LengthOfStay = 2
THEN 3
WHEN LengthOfStay = 3
THEN 4
WHEN LengthOfStay BETWEEN 4 AND 7
THEN 5
WHEN LengthOfStay BETWEEN 7 AND 14
THEN 6
WHEN LengthOfStay >= 14
THEN 7
END
Length Of Stay:
CASE
WHEN LengthOfStay BETWEEN -9999999 AND 0
THEN '0'
WHEN LengthOfStay = 1
THEN '1'
WHEN LengthOfStay = 2
THEN '2'
WHEN LengthOfStay = 3
THEN '3'
WHEN LengthOfStay BETWEEN 4 AND 7
THEN '4 - 7'
WHEN LengthOfStay BETWEEN 7 AND 14
THEN '8 - 14'
WHEN LengthOfStay >= 14
THEN '15+'
END
Then you just build a new attribute in the Dimension with the key as bucketkey, and the name as bucket name, in this case LengthOfStayKey and LegnthOfStay respectively.
Could you do this in the SQL itself, absolutely. You could just take that same code and move it into your ETL instead of in the SSAS project.
If you figure out a way to make the discretization useful, reply here so I can see it,
Rick Todd
March 9, 2009 at 9:50 am
I would like to add to one of Rick's comments. I find it easier to add this sort of logic with T-SQL. I also believe that if I wa;lked out the door then the porganisation woulkd be able to maintain the T-SQL easier than in MDX. Use a lot of T-SQL and stoired procs to apply business logic as it is easier to read and maintain.
Just my hunble opinion
Mark.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply