Doubts with a data model

[#1]

Hello, I have a few doubts about how I must raise a data model.

The point is that I have some tables that contain a lot of registers for each day with several years of history. We will have in qlikview a date selector which will always have a selected date value, so this selector will apply a filter over all these tables. The problem is that these tables, with different primary key, are joined between them on different fields apart of this date field (are referenced each other through different fields apart of this date field).

I thought two different solutions:

- Concatenate key fields: To avoid creating syn tables we can concatenate fields and then reference two tables each other with only a field. By contrast only one or two tables could contain date selector's field or qlikview could produce circular reference but for filtering by that date field, we should read many heavy tables to arrive the one which contains the date field.

- Not concatenate: Even trying organize tables so that qlikview generate less as possible $Syn fields (by using concatenate command, etc.) qlikview generate five of them.

I opted for second solution, but before start developing I would like hear from someone experienced in a similar case.

The picture added is the data model exemple resulting from second solution development.

Thank you very much.

[pablolabbe]

Your problem is very common when joining several transactional tables to build a one single model.

Can you explain a bit more what means each table ? Which one have transactional data and dimensions for filtering.

Pablo Labbe

Qlikview Senior Consultant

www.visiongi.com.br

[#3]

Hi Pablo,

Tables whose name contains the word "_diario" also contain the date field (FX_Valor). These tables contain transactional data which changes every day. But this data must be filtered by range filters. For example, user can need data from "Box_Diario" table which joins to "Cartera Diaria" table's records that accomplishes"Amount" field is between 1.000 and 2.000 €

The rest of tables (Instrumentos, Boxes y Carteras) are tables about a concept doesn´t change along time. These three tables join with parameter tables who are not in picture.

Fields you can see in picture which name contain "Resto_Campos_" are not really a field but a group of fields. The meaning of "Resto_Campos_" is "Rest_Of_Fields_" but these fields are not relevants for the data model.

Thank you very much.

[pablolabbe]

Pablo,

  You can concatenate all tables ended with "_diario"  in one single table:

  Tabla_Datos:

  LOAD

  IdCartera2, IdCartera, FX_Valor, IdBox, FX_Valor&'|'&IdBox as FK_Box_Por_TB,  Resto_Campo_

  RESIDENT Cartera_Diario;

  CONCATENATE

  LOAD IdCartera, FX_Valor, IdInstYHered,  FX_Valor&'|'& IdInstYHered as FK_Pricing, Resto_Campos_

  RESIDENT Posicion_y_LK_Diario;

  CONCATENATE

  LOAD FX_Valor, IdBox, IdBox2, FX_Valor&'|'&IdBox as FK_Box_Por_TB,  Resto_Campos_

  RESIDENT Box_Diario;

   Then load the remaining tables creating a concatenated key for Pricing, Box_Por_TB to avoid sintetic fields.

[#5]

Hi Pablo,

I think I can't use more concatenate() command. It is because what I wrote about user want to filter by transactional data and see data from records joined on another transactional table.

I think concatenate() will add records but they will be not joined with records I need.

I can't use neither left join clause because some records will be duplicated.

I think the model data can't be simpler, althought it's a good idea we study about it.

So, bearing in mind tables contain millons of records, we only will need a few of thousands of them simultaneously (perhaps hundreds), and they are a lot of tables whose will be always filtered by date, and the primary key of these tables is not the same between them, the doubt  is about how we penalize less as possible program performance:

- With a star data model, althought the star core is the union of some Syn tables.

- Concatenating fields for building one primary key field and read many heavy tables to arrive the one table which contains the date field.

Thank you very much.

[#6]

Hello.

Can someone help me about that?

In short: With a lot of transactional data, with new records every day, and many years data. With the same date field in each table, and always a day selected:

- Concatenate fields for building one only primary key field for joining two tables. We don't have Syn tables but  we must read many heavy tables to arrive the one table which contains the date field.

- Not Concatenate fields. A Syn table is created which is all Syn fields union. That table is the center of a star data model. All tables are directly filtered by date field.

Thank you.