Multiple measures with condition
-
Hi all,
Suppose I have defined 2 measures in my cube, namely, PRICE and DISCOUNT and they are columns from the fact table ORDERS.
There is another dimension table called CUSTOMERS storing customer details, with a bit column called CREDIT: value 1 -> credited customer, and discount is granted; value 0 -> non-credited customer, discount not granted.
When I browse data in my cube, I wish to perform the aggregations on the measures in such a way that, when the customer is CREDITED (i.e. credited = 1 ) then I will use the value "PRICE - DISCOUNT"; if customer is NON-CREDITED (i.e., credited = 0 ) then I will just use the value "PRICE".
There is a join on the CUSTOMERS and ORDERS tables in the cube, where each ORDERS record will have a CUSTOMER_ID field linking to the CUSTOMERS table.
Can someone teach me how to achieve this? Is that I should first create a calculated member, say "NET PRICE", and then put the CRETIT condition in the source table filter of the cube? Or should I perform the condition decision inside the MDX query which queries the data?
It's just like:
SELECT
case CREDITED
when 1 then PRICE - DISCOUNT
else PRICE
end
But I dunno how to put this in MDX ...
Many thanks,
delpiero
-
In my experience for performance issues retrieving the cube, I would suggest doing when loading the cube (thru a view). I.e., processing the cube would be marginally slower but retrieves could be much faster. E.g., if you Excel Pivot Tables for retrieving most of the work will be done in Excel (even you force the execution location on the server) unless you use the "Non Empty Behavior" (which you cannot since the two measures are exclusive - only one is valid).
Hope it helps.
-
I agree with Tiago, definitely put the Net Price into the view. This also means that you can have discounts that change (or are aligned with) time (if you're not doing this already).
Steve.
-
Thanks a lot.
delpiero
-
Hello again,
I have adopted these steps to retrieve data from my cube:
(1) Load data from the fact table into a view which filters out unnecessary records.
(2) Process the cube and its dimensions dynamically with the view as source table, through DTSRUN.
(3) Retrieve data from the processed cube.
All of the above steps are put into a stored procedure in SQL Server. Everything works fine when only 1 user is running the stored procedure. But when multiple users happen to run it at the same time, problem occurs: locking, inconsistency, failure to refresh cube. Usually only 1 user gets the results and all others fail.
How can we isolate each other? I have thought about locking, begin tran/commit tran, temporary table, but it seems not practical. What is the usual practice? Can we lock a cube explicitly?
Lots of thanks,
delpiero
-
Why are you having the cube be rebuilt per user? This would normally suggest that there is a common identifier (e.g. salesperson number/code etc) that you could use as a dimension. You could then load up all information, and use security to limit the viewing/access for each user/group so that they see only their appropriate data.
Steve.
-
Steve,
The main reason to rebuild the cube is to refresh the data from the source table. There is no user-based query in my application ... Treat all users viewing the same report, and when they view the report the stored procedure is run, which rebuilds the cube and its dimensions.
Another reason is that different users may choose different filter criteria when viewing the reports ... e.g. one dimension lists the products but products are divided into different types. You may say I could use MDX to filter but some range filter (e.g. date ranges, especially very wide date ranges) perform slowly when filtered by MDX.
Anyway, the first reason is to refresh/update the data. But now when 2 users view the report at about the same time, one of them would get an error saying failure to refresh cube because a shared dimension is locked by another user.
Please help. Many thx!
Del Piero
-
Hey Del,
I understand re: refreshing the data but still don't see why you couldn't/can't use MDX and dimensions to filter the data appropriately (have you looked at multiple hierarchies in your dims, they let you view the same information via different drill paths).
If you *really* want to keep going this way, one method you could use is to create a basic table to capture the current state of the cube and data. Add an insert to your stored procedure (the one that rebuilds everything) to insert say the user id (Dels_Domain\Del_Piero), the action (data refresh started) and the datetime (GETDATE()). As you progress through the stages you could update this further (ie update status to 'building cube' etc). Then within the same stored proc, you need to do a select on the status table, determine if anyone is currently processing/building etc and if they are, exit without going any further (preferably return an error code). Assuming you've got some 'wrapper' type application (asp.net web app?) you could display the last build datetime and user to the users prior to letting them try to rebuild it for themselves.
I still think the easier approach is to work on getting the MDX to perform and maybe using multi-heirarchies to allow people to see the product dimension differently.
Good luck!
Steve.
-
Hey Steve,
Finally I have come with your "basic table" solution ... and it works .. I think this looks like a typical method of isolating transactions ...
Thanks a lot!
delpiero
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply