topic Re: Best Practices to Concatenate Multiple Fact Tables? in QlikView

Best Practices to Concatenate Multiple Fact Tables?

dmohanty — Tue, 20 Jan 2015 05:46:13 GMT

Hi All,

I have a scenario where I am calculating 7 Metrics (KPIs) and are stored in 7 Tables/QVDs (Fact tables). My target is to Concatenate all the tables into a Single table to form a Star Schema (I already have my dimension tables).

I want to know in a cleared way, what would be the BEST PRACTICES/ THINGS TO KEEP IN MIND, while concatenating multiple Fact Tables into a single one. (I already have the documents/links shared in community, but still want to know more in my case.) Please help.

For example, I have 3 Metrics as of now in 3 Tables (Fact A, Fact B, Fact C).

Fact A is at Month and Supplier ID level.

Fact B is at Month and Supplier ID level.

Fact C is at Month, Supplier ID an Plant ID level.

(Similarly the other Facts are at some level, which I need to merge.).

In the example scenario, what could be the Best Way to concatenate. What are the things to be taken care of?

N.B: The Dimension Tables I have are: DATE_DIM, SUPPLIER_DIM, PLANT_DIM (and few others).

Re: Best Practices to Concatenate Multiple Fact Tables?

marcus_sommer — Tue, 20 Jan 2015 11:07:44 GMT

If you have deceided to concatenate the fact-tables it's really so simple like here:

FactTable:

Load * From a

concatenate (FactTable)

Load * From b

concatenate (FactTable)

....

But it could be that other solutions like joining or mapping maybe combined with concatenating led to better (in which way: load-time, ram-ressources, gui-calculations ?) results. Concatenating is the easiest way and aren't there really performance issues it should be enough. If not you will need test some different approaches and examine the log- and mem-files to optimize your app regarding to your likeliest bottle-neck.

- Marcus

Re: Best Practices to Concatenate Multiple Fact Tables?

dmohanty — Tue, 20 Jan 2015 12:11:08 GMT

Thank you Marcus for your suggestions.

Looking into the scenario in my case (described above), what could be the possible way to do? I meant do I need to take the Key Fields into consideration? If YES, could you please let me know HOW?

The problem is that, when I am loading the Individual Tables at a time separately with dimensions, I am getting the proper results. Once I am doing a simple concatenate, the association does not happen properly.

For example, For Fact A and Fact B (if loaded separately; one at a time), I am able to filter the metrics by Month and Supplier ID

But once I load both Fact and Fact B (concatenated), the association fails and there is no Month association (or some other dimension association) with the metrics of Fact A.

Could you please help more?

Re: Best Practices to Concatenate Multiple Fact Tables?

engishfaque — Tue, 20 Jan 2015 12:28:12 GMT

Dear DMohanty,

I think you are going to concatenate multiple fact tables.

Kind regards,

Ishfaque Ahmed

Re: Best Practices to Concatenate Multiple Fact Tables?

marcus_sommer — Tue, 20 Jan 2015 12:29:36 GMT

The key-fields from the fact-tables need to be absolutely part from the concatenation and then it shouldn't be issues with associations. Maybe this is helpful in your case:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

http://community.qlik.com/groups/qvscriptor-from-schema-to-script/blog/2011/11/11/understanding-join-keep-and-concatenate

http://community.qlik.com/blogs/qlikviewdesignblog/2014/01/14/merging-data

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

- Marcus

Re: Best Practices to Concatenate Multiple Fact Tables?

giakoum — Tue, 20 Jan 2015 12:31:44 GMT

This Ahmed is really bad advise

Re: Best Practices to Concatenate Multiple Fact Tables?

dmohanty — Tue, 20 Jan 2015 15:31:27 GMT

Hi Ahmed,

What does your reply implies?