Another Crosstab

[danasellers@udayton.edu]

Hello again crosstab gurus,

I have what seems to be a simple one, however, it's causing me fits.  Here's the desired outcome:

                                          2009    2008  Delta(09-08)    2007 Delta(09-07)    2006 Delta(09-06)
                                     
Prospects                             1,167   369    216.3%          674    73.1%            581  100.9%
Inquires                                1,167   369    216.3%          674    73.1%            581  100.9%
Applications                           564     341      65.4%          277    103.6%           564  0%

Basically this is showing application statuses down the left, academic years across the top and the total number of students in each year.  Then it's showing the difference of each year to the current year and the % diff each year. 

Any ideas how to get this diff to show for each year other than the 'current' (or first year if I sort the years descending)?

Thanks so much!
Dana

 

[MFGF]

Hi,

Are you using a dimensionally modelled package or a relational package with query subjects/query items?

MF.

[danasellers@udayton.edu]

It's a relational model.  Please tell me it's still possible.  I'd rather not create a DMR since I'm not that familar with them.   :o

[paddhu]

I doubt the ease with which this could be arrived, without a DMR model. I see an exhaustive and non-manageable workaround only.

[MFGF]

Hi,

You would probably need to set up multiple queries to calculate your deltas, then join them back together to provide data for the crosstab if you stick with a relational package.

With a dimensional package, this report would be very simple - just drag in the relevant year members and add calculations for the deltas.

MF.

[danasellers@udayton.edu]

Thanks gurus!  Now I just wish one of you were sitting right beside me to guide me through creating the DMR!  I'll probably just have to create several queries and join them together.  Unless there's an easy way to turn a relational pkg into a DMR???

[MFGF]

Unless there's an easy way to turn a relational pkg into a DMR???

It's not difficult if you have Framework Manager (and know how to use it, of course!  :) )

Have you ever designed/built an OLAP cube before?  The process is very similar.

MF.

[danasellers@udayton.edu]

I'm really familar with Framework Manager.  I've never built a cube before - well, change that, I did about 12 years ago on the old Cognos Transformer.  I also went through FM training (again) about 2 months ago, but I'm not too confident in how to get started with my own data.  The training is so set up for you that when you get back to your own, it all looks so foreign.  I'm currently starting with Sungard/Cognos delivered packages for our Banner ODS.  Can I use those to start with and create a DMR within that?

[MFGF]

Yes you can - add a new namespace to act as a container for your Regular Dimensions and Measure Dimensions.  Assuming your current FM model is defined according to best practices and has model query subjects emulating star schemas, it will simply be a case of setting up a regular dimension based on each dimension query subject, and a measure dimension for each fact query subject.  If your dimension query subjects also have determinants defined at each logical level of detail, the quickest way to get a regular dimension is to right-click the query subject and 'Merge in new Regular Dimension' - the determinants then drive the DMR levels.  The process should be pretty much as it was in your FM training.

Regards,

MF.

[danasellers@udayton.edu]

Thanks MF - one last (I hope) question.  Can I somehow use a test file as a measure?  I'm now relating this to my other quesiton topic "Different Kind of Crosstab".  I'm thinking I probably should create this one in a DMR as well?
I appreciate your time and expertise!

Dana

[MFGF]

Sorry - not quite clear on what you mean by "use a test file as a measure".  Can you elaborate?

MF.

[danasellers@udayton.edu]

So sorry for the confusion.  My fingers weren't working back then (and probably my brain wasn't either)!  I didn't mean "test file" I meant 'character field'.  Can a field that is defined as VARCHAR or CHAR be used as a measure in a DMR?

[MFGF]

No - only real measure values can be used, as they get aggregated.

MF.