Hi Ian,
Thank you a lot for your contribution to my question. Your answer revealed interesting aspects for me and is opening the path to two completely different topics, I would like to discuss further:
- I do not use FM, but I am asking myself since quite a time, if I should do so. What is your opinion on that: In what situations would FM be helpful? How do I recognize that a problem is better solved in FM? Or is FM not the path to take, if I did not do so until now?
- You say «You should only use SQL tables in certain cases» and you say to use relationships instead. I do both, first creating «improved/enriched» data by SQL Table queries, then linking them using relationships. Only in a few rare cases I am able to use database tables «as-is». This leads to my second question: Am I doing the things right? Did I prepare the data in the right way?
The general issue in my approach to create SQL Tables for almost everything, is maintainability. If I get new ideas, I have to go into many queries to apply changes, since there are groups of queries that are quite similar, but different enough, for not reusing them.
The original question I had, is coming from the «maintainability» issue. I will take a different approach to explain.
When I go to «Manage - Data server connections» I see the list of connections, it looks like this:
| Server connections |
Connections |
Schemas |
Tables |
| DsConn1 |
DbConn1 |
Schema11 |
table1, table2, ... |
| DsConn2 |
DbConn2 |
Schema21 |
table21, table22, .. |
| |
|
Schema22 |
table 31, table 21, ... |
| HCC_WAREHOUSE |
DWH |
HCC_DWH |
DIM_TIME, .. |
When I create a SQL Table in a data module, then my query looks like this:
WITH TIME_VIEW AS (
SELECT
TIME_ID,
_months_between (_first_of_month(current_date), DATE_VALUE) AS MONTHS_AGO
FROM HCC_WAREHOUSE.HCC_DWH..DIM_TIME WHERE DAY=1 AND DATE_VALUE <= current_date
),
In this query I have to mention «HCC_WAREHOUSE» in front of HCC_DWH or the query fails with an error. If I want to change my Data Server Connection e.g. to DsConn1 for what reason ever, I need to edit about 50 SQL queries.
BUT - and this is the second thing that puzzles me - the need to mention the Data Server Connection in the query to specify the table is only necessary on schemas that are added using the «Add more sources» function in the data module. This is NOT true for the first schema, added during data module creation.
I hope I could make more clear where I am stuck. And thank you for the very helpful screenshot - my relationship view is not the only one with a zillion of relationships.
Due to your pointing to the mechanisms behind my questions, my questions are opening a broader field... Here the final summary of my issues:
- In what cases it is inevitable to use FM?
- I think, improving data by using SQL Tables is a good way - but maybe I am doing something completely wrong. But how can I do it better (probably not possible to discuss here)?
- Is there a way how I can create a FROM clause without reference to the Data Server Connection (in the example HCC_WAREHOUSE)?
Regards,
Andreas
------------------------------
Andreas Kühn
Managing Partner
know.ch AG
St. Gallen
+41 71 279 32 22
------------------------------
Original Message:
Sent: Mon October 25, 2021 01:14 PM
From: IAN HENDERSON
Subject: Cognos SQL Table Reference / XQE-MSR-0023
Hi Andreas
If understand correctly, you have 3 schemas in your data module. You want to create relationships between a table in one schema and another table in another schema.
You should drag into the module the tables which you need to use in your module. Then select each table pair between which you want to create a relationship and create the relationship.
You should only use SQL tables in certain cases. In FM you needed to have a data/query layer where there were query subjects with SQL statements in the form of select * from table. You don't need that in a data module.
This workflow is a bit different from what you need to do in FM.
The objective is the same: to create a model where the queries which are generated in reports will plan in a consistent way. This includes identifying and creating the dimensions and facts and creating relationships which will only flow in one way, so that the SQL will always be the same, which is done by using aliases for role-playing.
Here is a diagram of a module where I have dimensions, including two which use the same table but I have set up aliases so that using something from either of them will only reference the tables for the role in which the table is being involved in. I have GO REGION DIM in both my retailers and my employee dimensions. I have set up an alias for one and created a relationship between each instanced of GO REGION DIM and the tables which are also in the relevant dimension. In this case, if I choose country from the employee dimension then the query path will be generated flowing from GO REGION DIM down to the leaf level of the employee dimension (emp employee dim) and because they have been put into a view, the relationships to the facts will go through them. If you read my blog topic about identifiers you can learn a bit about related modelling.
This is conceptually the same as what you want to do in FM but the workflow is slightly different, most notably through the elimination of the query/data layer.
Hope that helps.
------------------------------
IAN HENDERSON
Original Message:
Sent: Mon October 25, 2021 10:34 AM
From: Andreas Kühn
Subject: Cognos SQL Table Reference / XQE-MSR-0023
Hi all,
I am working with IBM Cognos for a short time, and I am having difficulties to understand how to reference my tables when building SQL Table queries in data modules. Let me explain.
I added to a data module three different "Sources" coming from two different connections, representing three different relational databases.
They appear as A, B, C in the data modules "Sources" panel. Databases A and B are part of "Data Server Connection" CONN1 and C is part of CONN2. During data module creation I added CONN1 with databases A+B. Later I added from CONN2 the database C, using "Add more sources" in the data module.
If I create the query SELECT * FROM A..TABLE1, this is validated positively and data is shown.
If I create the query SELECT * FROM C..TABLE2, the validation shows the error "Reason: 'XQE-MSR-0023 'TABLE2' needs to be in the imported schema 'C' in the connection 'CONN1' in the data source 'A'. The issue is, that source 'C' is in fact linked to 'CONN2' and of course part of data source 'C'.
This query works only, if done like this: SELECT * FROM CONN2.C..TABLE2.
The questions I have:
- Is there a way to teach Cognos to use CONN2 and data source C when selecting from data source C?
- Is there a way to omit the connection name in the query, as shown in the first (working) query?
- Alternatively: What else could I have made wrong that leads to the described behaviour?
The issue is - maybe someone can share best practice - that I split the above databases from a single connection to two different servers / connections. For this purpose I created two new data server connections and relinked the data sources. If I have to mention the connection name in the query, I need to edit all queries in my data modules, what I would like to avoid.
------------------------------
Andreas Kühn
Managing Partner
know.ch AG
St. Gallen
+41 71 279 32 22
------------------------------
#CognosAnalyticswithWatson