Using Transformer 10.2 and Modeling - Simple Question

[gosoccer]

Friends, 8)
I have the following two tables in our DB.

TABLE A (ONLY A LISTING OF CITY AND DESC)
CITY CODE
CITY DESC

TABLE B (FACT TABLE)
CLIENT #
ADDRESS
CITY CODE

FM Model has the package established to be used within Transformer. A Relationship is established between TABLE A and TABLE B based on CITY CODE.

Now the datasource will be:
Data Sources:
TABLE A Data Source
TABLE B Data Source

Now, in the Transformer,
DIMENSION:
LEVEL: CLIENT #
LEVEL: ADDRESS
LEVEL:CITY CODE
LEVEL:CITY DESC (FROM 1st Datasource above (TABLE A))

Now, in the report, I could do the following,
This way the Report Author 10.2 (Dim. Hierarchy) can use the dimensions to produce a report such as below:

City Code               City Desc                      Client #
Wash                      Washingon DC                      111111
Balt                          Baltimore MD                       3333333

So, the CITY DESC was generated from TABLE A as a part of the cube generation.

Thank you in advance for your time.

[gosoccer]

Thank you.

[cognostechie]

You are welcome !

[gosoccer]

I DO NOT have an answer to this question yet. I said Thank you so I could add the email notification.
Sorry about that.

[bdbits]

Cubes are all about aggregating measures (numbers) based on dimensional selections, and are best based on a star schema. I do not see any of that here.

You seem to misunderstand dimensions, as your dimension is not really hierarchical. I would recommend doing some reading on dimensional modeling, which is not a Cognos-specific thing.

[gosoccer]

Ok! Everything is set up and working greatly over the last year! Now I have a city desc that I have to present it as a part of the report. I wasn't sure how to handle that when the city desc is on a separate table in our database!
I didn't know if its better to create dimension from a different Datasource  OR just add the field that is available within the same Datasource using the Modify option and add the city desc from a different subject query from the main Datasource!

[bdbits]

If you have a level that has both a code and a description, you can use level attributes to give access to both without creating a new level.

I will assume City Code is an existing level. Open your Transformer model. Add the City Code description to your data source queries (as you said, with the Modify option). Now double-click the level for City Code. Your "Associations" list probably only has the Source role in it. Click the Add button below the list. Look at the dropdown "Association role" to see your options, I would logically choose "Description", why will be clearer shortly. Then enter the column name for your City Code description, or choose the More button and select it from there. Click OK back out to the model and generate your cube, and publish/activate/deploy it to a Cognos server as needed to make it "live".

Now open the cube package in Report Studio. Make sure that at the top of the "Source" panel, you have View Metadata Tree selected. Expand the tree down to the City Code level, and you should also be able to expand the City Code to see the roles, including "Member Description". Drag that out on your report, and it will display the "Description" association role from your model.

Hope that clears it up.

[gosoccer]

 :)That is perfect. Last question,

If in Framework Model I have a Relationship established between the two tables (1-many), the Transformer understands this relationship and use it to build the categories, right?

Thx so much. I'm building it now!!

[bdbits]

If your Transformer data source is an FM package that has this join, yes Transformer will use it.

Glad I could help.

[MFGF]

Glad I could help.

As are we all, Bob! :) Thanks for your helpful contributions - we appreciate it hugely!

MF.

[gosoccer]

Yes. thank you so much for your time.  :-[ I meant to say something sooner.

I tried the approach around 2 Am at morning. I know this approach should work but
because I'm getting blanks for the description, I think I'll check it at the FM Model
level to see if I'm getting blank then.
>:(
Go Cognoise!!!

[bdbits]

gosoccer - When you are in Transformer, you can right-click the query containing your data items and choose "Data Source Viewer". This will pop a window with the Cognos SQL, the Native SQL, and Preview which is your data. This might help you determine why you are getting blanks.

MFGF - reading thanks from a muppet on a Monday morning. What a way to start the week!  ;D

[Michael75]

When you are in Transformer, you can right-click the query containing your data items and choose "Data Source Viewer". This will pop a window with the Cognos SQL, the Native SQL, and Preview which is your data

And just to add to what Bob said, if you find the Preview view a bit limited for your purposes, as I often do, just copy the Native SQL and paste it into your preferred DB tool (Toad or whatever)

[gosoccer]

Excellent. Thx so much! I'll try that!