May 24, 2005 at 7:43 am
Okay, so I have been working in Analysis Services for a couple of months now, and am starting to feel a quite at home. Except when I meet situations like this, when even doing an internet search just gives me a hadache and no answers.
I have two datasets, one for General Ledger (GL) Data. Another is for Payroll data. GL data has two keys, Profit Center and Fiscal Calendar Period. Payroll has the same two keys plus an additional key, Rate type (Exempt, Non-Exempt, Timesheet).
In a cube I need a calculated member, or something akin that shows as a data field for reporting purposes. (I am using both Reporting Services and BI Portal) This is for determining the payroll cost per meal. Payroll cost is in the payroll dataset, Meal Counts in the GL dataset.
Joing the data gives me up to triple the amount of meals as for each Profit Center for each period you may have up to the three rate type entries in the Payroll cube. No one of the Rate Types always exists (there is at least one Rercord missing each rate type).
So, I created an aggregate cube where the Meal Count field for each Profit Center Period is divided by the count of rate types for that Profit Center Period. Now all works well - or so I thought until I filtered the report based on Rate Type.
The problem has just moved down the tree to the rate type level. If I look at a particular rate type for a profit center period, it is showing the result of the dividing I did above. Not so - says the client. This number should not change from the Profit Center Period All Rate type number. In other words - it needs to show the numbers as it did when I used a join, but the totals above that need to show as they are now that I have did the division by count.
Other than feeling like I have been knocking my head into a wall on this, I have made no progress in getting this number to show correctly in the cube in all cases. Sounds like it needs to be a calculated member, or possibly even a set, but I need the darn thing to change based upon whether the Current Member of the Rate type is All Rate type, or a particular rate type.
HELP!!
Here is a sample of how they want the data to show, given that the Cost per Meal is Payroll / Meal Count and that when you add the Payroll from the three record below The rate Type All record, it totals what is in the All Record.
Profit Center Period Rate Type Meal Count Payroll Cost Per Meal
1aa1 1 All 10000 5000 0.50
1aa1 1 Exempt 10000 2000 0.20
1aa1 1 NonExempt 10000 1200 0.12
1aa1 1 TimeCard 10000 1800 0.18
Thanks in advance for any help.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
May 24, 2005 at 9:11 am
Ok, maybe I'm simplifying it too much, but assuming you have a GL cube and a payroll cube, couldn't you either use your existing Payroll cube, or create another just for this purpose, and create a measure that uses the LOOKUPCUBE function to get the meal count? From the exmple you've shown, the meal count is a constant releated only to Profit Centre and Period. You'd have to pass the appropriate profit centre and date in the function call, but seeing as you've said that both datasets have the PC and date in them, this shouldn't be a show stopper - as long as you've used the same dim structure for htese dims in both - even better if you've used shared dims as this will guarantee it.
An example of how to do the call (with no knowledge of your dim structure) would be (assuming we'r ein the Payroll cube)
LookupCube("MySuperDuperGLCube", MemberToStr([MyPeriodDim].CurrentMember) + "," + MemberToStr([MyPCDim].CurrentMember))
Cheers,
Steve.
May 24, 2005 at 10:15 am
Steve,
Now talk about a cool function with lots of power. You just gotta ignore the Analysis Services help on that function, and do some exploring of your own. In the string (The second Parameter), the first value is the name of the measure I want to return, then I repitively did the MemberToStr function on all dimensions. Even though there are about 25 dimensions it only took a few minutes with judicious use of Copying and pasting.
Some things to be aware of. This function will be very unhappy if your Dimensions are not named the same between tables (another reason for standards and shared dimensions). Yes - I found a holdover from the previous developer that was not shared and did not match, so I had to fix it. And you need to wrap the whole string (2nd parameter) in "(" ")" when there is more than one field.
My only wish is that you wrote the Help File for Analysis Services, because once again the help file is lacking. Imagine that.
Once again Steve, I thank you.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply