MDX question

0
votes

I am developing a cube with Analysis Services 2000 for a web application where users can register and unregister to the site. So, the "user" table has these three fields:

  • activo (1 or 0)
  • fechaAlta
  • fechaBaja
When the user activates his account, the application saves the "fechaAlta" and puts 1 on "activo" field. When the user unsubscribes his account,the application updates the field "activo" to 0 and saves the "fechaBaja".

The information I need is to know how many users are active at a time, through a time dimension. Something like:

Year Month Day Active users

2009 January 1 10 (10 activations this day)
2009 January 2 12 (3 activations this day and 1 unregistered)
2009 January 10 17 (5 activation this day)

Even I query on february 2009, I need to know that in January 1th there was 10 active users (the user that unsubscribed the 2th must be counted).

I developed a cube where the fact table is the user table, and create two dimensions for both date fields (fechaAlta and fechaBaja). Also I created this calculated field:

active by month:
Calculation subcube: {[Measures].[Altas]}, [Fecha Alta].[Mes].MEMBERS
Calculation formula: sum({Descendants([Fecha Alta].currentmember,[Fecha Alta].[Día])},[Measures].[Activo])

active to day:
Calculation subcube: {[Measures].[Inscritos]},[Fecha Alta].MEMBERS
Calculation formula: sum({Periodstodate([Fecha Alta].[(Todos)])},[Measures].[Activo])

I don't know how to discount the unregistered users only from the day indicated on fechaBaja.

Thanks.

2
mdx

2 Answers

1
votes

This is a classic slowly changing dimension issue. What you are describing is a type 2 slowly changing dimension see here

You need to make sure that your user dimension has a surrogate key. Then you create a new record in your user table each time the user changes status and then you use effective dates to control which surrogate key to insert into your fact table. This will let you report on the users effective status at any point in time.

0
votes

I think you need a "User Status" dimension, then you can show this against Time, with the measure being count of users.