Datediff from a Fact table

[Alfredomulet]

Hello, anyone knows what MSTR objects would I need to create:

1. To calculate the number of days between the Changes of Status of an invoice (FACTURA)?
2. To obtain the last status of the Invoices (FACTURA), at a given date (for example on Jan 15th)?

The change dates are only stored in the fact table F_MOV_FACTURA that looks like:

F_MOV_FACTURA

ID_FACTURA--DATE_ID--STATUS_ID--F_ASSOCIATED_COST
1000004--01/05/2004--1--35.25
1000005--01/06/2004--1--35.20
1000004--01/14/2004--2--21.27
1000005--01/19/2004--2--27.10
...

I guess it would be easier to calculate it if I store the dates in columns rather than rows (something like FACTURA, STATUS001_DATE, STATUS002_DATE, etc... and using a function like ApplySimple("datediff(... )) but there are more than 60 different status for the invoice, so we'll need as much attributes...

Thanks in advance for your ideas.

Alfredo

 

[nlim]

1. I'm not sure which statuses you will use for the difference since you have 60 statuses...but let's assume you want days between status 1 and 2. And your report looks like

Invoice Days between 1-2
------- ----------------
1000004 9
1000005 13
subtotal avg 10.5

Here's the rough idea - assuming your each invoice has only 1 row for each status.

1) add the date-id column as a fact called F1.
2) create metric M1 defined as Max(f1) at the invoice ID dimensionality, filtered by status = 1
3) create metric M2 defined as Max(f1) at the invoice ID dimensionality, filtered by status = 2
4) create a metric M3 defined as M2-M1. Now create you report with M3 on it.

You can also make the filters in M1 and M2 prompted.

2. to get the latest status, you need to use what's called a non-aggregatable metric.

- make sure date is an attribute.
- make sure status is a fact.
- create a metric M1 defined as max(status fact) dimensional by reportlevel, invoice and date. For the date dimensionality, under the group by, pick "ending (Fact)". This will cause the metric to find the latest status. If you need the earliest status in a time period, pick "starting (fact)".

now add M1 to any report.

good luck, let us know if it works.

 

[entaroadun]

Your MSTR implementation can become a lot easier if you do just a little more work on the relational database.

Add a few more fields on your fact table so it looks like this:

ID_FACTURA DATE_ID    STATUS_ID NEW_DATE_ID NEW_STATUS_ID F_ASSOCIATED_COST
1000004    01/05/2004 1         01/14/2004  2             35.25
1000005    01/06/2004 1         01/19/2004  2             35.20
1000004    01/14/2004 2         NULL        NULL          21.27
1000005    01/19/2004 2         NULL        NULL          27.10

For the first requirement, map the STATUS_ID and NEW_STATUS_ID fields in as attributes. Create a fact that does a datediff between the DATE_ID and NEW_DATE_ID. Create a metric that does the SUM of that fact. Create a report that filters on STATUS_ID = 1 and NEW_STATUS_ID = 2 and use the metric. That's the simplest, most straightforward way to get the number.

For the second requirement, map the DATE_ID and NEW_DATE_ID as attributes. Create a report that has a filter of DATE_ID <= [Jan 15th] and (NEW_DATE_ID > [Jan 15th] or NEW_DATE_ID is NULL). Pull the STATUS_ID attribute on your report. That's the simplest, most straightforward way to get the status.


The table structure above can be achieved with either a table or view. Let us know if you need help writing an ETL process to populate the table. Also, let us know if you need help creating a view, if space is a concern. Both are very easy to do.