Author Topic: How to create a dim table into the data module  (Read 503 times)

Online Eugene

  • Full Member
  • ***
  • Join Date: May 2019
  • Posts: 24
  • Forum Citizenship: +0/-0
How to create a dim table into the data module
« on: 28 Sep 2020 04:20:15 am »
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.

Offline bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 435
  • Forum Citizenship: +43/-0
Re: How to create a dim table into the data module
« Reply #1 on: 28 Sep 2020 07:34:49 am »
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.

Online Eugene

  • Full Member
  • ***
  • Join Date: May 2019
  • Posts: 24
  • Forum Citizenship: +0/-0
Re: How to create a dim table into the data module
« Reply #2 on: 28 Sep 2020 10:40:54 am »
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.



Offline bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 435
  • Forum Citizenship: +43/-0
Re: How to create a dim table into the data module
« Reply #3 on: 28 Sep 2020 11:11:43 am »

Online Eugene

  • Full Member
  • ***
  • Join Date: May 2019
  • Posts: 24
  • Forum Citizenship: +0/-0
Re: How to create a dim table into the data module
« Reply #4 on: 28 Sep 2020 11:52:08 am »
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.

Offline bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 435
  • Forum Citizenship: +43/-0
Re: How to create a dim table into the data module
« Reply #5 on: 30 Sep 2020 06:54:18 pm »
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.