Dimension Design with intermediate tables between fact and dimension

Hello everyone,

I have a current assignment to model a data-mart for an asset management software (IBM MAXIMO).
I have a current design problem would like to ask about.

I have concept of Location, and Asset.
An asset can move from location to location across its life cycle (only present at one location at a time).

An Asset has :

• Unique Code
  
• purchase price
  
• hire rate
  
• Installation Date On Location
  

A Location has:

• Unique Code
  

The hire rate changes every time an asset moves, even if moved back to location it was at previously.
So each asset has multiple hire rates but only one purchase price.

I am not sure how to model this relation, if say i want to be able to get :

• average hire rate for each equipment over its life or over certain time span
  
• average hire rate for each location over its life or over certain time span
  
• average purchase price for each location over its life or over certain time span (ex: say user wants know value of assets at a location)
  
• purchase price of each asset
  

Should i have :

• Location Dimension (each site is only one unique row)
  
  
  
  • Unique Code
    
  
  
  
• Asset Dimension (each asset is a unique row)
  
  
  
  • Unique Code
    
  • Purchase Price
    
  
  
  
• AssetLocationMoves (each combination of {location, asset, Arrival date} is unique)
  
  
  
  • Location Unique Code
    
  • Asset Unique Code
    
  • Arrival Date
    
  • Hire Rate
    
  
  
  

But in this case will user be able to tell what is Sum of purchase price that where at this location during a year for example ?
or even simpler what are assets currently on this location ?

Should i remove the asset dimension completely and just explode purchase (along with other non changing attributes) on the AssetLocationMoves table ? and if so will i still be able to correctly report back Sum of purchase price at a a location (seeing that this purchase price would be repeated for every move)

Also, how would i introduce the Date dimension in this case. For example, user wants average hire rates from year 2010 to year 2012 .
What would be an appropriate Date Dimensions and how would i create link in this case to the mentioned dimensions and facts

ame54 wrote:
But in this case will user be able to tell what is Sum of purchase price that where at this location during a year for example ?
or even simpler what are assets currently on this location ?

It depends on what you're trying to answer here. Does the sum of purchase price at a location include all the assets that were at the location for a period of time in the past but is no longer at that location? Most likely not so it sounds like the real question is what's the sum of the purchase price at a location at a given point in time. You essentially want to know how much inventory was at a location. In order to answer that question you either need to create a report that can take all the movements and determine where the asset was at a given point in time or create a snapshot table that holds that information.

ame54 wrote:
Also, how would i introduce the Date dimension in this case. For example, user wants average hire rates from year 2010 to year 2012 .
What would be an appropriate Date Dimensions and how would i create link in this case to the mentioned dimensions and facts

Your arrival date would basically be a reference to the date dimension.

You don't have enough information to do what you want to do. First, you are missing information about the length of time of the lease. You have when the item arrives, but not when it is removed (or planned to be removed). I would also imagine there are gaps when the item is not on lease.

Without a time span (or quantity) you cannot calculate average rates. If the equipment was leased for $100 for 1 day, and $50 for 20 days, is the average $75 or $52.38? In general, questions involving revenues are usually driven from invoicing data.

ngalemmo wrote:Without a time span (or quantity) you cannot calculate average rates. If the equipment was leased for $100 for 1 day, and $50 for 20 days, is the average $75 or $52.38? In general, questions involving revenues are usually driven from invoicing data.

That is a good point, and so far i still don't have this information. It is taken so far that when an asset is assigned to a location its given a Hire Rate, and that's all information i have so far.
So i guess so far i can only report the full history hire rate of a site, or average hire rate given for an asset across its life.

zip159 wrote:It depends on what you're trying to answer here. Does the sum of purchase price at a location include all the assets that were at the location for a period of time in the past but is no longer at that location? Most likely not so it sounds like the real question is what's the sum of the purchase price at a location at a given point in time. You essentially want to know how much inventory was at a location. In order to answer that question you either need to create a report that can take all the movements and determine where the asset was at a given point in time or create a snapshot table that holds that information.

Yes you are correct for that, but since i still don't have a correct Date information for a proper snapshot table, still don't have a solution for this.
Also, i have question regarding some "header" information regarding Assets like purchase price or accumulated depreciation.

This would require, as for my knowledge, a "header" table for assets and a details table that holds moves of the asset across different locations. In the header table i would have information on level of the asset, and details would hold information about asset on a site example Hire Rate or installation cost.
Is this a proper approach or should i also follow similar to dimensions where its better to combine different levels in a single table, and not allow a header details relation ?