This content has been marked as final. Show 54 replies
Hello, can you also please explain to me how I can do this:
We are using Oracle 10g Enterprise Edition Release 10.2.0.4.0 - 64bit with OLAP
For the commercial reporting and we have 2 non-additive measures for the product and store dimensions: number of debits, number of clients (f.e. you can go twice a month in a store ) and some volume 120.000.000/months for the detail level (day/store/client/debit/product). We are using a “count distinct” but the user can’t drill and must wait a long time for each request.
We have the following dimensions:
Store : All -> Business Unit (4) -> Area (50) -> Store (600) (-> Desk -> Ticket header in order to calculate de debit)
Client : All -> segment (10) -> Account (2.000.000)
Product : All –> Universe (6) -> “métier” (40) -> Category (350) -> … -> Product (500.000)
Time : Year (3) -> Semester (6) -> Quarter -> (12) -> Month (36) -> … -> Day (1.100)
We want to build a cube will with <Store, Segment, Category, Month> as basis level. We have already a prototype (we use AWM) with the additive measure like Turnover (Historical, Budget, Actual>, Quantity Sold (H,B,A) but we don’t known how we can manage the Number of Debits (count distinct of ticket header) and the number of account (count distinct of account). The count distinct must be evaluated for each level in a dimension. The debits are additive belong the Store dimension.
I have seen that you have a document that explains how to manage COUNT DISTINCT measures within OLAP. Can you send me this information?
frank.vanimschoot "at" businessdecision.com
Hello Nirav / Frank / Krill / Erik / User10411795,
I am sorry for the long delay in replying to this thread.
Due to an extreme personal emergency, I have been out of action for a few weeks - and still trying to catch up on the missed items.
I have mailed you all the required documents at the e-mail ids that you mentioned in this thread.
Hope it helps.
In an attempt to protect Piyush's email Inbox from exploding with requests for this information we are hoping to post an article on the blog about how to manage count type measures. It should be available shortly. Thanks to Piyush for kindly sending out the documents which I thought I had lost - amazingly I have now found them again on an old zip drive.
I am again...
I have two dimensions and one fact table and I want to build a cube with a measure as
"COUNT" instead of "SUM".
Oracle Support Service told me:
- Create a formula that returns a 1 if a value is stored, otherwise returns zero.
- Then assign the values from this formula to a stored variable that can then be aggregated to reflect
the cells loaded.
But how can I do that with the AWM tool?
I need help!
Could someone also forward a copy of the documents over to me? I'm working on an HR based data mart (relational) that is suffering performance issues due to all the count(distincts) we have to do...finding a solution in OLAP would be wonderful.
The first part of my email address is "SPowell"
The tail part of my email address is "@columbus.rr.com"
Thanks very much in advance!