How to create a dim table into the data module

[Eugene]

Dear all,

I have the following case.
I have a data set as source into the data module and I want from the column msisdn of this data set to create a table with distinct values of the msisdn .
That is I want to create a dimension table from the data set.

How can I do that ?   I cannt find a way to tell the data module to extract only the unique values of msisdn.

Thanks in advance.

[bus_pass_man]

If I correctly understand what you are attempting to do, you want to create query subjects in the module from a data set, with the ultimate objective of having something like a star schema. Is that a correct understanding?

You don't need to do this.

You could set column dependency on the data set.  That would allow you to define the dimensions in the data set and what columns are at what level of dimension detail as well as what facts are at what level.

If you want to use the data set in conjunction with other objects in the module, you would use column dependency just as you would use it to define levels in any other dimension table.

[Eugene]

Dear bus_pass_man,

you correctly understand. I am trying a way to do a  query subject in the data module. In framework manager I know how to make a query subject.


In the deta module at 11.1.7 , inside the join pop-up window there is not the many--to--many relationshhip.
So in order to make this happen, I must create 2 relationships 1--to--many  between 3 tables. Lets say TableA, TableB, dimensionTable.

TableA   many--to--1  dimensionTable  1--to--many  TableB.

Where TableA, tableB  are data sets and I use them as sources of the datamodule.
So I am trying a way to create the dimensionTable from the unique values of the msisdn-column of the TableA or TableB.

I am not very familliar with column dependencies functionality , can you give me more info, some steps on how to do that ?


Thank in advance.

[bus_pass_man]

Here's a bunch of stuff I found just by typing fairly easy to guess search terms.

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/t_ca_mdlg_col_dpndcy.html

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/c_ca_mdlg_col_dpndcy.html

https://www.ibm.com/support/pages/multi-fact-multi-grain-and-relative-dates-defining-column-dependencies-prevent-double-counting-cognos-analytics-proven-practice-capp-document

https://pmsquare.com/analytics-blog/2020/2/2/cognos-power-of-data-module-column-dependencies

https://community.ibm.com/community/user/businessanalytics/blogs/ian-henderson1/2020/04/30/migration-of-cognos-query-engine-primary-key-query

https://www.atlanticcognosanalytics.com/post/column-dependencies-in-data-modules

https://www.atlanticcognosanalytics.com/post/adding-column-dependencies-to-tables-with-pre-aggregated-data

https://myemail.constantcontact.com/Data-Modules-on-Overdrive--Analytics-in-the-Clouds--Pizza-Showdown--and-More.html?soid=1117133329037&aid=4v3VhC6H24Q

http://ibmblueview.com/blog/page/2/

http://ibmblueview.com/framework-manager-vs-data-modules/

https://www.sempreanalytics.com/news/framework-manager-versus-data-modules/






You will need to make a case for why you want to have a many to many relationship.  I'd really need to have rather a lot more information and a lot of evidence to indicate that whatever situation you have requires such a thing. 

[Eugene]

So there isn't any way to create a custom table into the data module form the TableA or TableB as  a query subject like this
dimensionTable:
select distinct msisdn from TableΑ. ?
I know that I can create a copy, view, joined, except, union or intersect between tables.


Thanks in advance.

[bus_pass_man]

You are fixated on this approach and it is not apparent that you have read and thought about what I have said.

Bringing into play, for some unfathomable reason, a many to many relationship just compounds it.

One problem seems to be that you are not able to describe your modelling intentions.

If you have a big rectangle of data - an uploaded file or data set - and you want to normalize it, you use column dependency. Period.