Grouping problem

I am working on a report for a small POS. The report should allow the user to choose the time interval to group sales records, e.g. 1 hour, 2 hours or 4 hours. I believe I can setup this by the DiscretizationMethod and DiscretizationBucketCount of the Hour attribute in my DimTime dimension. However, the problem that I am facing is this POS will support multiple branches. Each branch will have their particular opening and closing hour. So, how can I group all the transaction into groups, said "Before Shop Open" and "After Shop Closed"? This sounds strange but will happen quite often as overtime work is always expected in my living place.

If this is infeasible, is there any workaround? I think the business user certainly want to know how many transaction has been created in those extra time.

In the other report, it is required to generate a transaction count by amount. The user should be able to specify the amount interval and upper limit. e.g. if amount interval and upper limit are set to 50 and 150, then the transaction will be grouped into 4.
0<=amount<50
50<=amount<100
100<=amount<150
amount>=150

I have no idea to this. First, I don't know how can I get the amount for each sale order as my fact table is storing sales order item information only. Second, how can I make this customizable grouping just like the report stated above? Thanks!

Hi Alex:

You pose two difficult problems. I'll address the second problem because you provided the most detail and clearly stated the issues. To restate, the issues are:

(1) How can you get the amount for each sale order?

(2) How can you allow customizable grouping?

Addressing issue (1) about the amount for the sales order. If the sales amount for the sales order is not in your fact table then you will not be able to access the sales amount in your cube. You have to go back to the ETL process and bring in the sales amount as part of yur fact table.

Issue (2), customizable grouping, is best approached on the client side of your application. Alternatively you, as an administrator, could create a separate attribute hierarchy for each branch with it's own amount interval and upper limit. I think your choice of a solution (client side, or separate hierarchy per branch) depends upon how many branches you have, and how much management you want to put in as an administrator. Creating transaction count by amount on the client is simple if you have the transaction amount as a measure. Get the transaction count by using a calculated member with the MDX count() function. Within each query you can adjust the amount interval and upper limit for each user. Here's an example:

WITH MEMBER MEASURES.[Less than 50] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] < 50)'

MEMBER MEASURES.[Between 50 and 100] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] > 50 AND Measures.[Sales Amount] < 100)'

SELECT {MEASURES.[Less than 50] , MEASURES.[Between 50 and 100]} ON COLUMNS FROM [my cube]

Hope this helps.

PGoldy

|||Hi PGoldy,

First, thank you for your input to these difficult problems that I am facing right now. Actually, I have come up with sort of solution after the post but it still doesn't work very well.

For issue 1, I found out that even I don't have a total for the sales order stored in the fact table. I can get it by creating a "Named Query". In this query, I will group the fact table records by the transaction ID. In this way, I obtain the sales amount per transaction, not per item. It looks good.

For issue 2, I use the "Named Query" that just created a bit further. In that query, besides the total amount per transaction. I create another field which is a floored amount. I am using this function.

floor(convert(decimal, sum(ItemAmount)) / 50) * 50

By doing this, I am able to make those sales total into the starting value of their groups. e.g. 38 returns 0, 59 returns 50 and 160 returns 150.
It seems really good at first. However, I have another problem to make this perfect or really usable. In SSAS, if there's no data exists for a specific group. It won't get display. e.g. if I got 38, 59 and 160 in my sales order total. I will only get the groups 0~49, 50~99 and 150~149. The problem is the missing 100~149. For business user, I think it's not acceptable to have a gap in the report like this. So, how can I fill in this gap?

Moreover, is there any best practice for my situation? I think this is a very common scenario but I can't find any useful reference.

Regards,
Alex|||

Hi Alex:

Best practice is creation of a hierarchy which has the "bucket" ranges you want. Then link each fact table record to the appropriate bucket with a foreign key. It's a common practice and used in most implementations. Below is a link to a series of articles by Bill Pearson which articulate (very well) the functionality you're looking for and a lot more. Good luck.

PGoldy

|||Dear PGoldy,

Could you please check whether the links has been posted? Thanks!

Regards,
Alex|||

Hi Alex. Sorry about the delay. Below is the link. PaulG

http://www.databasejournal.com/article.php/1459531

sql