October 20, 2016 at 9:22 am
I have the classic FactOrderLine table at the lowest granularity of items on orders. I have built this in my cube and when i connect with excel to get the total count i get the total count of all item on all orders as expected. Can I add measures to this fact table that will give me the total Orders without creating a new fact table at Order level granularity?
quite new to cube design
October 20, 2016 at 10:08 am
ps_vbdev (10/20/2016)
I have the classic FactOrderLine table at the lowest granularity of items on orders. I have built this in my cube and when i connect with excel to get the total count i get the total count of all item on all orders as expected. Can I add measures to this fact table that will give me the total Orders without creating a new fact table at Order level granularity?quite new to cube design
Yes you can do this without adding another fact table at a different granularity.
The first option would be a distinct count measure on the Order ID in the fact table. The pros of this are that it's easy to set up. The cons are that the performance can be very very bad.
The second option is to replicate the behaviour of the distinct count using a calculation. Something along the lines of
COUNT(FILTER([OrderDim].[OrderID].[OrderID].MEMBERS, [Measures].[Measure in your Fact Table] > 0))
The third option is to replicate the behaviour of a Distinct Count measure by using a Many To Many join to your Orders dimension.
Please see https://svangasql.wordpress.com/2012/03/14/distinct-count-in-analysis-services/ for instructions on this.
These three options are all for SSAS Multidimensional. For Tabular, see option 1 here: https://www.mssqltips.com/sqlservertip/3043/different-options-for-creating-a-distinct-count-measure-in-ssas/
Hope this helps 🙂
October 21, 2016 at 8:55 am
It did thanks, the first option worked well. Im not knowledgeable enough on the 2nd and 3rd just now so ill go with 1 thanks.
October 21, 2016 at 9:31 am
ps_vbdev (10/21/2016)
It did thanks, the first option worked well. Im not knowledgeable enough on the 2nd and 3rd just now so ill go with 1 thanks.
Okay well be warned, as I said performance can be very bad (both for processing and querying) over larger fact tables.
October 21, 2016 at 9:36 am
oh definitely taking that on board but literally just started developing in SASS this week so I'll revisit this again when I'm more familiar with cubes. it performs ok just now but that will change I'm sure.
Thanks,
Philip
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply