Dynamic Dimension with link to Text Dim (maybe Set Analysis??)

[#1]

Hi all,

i have a question concerning the dimensions in tables.

The database is a Ticket with change history and oldvalue and newvalue column and overall status of the ticket. The Status Text is connected via ID (Dim Status with Status_ID and Status_text).

The content of oldvalue and newvalue columns can be Statuschanges, Textchanges, Responsibility changes etc. and is defined in a Change_Field.. In the formula i have reduced the changes to show only the status changes ( count ({$<Change_Field={"Status"}>} changes ). This is working fine.

Now my problem: in the newvalue and oldvalue column (which is shown as dimension in the table) is only Status_ID. I can not connect it to overall Status or built a special Status Dimension for new and old value via Import Script because of circular references (i did it already for other change_field types).

Is it possible to use the Statustext in table dimension if i explain the connection with dynamic dimension. I thougt of something like "= ( { oldvalue ={status_id} } Status_text)" ??

Thank you very much in advance.

Kind regards

Jezze

[brenner_martina]

Hi, Jezze,

normally you can of course use calculated dimensions, but I think you have to use aggr-function!

Greetings

Martina

[jonathandienst]

Jezze

If I understand you correctly, you have two status fields that you would like to join to the Status dimension.

If the status dimension contains only StatusID and StatusText, then I would use applymap to bring the status text into the fact table. It is quite OK and often better, to denormalise your data in the QV model.

The script would include:

MapStatus:

Mapping Load StatusID,

     StatusName

From Status;

And in your fact table load:

FactTable:

Load ....

     ....

     ApplyMap('MapStatus', newvalue) As newstatus,

     ApplyMap('MapStatus', oldvalue) As oldstatus,

     ...

From ...

You can also drop the oldvalue and newvalue fields after the loading is complete:

     Drop Fields oldvalue, newvalue;

Hope that helps

Jonathan