Avoiding a loop in the data model

[#1]

Hello,

I decided to ask the community for this one, i am all out of ideas:

I have three dimension tables and a fact. All dimensions are dates, and the fact has three date columns and one ID.

First dimension is the EnteredDate, the second is SentDate and the third is ReceivedDate.

How can I achieve a graph with three different metrics(expressions) and show in time the count of ID's for each dimension?

Entered:

ID     Month

1     jan

2     jan

3     feb

4     feb

5     mar

Sent:

ID     Month

1     jan

2     feb

4     feb

5     mar

Received:

ID     Month

1     mar

4     mar

Desired result:

Month     Entered     Sent     Received

Jan         2               1          0

Feb         2               2          0

Mar         1               1          2

Thank you!

[Anonymous]

If you can live with it, I would recommend the following table structure:

LOAD * Inline [

Month,ID,Status

jan,1,Entered

...

...

jan,1,Sent

...

...

mar,1,Received

...

...

];

Where ... represents data I unfortunately don't have time to type in. So, "Status" is a new field which tells you which table the data originates from.

Then you can have Month as your Dimension, and your straight table expressions would be

count({$<Status={"Entered"}>} ID)

count({$<Status={"Sent"}>} ID)

count({$<Status={"Received"}>} ID)

Hopefully that made sense

[lironbaram]

hi check the attach example

[#4]

hi all,

thank you for the help, but i am not able to load the data in this way.

I have an operational database (no aggregates available) with one big table that has:

ID .... DateEntered DateSent DateReceived  ...

I created, based on this, a start schema with the ID as a fact (and other columns) and with KeyDates for each date dimensions. See attached screenshot. I want to be able to move from a month level to a quarter or year, etc. so i need those tables as they are now.

The only way i can think of is creating a separateisland, but i'm not sure how to do that in my case, because i think i need a separate calendar table that can join with duplicates of the three time dimensions. Or not?!!?

Thank you!

[vijay_iitkgp · Accepted solution]

Hi,

You can try this:

Load

Id,

DateEntered as Date,

'Entered' as Flag

From T1;

Concatenate

Load

Id,

DateSent as Date

'Sent' as Flag

From T1;

Concatenate

Load

Id,

DateReceived as Date

'Received' as Flag

From T1;

Hope this will help

[#6]

Hi,

Please look at the attached file. It solves your problem.

Thanks,

Niranjan M

[#7]

thank you! i forgot the power of Concatenation