One Sale Fact across multiple systems

View previous topic View next topic Go down

One Sale Fact across multiple systems

Post  pkettley on Wed May 29, 2013 4:53 pm

Hi,

Just wanted different opinions on how people would represent the following in a fact table.

One sale (of a product) occurs from a retailer that connects to system A in one country, System A connects onto System B in another country that processes the sale and then connects to System C which procures the product for the sale.

Each System represents an account with a different ID to represent the retailer account within the country for System A.

If each country had interest in the transaction would you represent the sale as one fact row or multiple rows for the events of the sale?

Please take into account that each country system can be managed by one person who needs to see the value of that one sale regardless of how many systems it went through.

My thought was to represent the one sale as one fact row, create a dimension to represent the transactional model as we'd know that product sold on system A would procure from System C, have a dimension for the retailer with 3 columns for each ID used to represent the retailer on each system.

Anyone else have a different design?




pkettley

Posts : 5
Join date : 2011-06-30

View user profile

Back to top Go down

Re: One Sale Fact across multiple systems

Post  ngalemmo on Wed May 29, 2013 5:17 pm

Multiple rows. If you need to, you can always build an aggregate the combines everything on one line. But that usually isn't necessary.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: One Sale Fact across multiple systems

Post  pkettley on Wed May 29, 2013 5:29 pm

Ok.

Question:
Whats happens if another person only should see one system with the one sale, and another person would need to see all. Should you have an aggregate that tries to determine what person is logged in and running the query?


pkettley

Posts : 5
Join date : 2011-06-30

View user profile

Back to top Go down

Re: One Sale Fact across multiple systems

Post  ngalemmo on Wed May 29, 2013 6:46 pm

You simply frame the query correctly. Facts should have sufficient dimensions to properly identify the context of the row.

Keep in mind, dimensional design is not about looking at a transaction, but analysis of many transactions, so a query to look at a transaction is not exactly the most efficient in a dimensional model. But it is easy enough to create a view that handles the complexity.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: One Sale Fact across multiple systems

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