Is it possible to get a distinct order count with a transaction line sales fact table?

View previous topic View next topic Go down

Is it possible to get a distinct order count with a transaction line sales fact table?

Post  donrickman on Fri Apr 04, 2014 2:46 pm


Hello All, I'm attempting to find best practice for a real world scenario.
If there's a post already on the forum with a "great" answer, please lead the way.

Any insight would be a greatly appreciated.

sales fact
id customer_id product source_order_id order_year
1 100 a 10001 2014
2 200 a 10002 2014
3 200 b 10002 2014

In my Excel Pivot tables, I'd like to see a report which looks like the following based on the table above.
___________
|year | orders |
|2014 | 2 |
--------------
Can't figure out how to roll up my data to a unique order count without making each order line a fraction of the "1" order based on qty.

-Don

donrickman

Posts : 3
Join date : 2013-06-17

View user profile

Back to top Go down

Re: Is it possible to get a distinct order count with a transaction line sales fact table?

Post  ngalemmo on Fri Apr 04, 2014 3:29 pm

Do a count(distinct source_order_id) in your query.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Is it possible to get a distinct order count with a transaction line sales fact table?

Post  donrickman on Fri Apr 04, 2014 6:18 pm


I don't get it.

That's what I do with SQL when i need a distinct count within a query with aggregates.

How do you do that off of a cube.

donrickman

Posts : 3
Join date : 2013-06-17

View user profile

Back to top Go down

Re: Is it possible to get a distinct order count with a transaction line sales fact table?

Post  ngalemmo on Fri Apr 04, 2014 6:34 pm

I don't know. I don't use cubes.

I was assuming you are loading excel using a query against a star schema. An order count is an aggregate of the sales detail. It would not make sense to include it in a line level query.

I defer the question to someone more familiar with the toolset. I'm sure there is a way.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Is it possible to get a distinct order count with a transaction line sales fact table?

Post  BoxesAndLines on Fri Apr 04, 2014 9:46 pm

The last place I worked that had cubes, we had to mix the grain (set a 1 on the first order line) in order to get an order count. Use at your own risk.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Is it possible to get a distinct order count with a transaction line sales fact table?

Post  nick_white on Mon Apr 07, 2014 2:48 am

Just a thought, the example data you've given is at the year level so I'm assuming this is aggregated data and you also have data down at the transaction level? If this is the case then why not just build another aggregate fact table that gives the information you require i.e. without the Product Dim FK?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Is it possible to get a distinct order count with a transaction line sales fact table?

Post  grahan007 on Mon Apr 07, 2014 3:11 am

donrickman wrote:
I don't get it.

That's what I do with SQL when i need a distinct count within a query with aggregates.

How do you do that off of a cube.

Hi

If you are using SSAS you can create a new measure or measure group set the usage to distinct count, select your fact table in the source table drop down list and select the column from Source column drop down list on which you want to do distinct count.

alternatively you can always create calculated measure for the distinct count if your count can not be aggregated across all the dimensions. But in this case the users must know over which dimensions this count is valid.

Regards
Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Is it possible to get a distinct order count with a transaction line sales fact table?

Post  donrickman on Mon Apr 07, 2014 6:18 pm

grahan007 wrote:
Hi
If you are using SSAS you can create a new measure or measure group set the usage to distinct count, select your fact table in the source table drop down list and select the column from Source column drop down list on which you want to do distinct count.

alternatively you can always create calculated measure for the distinct count if your count can not be aggregated across all the dimensions. But in this case the users must know over which dimensions this count is valid.

Regards
Harris

This worked. Thank you. -Don

donrickman

Posts : 3
Join date : 2013-06-17

View user profile

Back to top Go down

Re: Is it possible to get a distinct order count with a transaction line sales fact table?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum