Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

5 posters

Go down

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

Post  donrickman 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

Back to top Go down

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

Post  ngalemmo Fri Apr 04, 2014 3:29 pm

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

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

http://aginity.com

Back to top Go down

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

Post  donrickman 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

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

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

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

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

Post  grahan007 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

Back to top Go down

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

Post  donrickman 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

Back to top Go down

Is it possible to get a distinct order count with a transaction line sales fact table?  Empty 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

Back to top

- Similar topics

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