Get Subdimension (outrigger) count (built using Bridge Table)

Hello,

How do I get the subdimension count?

I have a table

FactPolicyAggregate
DimAutoVehicles
DimAutoDrivers

AutoVehicles and AutoDrivers have effective and expiration dates.

How do I get the number of AutoVehicles and AutoDrivers, who are

1) Active as of the end of month.
2) Not active at the end of the month?

Thanks,

Raghu.

Usually, things like this are handled by building a snapshot aggregate table. Trying to do this as queries against a detailed fact table using traditional BI tools is problematic and slow. It gets worse if the user wishes to do time comparisons (such as year over year). Building a snapshot allows you to embed classification logic in an ETL procedure or stored procedure, making queries simple and fast for the end user.

Nick,

Thank you for your insightful answers, as always.

So, is it a correct statement if I say 'Aggregate tables should always be a star schema for ease of operations'?

Thanks,

Raghu.

Yes.