This one is a bit difficult to explain...

I have a cube the reports business by the date the business was first entered. 

The business comes in from different organizations and those organizations are tracked in the fact table.

There is also a Date Dimension table and a Dimension Table for the organization.

Related to the organization, is a status that is calculated daily and which reflects the level of organization they are.  This results in a table that stores level changes (delta) by organization

For Example:

Org A,  Level A, 01/01/2012

Org A, Level B, 03/14/2012

Org A, Level A, 10/20/2012

We want to track the level, as it relates to the date, month, quarter, and year of the business on that fact table.

This delta table can tie to both the Date Dimension and the Organization Dimension.  It could also tie to the Organization ID on the fact table.

I tend to think it should be a fact table, but on SQL Standard I've had trouble implementing it.  I'm not sure where to go from here...

Thanks,

- Nick

Hi Nick,

According to your description, you want to track the level change for different organizations based on date. Right?

In this scenario, since you have organization and data dimensions, you can use that table as a fact table and make the level to be a measure, even it's not numeric. when designing the measure, you can set the the aggregation type into LastNonEmpty, because when aggregating this level on organization level, only the last one should be the effective level. 

Regards,