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

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

Do a count(distinct source_order_id) in your query.

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.

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.

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.

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?

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