Hi There,
I've run into an issue with our system that the sales team is often changing product names, and the product name is a pk for much of our code. So, my solution has been to create a translation matrix. The plan is to add it to new reports going forward to update the product name/code with the one from my table with the old and new values.
This worked perfectly when I created my first join. Issue came about when I started working on another report, and joined the translation matrix table to a second table. When I looked in the SQL generated by Cognos, it also included the first table that I had made a join on.
Both the tables are joined with 1.n (base table) to 0.1 (translation matrix table).
I'm not sure why table 1 is being added when I'm joining the translation matrix to table 2. They should be able to function independently ...
In business intelligence / data warehousing, attributes like your product name are called slowly changing dimensions (often SCD). Even if they change often. ;)
So I am uncertain here - is it more like scenario 1 or 2?
1: transmatrix_table <<-- product_name --> product_table <-- product_name -->> fact_table
2: transmatrix_table <-- product_name -->> fact_table <<-- product_name --> product_table
Does this help any?
http://www.kimballgroup.com/2005/03/slowly-changing-dimensions-are-not-always-as-easy-as-1-2-3/
So, basically what we have is. I don't really know the way that you're using to describe the relationship, so I'm just gonna muddle through.
Accounting table 1..N Product Translation Matrix
Product Translation Matrix 1..0 Matrix Accounting table
Sales table 1..N Product Translation Matrix
Product Translation Matrix 1..0 Sales table
So, I think that that comes out
Accounting table <<----Optional---> Matrix Product Translation <---Optional --->> Sales table
The purpose is that I join on the product name (optionally), and so if there is a new name, use that one, else use current product name.
So far, it's worked great, but now that I'm joining more tables to the translation matrix, I'm running into problems. I'm seeing that when I add a column from the translation matrix to the sales table, it does a join on the accounting table as well.
From a database point of view, this doesn't make sense, any idea why it's happening?
Cardinality in Framework Manager tells the query engine how to identify facts versus dimensions and this can have a big impact on the SQL that is ultimately generated. I'm not sure if this is the root of your problem or not, but it might make sense for you to review cardinality as well as multi-fact queries in the user guide to see if it is a factor or not. At the end of the day Cognos is expecting a star schema with central fact tables connected to dimensions that spoke out around it. SQL is going to be generated based on this expectation, to some extent.
Another area to consider is using alias shortcuts of your translation matrix so that each fact table is joined to a separate instance of the matrix instead of having the translation table as some sort of bridge between the two fact tables, which is not your intent.
Stepping back for a minute to look at the big picture of the problem, you ought to consider following data warehouse principles as bdbits suggests. I highly recommend The Data Warehouse Toolkit by Ralph Kimball as well as all the excellent articles available on the Kimball website. If you can move in that direction you'd probably generate surrogate integer keys to identify your products and join to your fact tables based on those keys with an SCD design handling the rest very elegantly.
I am on board with Lynn in that using surrogate keys in a data warehouse almost always simplifies handling SCDs and other sometimes tricky or messy situations. And ideally having a star schema or at worst, a snowflake. Anyway, surprising though it may be, I am still confused. ??? This is probably a limitation of trying to create diagrams with text, which apart from some amazing ASCII art usually does not work very well.
Maybe we can try this... in FM right-click your Product Translation Matrix table and "Launch Context Explorer". Screenshot that and add it as an attachment to a post.
Please forgive how bad the image looks. I had to remove anything not related to this question, I don't want trouble for posting company info.
What you see here is that both the sales and renewals are related to the matrix table.
This is somewhat like a snowflake schema.
I inherited a very convoluted system, which has data flowing through it to multiple other systems. So, changing anything has to be done carefully, especially that there is no documentation.
One of my issues is that the product codes are constantly changing. So, what I did rather than go through all the stored procs that fix everything by hand is that I created a product matrix with the original name and the current name.
The cardinality is optional 1-m to both objects, so it shouldn't be causing any problems.
But when I add the currentName and OriginalName fields to the Sales table, I can see in the SQL that it includes the Renewals table as well.