Report as datasource returns cross join error because of aliases in package

[rb338]

Hi all,

I've got the following setup with Cognos 8.3:
- Source: SQL Server database with DMR model
- Package: standard Framework model with database view (relational, based on Source)
- Report: specifically designed for powercube, based on database view from Package
- Powercube: uses database view from package as source

The framework contains multiple fact tables and a bunch of dimension tables. Some of these dimensions are aliased, such as the Date dimension. Eg. I have a table on the database named DIM_DATE, and the framework model has two aliases: DIM_DATE_START and DIM_DATE_END. No problems here, I've been using this model for years.

The report I've created is a simple one: a single query using a single fact table and a couple of dimensions (also the start and end date dimensions mentioned earlier). When I view the report, it works fine.

Now, what I want to do is add the report to the powercube in Transformer. When I want to add it using the New Data Source wizard, I get a Cross Join error on the DIM_DATE table. When I don't use the aliased tables, it works fine.

Someone else has posted the same problem a long time ago over here, with no response:
http://www.cognoise.com/community/index.php?topic=5084.0

So my question is: is this a bug in Cognos? How do I work around this, knowing I can't modify the framework model as it is used for hundreds of reports?

Thanks for any response :)

[Arsenal]

Sorry, I have no definite solution for this but am curious as to what would happen if you try this:

Use your dev environment. Open up FM, delete the 2 shortcut aliases (might invalidate dependent QS's)  from your database view layer. Now, create 2 copies of the Date table (you'll have 3 in total then) by just importing it in again under the previously deleted name ( same as the prior shortcut aliases) and then add in the proper relationship to the fact for the two. Then re-validate the QS's thatg depend on these 2 and publish the package on Dev to a temp folder. Repoint the report to this package, run it and make sure it's fine etc.
Now try to build a cube.

[cognostechie]

There is a PDF on IBM site to suggest the best practices when using  Report/Package as a datasource in Transformer.

Essentially, the datasources should be designed the same way as the earlier IQD method. There should not be a bunch of query subjects inside a single datasource with joins between them. It would lead to slower cube build and would consume more resources of the server.

The dimensions should be by themselves (seperate query subjects, one each for every dimension) and facts to be by themselves with the keys inside the fact query subjects. Transformer would associate these based on common field names.

DMRs should never be used as a data source in Transformer. It might give you the correct data but leads to poor practices and much slower cube builds.

[rb338]

@Arsenal: that idea crossed my mind too. I tried it, and it works! However this is a LOT of work :( I'm afraid there is no other solution though. Thanks!

@cognostechie: I've got the printout of that document on my desk, thanks :) I do have seperate query subjects for each dim and each fact table. Why not use a dimensionally modeled database as source in Transformer?

[cognostechie]

Why not use a dimensionally modeled database as source in Transformer?

Because it defeats the purpose of following the proven practices suggested by IBM. Let's presume a DMR has 10 dimensions in Framework Manager. You need only 5 dimensions in Transformer. When you use a DMR as the data source in Transformer, Transformer does NOT use the DMR but rather converts the DMR into a SQL query which invloves all the joins that were originally required to form those 10 dimensions for the DMR. You will be using joins that may not even be required for the model resulting in a slower query.

Secondly, a DMR is an OLAP (even if virtual) which is exactly what you will be doing in Transformer so if you use a DMR then you are re-doing what you already did once.

[barrysaab]

Thanks,cognostechie.But what my model in FM is inherently built on DMR and i use this for my reports,then i guess i left it no choice but to with IQD because even if i take my report as a source it is built on DMR.Could you please clear my doubt.thanks

[Arsenal]

I think the primary point is this - you're already "mimicking" a cube by reporting off a DMR. Why, then, would you want to now use this already "cube mimicked" report to build a cube? That is what cognostechie is saying, I believe.
See, a cube is to be built with basically select statements (and some calculations if needed) i.e. IQD's. A list report would do the same if you had a simple list report with columns and maybe a few calculations. My opinion is that while we can leverage existing FM packages and reports to build cubes, those would be more true in the case of straight up relational packages and reports, not DMR.

If you're going to redo your DMR as a cube then strating from scratch is the best way if you have the time and if you don't, you need to figure out a way to get management to allocate that time. R

[cognostechie]

Thanks,cognostechie.But what my model in FM is inherently built on DMR and i use this for my reports,then i guess i left it no choice but to with IQD because even if i take my report as a source it is built on DMR.Could you please clear my doubt.thanks

You can create another namespace in FM called 'Transformer Layer' and create Query Subjects there for Dimensions and Facts, then publish this namespace as a package and use these Query Subjects in Transformer as a data source. It will be the same concept as using seperate IQDs but you will be following the new Cognos standards (8.4 onwards) and the queries will be much faster. Using a Report as a data source is risky because the report is available on the portal for anybody to change thereby increasing the chances of messing up the cube.

[cognostechie]

By the way, you can create these Query Subjects from already created Business Layer so it will be pretty fast. This way you can also have different filters for the Cube and different calculated columns if required. Ex: The DMR might be getting data for last 10 years but you want to put only 4 yrs data in the cube.

[jive]

Hi Rb338,

Just take a look at that document it could help you.

Cognos 8 Report Studio Creating Multi Query Reports
Look specifically at the end in the HINT section. May be the problem you have could be one of them.

Regards Jacques