Suggestions - To bring Missing month Data for Targets

[Former Member]

Hi All,

We are building universe(BO 4.0) and reports on top of a OLTP data base(SQL Server).

We are facing a challenge in regard to a requirement.

The Requirement is to create a WebI Report that shows the Actual & Target Sales for the Field Agents based on the period selection('Current Month','Previous Month',A date range).

My current datamodel has 3 tables(let's ignore rest for now).
An employee table, A fact table which has Actuals (Date grain) and another fact table which has Targets(Month grain) and please note that there is no exclusive time dimension table.

The target is defined for an employee for a month.In cases, The target remains the same for months(no additional records are getting inserted in the table) until a new target(a new row for the same employee for a different month) is defined.

Say for example EMP1 has targets defined for Jan'13,Feb'13 & May,13(3 rows in the Target fact and no rows for the missing months).

If the user runs the report on Apr'13 by selecting 'Current Month' the report should show the latest target defined for EMP1,which is Feb'13 Data.
The report should always shows the latest target based on the period selection and there are missing months for EMP1 as well.

Note: We are able to achive this requirement, if the targets are defined for EMP1 for every month in a year.

We are struggling to create a dynamic filter to traverse between months based on the period selection, as there are missing months as well.

Checked with few BO People and they suggested that we should get the data changed in the DB(Target defined for EMP1 for all the months in a year) and nothing much can be done from the BO end.

Please share your valuable suggestions on this.

Kindly let me know, if you need any additional information.

Thanks,

Siva

[Henry_Banks]

Hi,

Is this transactional data being pushed to a MSAS (SSAS) data warehouse?  this sounds like a DW requirement.

regards,

H

[Former Member]

Hi Henry

Yes, it is a transaction Data but we are not pushing it to MSAS or SSAS but using it for Webi Reporting. We are currently building a BO unvierse on top of OLTP system.

Thanks

Siva

[Former Member]

Hi Siva,

Can you attach sample excel dumps of the data so that this can be replicated.

A SQL that could possibly fetch the latest month data:

Select Max(Month_Field), Emp,Revenue_Target from Target_Table where Moth_Field < (Month prompt entered by user) group by Emp;

Thanks,

Prathamesh

[Henry_Banks]

Hi,

Again:  If you are doing time-dependent analysis of historical transactional records, you should be considering a data warehouse strategy as your single-point of truth.

this will also alleviate the stresses of reporting off the OLTP system, for the benefit of the productive business functions. 

Regards,

H