[SOLVED] using a database function to determine query subject?

[rannandale]

Hi guys,

First post :) - so a short introduction is a good idea.

I work for an ISP in South Africa, and we've recently acquired Cognos. I've been on the report studio and  the framework manager training. This question has even stumped my instructor hehehe.

On to the meat of the (query) subject:
We have a database in sybase IQ that contains ± 30 views, which are all union alls of 52 [weekly] other tables each.

This was done at design time [albeit foolishly] to try and minimise the data in each table. Currently we pre-detirmine which view to query using perl to dynamically build the SQL query for the IQ DB.

This is rather tricky to achieve in IQ. So, I've written a DB function that you can run which returns the appropriate view name that you need to use based on an input parameter.

Is there a way I can leverage this function [or any other method for that matter] to dynamically determine which Query Subject in FM needs to be queried?

Any help would be greatly appreciated.

Thanks,
Riaan

[MFGF]

Hi,

Do all the views have the same structure?  If so, it would make more sense to define a new database view which calls your function and uses the results of this to point to the appropriate underlying view.

If the views each have a different structure, they will need to be modelled independently in FM as they will each have a different projection list, in which case your function will be redundant.

Notwithstanding, I'd expect you will get very poor response times from the database given the fact you are potentially unioning up to 52 tables.  It might well be worth setting up a new series of tables in star schema form, and populating these nightly from your original 52 tables?

MF.

[rannandale]

Hmmm, I like your view idea.

The problem with that is, that the view doesn't know which key you are querying for:

Basically you have an IP address, let's assume 127.0.0.1, which has all it's data in a view called localhost_flows.

I need to be able to tell cognos, that because of the ip being X, it must use query subject Y. These maps are largely static, and I already have a table called router_map which does the above. So, I've set up a parameter map, and am trying to use it like this [in a new query subject from datasource]:
Select   * from  [Archive].#$[Router Lookup]{ prompt(?RouterIP?,'token')}#

This fails with some obscure error. I also tried putting ?RouterIP? as 'RouterIP' neither worked. I think I've just got the syntax wrong...

Busy waiting [for some reason it takes forever] for the Macro window to open in cognos to try and fix it.

EDIT:

Yes, all the gazillions of tables have the same structure.
Yep... this design sucks and I've been suggested a star schema by the cognos consultants already ;) - Busy working on this.

The problem is data maintenance, because deletes take REALLY long on IQ, specially if you are expiring upwards of 1 billion rows a day. Doing these deletes on a smaller scale and in parallel seems to work a bit better.

[MFGF]

Hi,

Have you tried putting the router_map data into a Parameter Map in Framework Manager - keyed on IP (or the relevant part of the IP) and with the Value for each IP defined as the relevant view name.

One other question - how do you currently know the value of the IP?  Is it being stored in a session parameter?

If so, your macro would then become

#$YourParameterMap{$YourIPSessionParameter}#

Regards,

MF.

[CognosPaul]

Ahh, I wrote this then walked away without hitting post. It seems too interesting to let MF have all of the fun.

Welcome Riaan!

What you're asking for may be possible with macro functions, depending on how your tables are set up.

As an example, lets say you have 12 tables - one for each month. Sales01, Sales02, Sales03...

The SQL in the query that pulls the data would be something like this:

Select * from  [schema].Sales#timestampMask($current_timestamp,'mm')#

The macro would resolve (at the time of this writing) to 04, so Cognos would attempt to run the following SQL:

Select * from  [schema].Sales04

The next example would be if your tables are Sales201001, Sales201002...
Select * from  [schema].Sales#timestampMask($current_timestamp,'yyyymm')#

The difficulties start arising when you have more complex calculations. Let's say your tables are set up as
Sales01, Sales02...Sales51, Sales52

Unfortunately there is no weekofyear macro function, so in order to get the week number you have to cheat a little.

The solution I have, which may not be the best, would be to use a parameter map. Set up a time dimension with the key in yyyymmdd format, with a week of year column. Import that table into the model. Create a parameter map called weekLookup based on that table with the key (in yyyymmdd format) as the key and the week of year as the value.

Now that you have the lookup list you can set up the SQL as:

Select * from  [schema].Sales#$weekLookup{timestampMask($current_timestamp,'yyyy-mm-dd') }# 

Which will, of course, resolve as:
Select * from  [schema].Sales17

[rannandale]

Nice - I like that idea Paul. I've got other areas where that'll work nicely.

As for the current problem, specifically the problem that I listed in my previous post.

I found this while digging around:

The Param lookup ends up not dropping trailing white space, because FM sees this object as char(15) and not varchar.
#$RouterMap{'10.1.1.4       '}#

I then deleted that param lookup, and created a new query subject from the model, where I declare 'router' like this:
rtrim (  convert (varchar(15),[GSS].[router_map].[router])   )
#Both functions being Sybase, so it shouldn't create too much overhead.

This doesn't work either. I still get the leading blanks.

So I use cognos functions, in order to do local processing, like this:
trim (  cast ([GSS].[router_map].[router],varchar(15))   )
# It's not going to get queried thousands of times per second, so it should be fine.

I now get the lookup to look like this:
#$RouterMap{'10.1.1.4'}# 
# Awesomeness!! We're almost there.

I now just need to add the prompt bit back in:
Select   * from  [Archive].#$RouterMap{ prompt('RouterIP','token')}#

But... it doesn't even raise a prompt for 'RouterIP' - it's like the macro doesn't like having a function imbedded in it.
I've had a pretty busy day, and it's past home time already, so I'm hoping that I'm missing something fairly obvious.

Just to give you an idea, the lookup looks like this:
Key -> Value
127.0.0.1 localhost_flows

So the idea is, that I will have an IP address coming from a different query, so the 'RouterIP' prompt value should be pre-populuated, and in the above example the resultant query should be:
select * from [GSS Archive].localhost_flows

Thanks in advance! :)

EDIT:
MOTHER#$#!#!!!!!!!!!!!!!!!!! - sorry for swearing, just really happy I got it sorted.

It doesn't prompt... if I manually capture an IP address against 'RouterIP' in options [in the query window] when testing, it works!!!

BRILLIANT!!!

Thanks so much guys!