Qlik Community

QlikView Documents

Documents for QlikView related information.

MASTERTABLE (LINK TABLE or Concatenated FACT Table)

nick_scott
New Contributor III

MASTERTABLE (LINK TABLE or Concatenated FACT Table)

Automated Link Table

Automating parts of the Script can shorten the development cycle and add to consistency across applications.  The use of a Link Table works well with a data mart / enterprise data warehouse.  Automating the creation of the Link Table can be used to simplify the application data model build a few subroutines.

Data Model - Dimensional Model

A well-structured data model provides many advantages in QlikView application development, including extensibility, collaboration and governance.  A dimensional data model approach, with highly provisioned FACT & DIMENSION tables as promulgated by RALPH KIMBALL et al. provides an ideal data structure for most QlikView reporting / analysis applications.  This is a basic requirement for building a link table.

Link v. Concatenate Fact table

The choice between using a LINK TABLE or CONCATENATED FACT table will be influenced as much by the number of records in the data model as by the developers choice between using set analysis or flags in the chart expressions.

There is extensive information on application optimization, see: Wiley: QlikView Your Business, An expert guide to Business Discovery with QlikView and Qlik Sense and Packt: Mastering QlikView.

Automated Link Table

The automatic build of a Link Table need not be complicated.  It must, however, be predicated on some consistency in table / field naming (adopting Naming Conventions).  There are a number of  QlikCommunity applications which demonstrate the use of script to create a LinkTable.

See also:

Rob Wunderlich / QlikView Components: https://github.com/RobWunderlich/Qlikview-Components

QlikTips:  Rules for creating a Key/Link Table in QlikView: http://www.qliktips.com/2011/05/rules-for-creating-keylink-table-in.html

Automated Link Table Example

This is an illustration, building on that of Lars Christensen, (exSitec, l_konsult@hotmail.com), which demonstrates the LINK TABLE and CONCATENATED FACT Table approaches on a simple data set.

In Technical Content, the approach uses core QlikView Table Functions and Inter Record Functions to review the tables and fields.

Please post your comments,

Explore & Enjoy,

Nick Scott.

github.png

Discover more on GitHub: GitHub - nicholasscott72/Qlik-DimensionalModelling: A simple development methodology for QlikView ap...

Attachments
Comments
nick_scott
New Contributor III

It seems to be such a fundamental point in Qlikview development, but poorly addressed on the QlikCommunity: do you CONCATENATE or LINK.

Working with a complex data set requires a rigorous approach to primary & foreign keys and field naming.  This application shows that by following a "KIMBALL" dimensional modelling methodlogy either the LINKTABLE or CONCATENATED FACT table approach can be taken.

Ultimately, the choice seems to come down to what is easier for the develper to create & administer.  Using a script file to create the "mastertable" enables the choice to be made and performance difference to be evaluated.

What is certain is that aiming to create highly provisioned fact and dimension tables, following a "dimensional modelling" methodology, provides significant development advantages.

lornafnb
Valued Contributor

amazing piece of code. Thanks to all.

Partner
Partner

very useful content & document. Thanks for sharing

0 Likes
gilbertomedeiro
New Contributor III

Congratulations Nicholas!

Thanks for this explanation. Articles like that, at least to me, constitute a rich learning experience and fortify the community.

0 Likes
lornafnb
Valued Contributor

Thank you so much

0 Likes
santiago_respane
Valued Contributor

Excellent! Thanks a lot!

Will try to apply in my solutions!

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-10-27 06:49 PM
Updated by: