Prev Month in Custom Group 1

[kpescatore]

Hi,
I am trying to create a pervious month and 6 months ago filter given a prompted date. My data is stored at the month level, usually as the last day of the month. I know it will work as a transformation within a metric but I need it to be a filter in a custom group.
I tried to use code from the tech note TN5200-7X0-0131. The problem is when I try to include a prompt in it the only one that would work is a value prompt on date, but that will allow the user to select any date and since my data is only stored for 1 day in the month there is too much room for user error. The prompt I would prefer to use is a “Choose from an attribute List” because it will actually select a specific valid date.
The filter is defined as ApplySimple("datepart(Year,dateadd(month,-1,#0))*100+ datepart(month,dateadd(month,-1,#0))",? [Load Date Prompt]) as per the tech note. The filter and the prompt will create the following SQL.

select sum(a11.CURR_UPB) WJXBFS1
from PRS_TBL_LN_PAYMENT a11
join PRS_TBL_LOAD_PERIOD a12
on (a11.LOAD_DATE = a12.LOAD_DATE)
where a12.MONTH_ID = datepart(Year,dateadd(month,-1,a11.LOAD_DATE in ('2004-02-29')))*100+ datepart(month,dateadd(month,-1,a11.LOAD_DATE in ('2004-02-29')))

A11.LOAD_DATE in (‘2004-02-29’) will obviously create an error.

Any help would be appreciated.

Thanks,
Keith

 

[entaroadun]

Element prompts can't be used in this way because the attribute form is embedded in the SQL that the prompt produces. Only value prompts will work in this way.

You can try to create some convoluted correlated subqueries to do what you need, but I think it's unnecessary.


The easiest solution is to model in two new attributes. This solution assumes that MONTH_ID is unique for PRS_TBL_LOAD_PERIOD.

1. Create an alias for PRS_TBL_LOAD_PERIOD. Name it LAST_PRS_TBL_LOAD_PERIOD.

2. Create a new attribute called Last Month. Model it in as MONTH_ID on the LAST_PRS_TBL_LOAD_PERIOD logical table but use the reverse of a last month conversion ApplySimple expression for the PRS_TBL_LOAD_PERIOD logical table.

Example: ApplySimple("(case when (#0+1)%100<>13 then #0+1 else #0+89 end)",[MONTH_ID])

I'm sure you can come up with more efficient conversion SQL. If you have any questions about the "%" operator (modulo), look it up.

3. Make the Last Month attribute a one-to-one parent of the Month attribute, joining on the PRS_TBL_LOAD_PERIOD table.

4. Create a new attribute called Last Month Load Date. Model it only to the LAST_PRS_TBL_LOAD_PERIOD logical table. Set the expression to LOAD_DATE.

5. Make the Last Month Load Date attribute a one-to-one child(?) of the Last Month attribute. (Just replicate the relationship between Load Date and Month.)

6. Create an element prompt on the Last Month Load Date attribute instead of the Load Date attribute. Use this prompt instead.


Your report should generate SQL that looks like this:

select sum(a11.CURR_UPB) WJXBFS1
from PRS_TBL_LN_PAYMENT a11
join PRS_TBL_LOAD_PERIOD a12
on (a11.LOAD_DATE = a12.LOAD_DATE)
join PRS_TBL_LOAD_PERIOD a13
on ((case when (a12.MONTH_ID+1)%100<>13 then a12.MONTH_ID+1 else a12.MONTH_ID+89 end) = a13.MONTH_ID)
where a13.LOAD_DATE in ('2004-02-29')


In other words:

Your prompt value (2/29/2004) gets converted to MONTH_ID on the LAST_PRS_TBL_LOAD_PERIOD logical table (PRS_TBL_LOAD_PERIOD a13 in the SQL). The MONTH_ID is 200402.

Then, on the funky conversion join, the Last Month value (200402) is converted into the Month value (200401). This is on the PRS_TBL_LOAD_PERIOD logical table (PRS_TBL_LOAD_PERIOD a12 in the SQL).

That gets you the 01/2004 load dates from A12 joining to the load dates on A11, which gets you the correct results with the last month conversion.


Try it before you shoot it down. That's what MD backups are for. Any questions, give a shout.

 

[JRO061601]

entaroadun,

i'm working with kpescatore on this . thanks for the reply. i couldn't quite get it to work. in order to make last month id a parent of month id, the attribute had to be modeled such that month id existed in both period tables. when i run a report with last month load date, last month id, month id, & load date on it, i get the following SQL:

select distinct a12.LOAD_DATE LD_DT,
a12.MONTH_ID MONTH_ID,
a12.MONTH_DESC MONTH_DESC,
a11.LOAD_DATE LOAD_DATE,
(case when (a11.MONTH_ID+1)%100<>13 then a11.MONTH_ID+1 else a11.MONTH_ID+89 end) CustCol_7
from PRS_TBL_LOAD_PERIOD a11
join PRS_TBL_LOAD_PERIOD a12
on ((case when (a11.MONTH_ID+1)%100<>13 then a11.MONTH_ID+1 else a11.MONTH_ID+89 end) = (case when (a12.MONTH_ID+1)%100<>13 then a12.MONTH_ID+1 else a12.MONTH_ID+89 end))
where a11.LOAD_DATE in (CONVERT(datetime, '2004-02-29 00:00:00', 120))

so it didn't quite work. the bigger issue is what we're trying to do is create a custom group with various time periods (this month, last month, 3 months ago, 6 months ago, 1 year ago). the idea is that if we could put this along the column headers, another custom group along the row headers, and 1 or 2 metrics on the template, we could get away with not creating conditional metrics with transformations. so even if your solution did work, we'd have to set up table aliases, attributes, and prompts for each time period we want. not the best end user experience.

i think we're going to go ahead with the conditional metrics w/transformations, unless you or someone else has another solution. it would be kinda neat if one could create a custom group with various time periods keyed off of one time prompt. maybe if transformations could be applied in filter criteria...

thanks again for the effort.

 

[entaroadun]

It doesn't sound like the attributes were modeled exactly to my spec:

Month                - MONTH_ID  - PRS_TBL_LOAD_PERIOD
Last Month           - MONTH_ID  - LAST_PRS_TBL_LOAD_PERIOD
                     - <CASE>    - PRS_TBL_LOAD_PERIOD
Last Month Load Date - LOAD_DATE - LAST_PRS_TBL_LOAD_PERIOD

Last Month   1-1 parent   Month                  PRS_TBL_LOAD_PERIOD
Last Month   1-1 parent   Last Month Load Date   LAST_PRS_TBL_LOAD_PERIOD

This should work. The key is to use multiple expressions for the ID form in the "Last Month" attribute. Link the regular MONTH_ID expression to only the LAST_PRS_TBL_LOAD_PERIOD logical table. Link the ApplySimple expression to only the PRS_TBL_LOAD_PERIOD logical table.


As for driving your multiple time frames off of different time frames:

Let's say you've created the six-month transformation attributes the same way you've done the last-month ones.

Create yet another alias of PRS_TBL_LOAD_PERIOD called PROMPT_PRS_TBL_LOAD_PERIOD.

Create a new attribute called "Prompt Load Date". Set the expression to LOAD_DATE and put it on the LAST_PRS_TBL_LOAD_PERIOD and SIX_MONTH_PRS_TBL_LOAD_PERIOD logical tables. (Basically, link it to every attribute you want to drive off of the prompt.)

Make Prompt Load Date a 1-1 parent of your Last Month Load Date and Six Month Load Date attributes.

Change your prompt to use the Prompt Load Date attribute instead.

In the custom group, create a custom group expression for your Last Month row and a custom group expression for your Six Month row. (You should already have these.)

For the Last Month custom group expression, use a relationship set filter. Take the Prompt Last Month prompt and put it in a filter, converting it to the Last Month Load Date attribute level.

For the Six Month custom group expression, use a relationship set filter. Take the Prompt Last Month prompt and put it in a filter, converting it to the Six Month Load Date attribute level.

The user answer to the Prompt Load Date attribute will drive each filter expression in the custom group differently based on the relationship set filters.

Try it out and give a shout.

 

[JRO061601]

ok, i did misunderstand your previous directions. corrected, i can return the previous month:

select distinct a12.MONTH_ID MONTH_ID,
a12.MONTH_DESC MONTH_DESC,
a12.LOAD_DATE LD_DT
from PRS_TBL_LOAD_PERIOD a11
join PRS_TBL_LOAD_PERIOD a12
on (a11.MONTH_ID = (case when (a12.MONTH_ID+1)%100<>13 then a12.MONTH_ID+1 else a12.MONTH_ID+89 end))
where a11.LOAD_DATE in (CONVERT(datetime, '2004-02-29 00:00:00', 120))

however, this is just returning the attribute values. when i throw a metric on the template, sum(curr_upb) from the PRS_TBL_LN_PAYMENT table, i get the following error:

Error: SQLEngine got an Exception from DFC: [DFCENGINE] Engine Logic: Fact does not exist at a level that can support the requested analysis. Fact: "Current Unpaid balance". Level: "Last Month Load Date".
Error in Process method of Component: SQLEngineServer, Project 6, Job 2613, Error Code= -2147212800.


so i created an extension on the fact curr_upb to extend it to 'last month load date'. it produced SQL with a correlated subquery to qualify on the load_date in the fact table, and returned the correct data. i'd post it, but after trying to do a couple of other things, i can't reproduce it again. hopefully i haven't screwed up the MD...

bottom line though is while it looks like your solution would work, it is far too much overhead to produce all this and maintain the fact level extension. and i'm not even sure what it would do with the metrics we have built using attribute values. we have to move on and use conditional metrics, but i'll keep this solution in the back of my head should reporting requirements eventually trump the overhead cost.

thanks again.

 

[entaroadun]

Oops! My bad...

Make Last Month Load Date a 1:1 parent of Last Month instead of a 1:1 child.

That way, the dimension will look like:

Load Date -> Month -> Last Month -> Last Month Load Date

The fact exists at the Load Date level, and MSTR can "roll it up" the hierarchy to Last Month Load Date, so a fact extension won't be necessary.

Try it out and it should work. Sorry about that!

 

[JRO061601]

ok, i can see that. but still, i have to move on for now. if i come back to it i'll let you know how it turns out. thanks!

 

[JRO061601]

I had to come back to this and thought of another way. Thought I'd post it for what it is worth. I created a table (below) and in the project created two new attributes - 'Period' and 'Prompt Date'. It is modeled such that 'Prompt Date' 1:M 'Period' 1:1 'Load Date'. The user picks a data from 'Prompt Date' and it feeds into 4 dates in the fact table. So the time transformations are hard-coded, but it appears to work.


LOAD_DATE PERIOD_ID PERIOD_DESC PROMPT_DATE
1/31/2004 1 Current Month 1/31/2004
12/31/2004 2 Previous Month 1/31/2004
7/31/2003 3 6 Months Ago 1/31/2004
1/31/2003 4 1 Year Ago 1/31/2004
2/29/2004 5 Current Month 2/29/2004
1/31/2004 6 Previous Month 2/29/2004
8/31/2003 7 6 Months Ago 2/29/2004
2/28/2003 8 1 Year Ago 2/29/2004
3/31/2004 9 Current Month 3/31/2004
2/29/2004 10 Previous Month 3/31/2004
9/30/2003 11 6 Months Ago 3/31/2004
3/31/2003 12 1 Year Ago 3/31/2004

 

[entaroadun]

Excellent solution...