Framewoek manager role

[matrixfree]

Hello to all,

All our needed tables are present innour data base as for what is saying our dba.

Do we need to make sure that all links between tables in the database are done by the dba before we start our database layer in FRAMEWORK MANAGER or we don't need those links anyhow because we in the framewoek manager will make all appropriate links ?

I ask this question because we have:
TABLE A
that needs to be linked to TABLE B
But there doesn't seem to exist a foreign key inside TABLE A to be able to link with TABLE BB in our database.

Thank you.

[bdbits]

If you do not have the foreign key present, how do you expect to relate the tables in any way?

If by "links" you mean declared referential integrity, no it is not strictly required to be in the database. But it will be easier to build the model if it is present, as FM can see and add those relationships automatically when you import the tables.

[cognostechie]

Hello to all,

All our needed tables are present innour data base as for what is saying our dba.

Do we need to make sure that all links between tables in the database are done by the dba before we start our database layer in FRAMEWORK MANAGER or we don't need those links anyhow because we in the framewoek manager will make all appropriate links ?

I ask this question because we have:
TABLE A
that needs to be linked to TABLE B
But there doesn't seem to exist a foreign key inside TABLE A to be able to link with TABLE BB in our database.

Thank you.

Which database are you referring to? Is it the database of a transactional system or a Data Warehouse/Data Mart ? If it is a database of the transactional system ,also called an OLTP system, then rest assured you are taking the wrong approach of building a Framework Model with those tables. It will work only with very limited number of tables and easy reports but the objective is to create a Model for most of the tables then you are going to run into the same limitations as every company has run into who has taken this approach.

[matrixfree]

Bdbits,

Here's the definition of our 2 tables:
TABLE A (UNIQUE KEY, PRIMARY KEY, ColumnZ, Other columns)

that needs to be linked to
TABLE B (PrimaryKey, SameColumnNameAsTableAPrimaryKey, other columns)

But there doesn't seem to exist a "foreign key" declared in TABLE A constraints.

The documentation specify a link between those 2 tables but by using the PrimaryKey of TableA and using the column
SameColumnNameAsTableAPrimaryKey

Which is not specified as ForeignKey in the database.
Might be that our DBA hasn't yet finisehd his job as for the project itself will have pretty soon other tables and have been told by our coworker that the project is still on but that for the tables we need for our first reports are ready ?

QUESTION: HOW AM I SUPPOSED TO LINK THOSE 2 TABLES WHILE IN my DATABASE LAYER in FRAMEWOR MANAGER ? I mean, can i take care of that in FrameWork Manager myself ? If so, how ?

Thank you for both your feed-back by the way.

[cognostechie]

Ok, to answer your first concern - Framework Manager does not need a field to be defined as either a Primary Key or a Foreign Key in any table. You can manually define the joins between two tables as follows:

Click on Table A, Ctrl-Click on Table B, right-click, Create, Relationship. It will open another window where you will see both the tables. If it shows a line linking two columns between the tables, click on that line and hit delete button. Click on 'New Link' button and then select the fields on the left and right to join.

[cognostechie]

After you do this, you can check if it joined properly or not. Right-click either Table A or B and select 'Launch Content Explorer'. It will show you the join between them.

[bdbits]

Do not confuse data-based relationships with database-declared referential integrity (RI) constraints. Maybe it will will surprise you to learn that some implementers do not have any declared RI in a data warehouse database whatsoever, often for performance reasons during ETL. The relationships are data-based, and since a data warehouse is generally read-only, the thought is that the database does not need to be protected from rogue transactions. (There is an implicit assumption here that the ETL is sound and will not create 'bad data' in the absence of RI.)

Whether the foreign keys are declared in the database or not is terribly important. As long as they are logically foreign keys in the data itself, then as cognostechie instructed, join them in FM and you will be fine.

[cognostechie]

I agree but I would presume that the folks managing the OLTP system would take care of those RIs. His question was how to create the joins in FM based on what his DBA told him (PrimaryKey of Table A should be joined to SameColumnName in Table B ) so I thought the data in those two fields would be exactly as it should be to make the join work properly.

[matrixfree]

Very good explanations and thank you.

Is it a BEST PRACTICE to join my 2 imported tables as mentioned in the analysis doc (doc doesn't come fromt DBA by the way)  by using a PrimaryKey and a non primary key like that in FrameWork Manager ?

I mean even if it works, are we trying somehow to bypass good practice or not at all and as long as both columns have the same data the it's OK ?

Regards !

[cognostechie]

As long as two tables are joined on fields which have a valid relationship in terms of data then it's ok. The best practices would allow that but it would not allow creating a Framework Model on top of the OLTP system which is what I mentioned in my first reply.

[matrixfree]

OK,

In reference to what you said in previous post:
"
Which database are you referring to? Is it the database of a transactional system or a Data Warehouse/Data Mart ? If it is a database of the transactional system ,also called an OLTP system, then rest assured you are taking the wrong approach of building a Framework Model with those tables.
"
==> Well the those tables are going to be queried from ReportStudio when connected to our first Cognos package having been published from this Frameworkanager model so we can say it's a Day warehouse/Datamart ?

[cognostechie]

No. It depends on which database your Framework Manager is connecting to. If you had a Datawarehouse/Datamart then you woud have had an ETL process which would have read the data from the transactional system , converted into a Star Schema design and populated another database . This another database would then be called a Datawarehouse or a Datamart depending on what kind of data it contains. It seems that you don't have that or atleast you are not aware of it. Is you model connecting to a database used by another system on which you company runs it's business os it connecting to a database solely to be used by Cognos. You can ask your DBA . If he answers that the databse is used by other system/s then you do not have a Datawarehouse/Datamart. I would suggest to take some formal trainings to understand how the Business Intelligence stack should be architected and what are the reasons for such architecture.

[matrixfree]

My co-woeker told me that it seems to be used bay many other tables.

Also when you say:

"same limitations as every company has run into who has taken this approach"

Can you tell me some of those limitations please ?

Thanks to all for your valuable feed-back by the way !