December 14, 2016 at 8:38 am
Hello
I somewhat new to ssas and my company completely knew to cubes. Ive built a FactItemOrder table that displays everything at the lowest grain possible i.e. everything on users order. now the business will want to do analysis at this level but they will also want to do analysis at order level which im assuming should have its own fact table and ssis package to populate this. Getting feedback from the business and people in particular invested into this project is difficult rather than building the fact order and package just now when it could turn out they don't need this i was thinking of building a FactOrder named query with the fact item order as its source and rolling up the items ordered to order level. Im sure this is not best practices but i was thinking this would be a quick solution to see if this is worth investing the time to build it properly?
Interested in your thoughts?
December 14, 2016 at 9:20 am
ps_vbdev (12/14/2016)
...Ive built a FactItemOrder table that displays everything at the lowest grain possible i.e. everything on users order. now the business will want to do analysis at this level but they will also want to do analysis at order level which im assuming should have its own fact table...
You could build a summary table from this fact table, that may be easier than doing all the steps again to create the same data in a different grain. I'm not sure how many line items are on a typical order, but I'd guess that the grain difference may not justify building a separate summary table for this fact though. Using SSAS, you can define the hierarchies of your data for your cubes, such as line item/order, and SSAS will be able to optimize the data based on this. It would also allow a user who is looking at the order level data to drill down into the line item level if they want to.
Here's an interesting article that talks about setting up hierarchies:
December 15, 2016 at 8:17 am
That's a good read thanks and I'm currently dissecting it. It looks to be what I'm after but I'm not sure how to apply it to my model. Our order process is for wedding party suite hire so a groom will create a party order (QA100A) and then add party members orders like best man (QA100A01), usher1 (QA100A02) usher2 (QA100A03) and then each party member order will have the line items like shirt, jacket, vest etc.
so the fact table i have built is at line item level "Fact Order Line" but we will want to analyse at Party Member order level and at Party Order level. Where im confused is the hierarchies are setup on dimension tables so would i need a dimension table that stores the party order ref, party member ref and item ref/ in order to set up the hierarchies?
thanks
December 15, 2016 at 8:33 am
ps_vbdev (12/15/2016)
That's a good read thanks and I'm currently dissecting it. It looks to be what I'm after but I'm not sure how to apply it to my model. Our order process is for wedding party suite hire so a groom will create a party order (QA100A) and then add party members orders like best man (QA100A01), usher1 (QA100A02) usher2 (QA100A03) and then each party member order will have the line items like shirt, jacket, vest etc.so the fact table i have built is at line item level "Fact Order Line" but we will want to analyse at Party Member order level and at Party Order level. Where im confused is the hierarchies are setup on dimension tables so would i need a dimension table that stores the party order ref, party member ref and item ref/ in order to set up the hierarchies?
thanks
Can you share what the definition of your fact table currently is, and dimensions related to it? From your description in first paragraph here, I'm thinking you'll have an Item dimension which would have your hierarchy in it.
December 15, 2016 at 9:07 am
Thanks
If I'm understanding you correctly my fact table has Item Count (always 1), Item Price & Item Tax in it and the associate keys to dimension DimCustomer, DimDate, DimOrderStatus, DimOrderType and DimGarment which. I have two degenerative dimension in it also for PartyOrderRef and PartyMemberOrderRef so i can do a distinct counts at each level. As i said the fact table stores every item ordered.
but im trying to get to a position where i can get a count of part orders or a count of party member orders from this one fact table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply