How to understand data modelling

ashishtiwari — Wed, 25 Nov 2020 16:16:04 GMT

I wud like to understand data modelling in qlickview and how various loads r used

Re: How to understand data modelling

maushmi — Thu, 06 Aug 2020 18:48:57 GMT

A Data Model in QlikView can be said as a combination of fact and dimension and it is part of schema.

When the source data are from tables that have more than one common column, QlikView will end up adding synthetic keys to tables and create a more complicated data model when it translates relational data into associative data.

One can create the data model as per the application requirement.

Commonly Snowflake is used to limit duplicate data which is irrelevant in QlikView's case as it automatically eliminates duplicates by design i.e. if a value is repeated, it only stores once.

In QlikView, Star schema is an ideal choice ,In a star schema, ideally, there is a fact table at the centre containing the key fields that servers as a link to other tables.

Best practice for data modelling in QlikView is all about making your data well-structured and logically feasible to optimize data processing and analysis in QlikView. This can achieve by plotting out a design with specific operations (ETL) you need to perform on your intended data set in order to get your data in a perfect data model.

Also Following the stated Practices an ideal data model can be achieved.

1. Incremental Load using QVD files - An incremental load is just the new data or the updated data that is to add in an already existing data file. For better and faster loading of incremental load, QVD files recommend.

2.Combining Tables with Join and Keep - To combine two tables which may or may not have fields or field values in common, join and Keep prefixes are used in QlikView.

3. Use Mapping as an Alternative to Joining - The Mapping function works much efficiently. Using Mapping keyword, you can map fields from one table into another.

4. Creating a Date Interval from a Single Date

5. Matching Intervals to Discrete Data

6. Working with Cross Tables

Types of LOADS in QlikView

There are various types of load used in QlikView, describing them below:

1.Loading data from the file

This method is normal method of loading data into the QlikView application. Load data from files like Excel, MSDB, CSV, Txt files etc., can be done by creating ODBC and connecting to your database directly.

Ex:

LOAD

FROM (biff, embedded labels, table is Data$);

2. Inline Load

The second method of loading is Inline Load where the user can define their own data and load within QlikView. The Inline data can be defined in the inline Data Wizard . InsertàLoad Dataà Inline Data.

Ex:

LOAD * INLINE [ A B C ];

3. Resident Load

Loading data from already loaded QlikView table is possible using Resident Load. Transformations and Calculations can be performed in the resident load script. Loading an existing field or a succeeding table is possible too.

Ex:

Employees:

Select

Empname,

HireDate,

Salary,

Incentives

From Employee;

Load

Empname,

Month(HireDate),

Salary + Incentives as ‘GrossSalary’

Resident Employees;

4. Incremental Load

Incremental load is used to load only the new or changed records. It will be very helpful where a database is big. This involves loading old data from the QVD and new records from the database and combine into single QVD.

Ex:

SQL SELECT PrimaryKey,

FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# AND ModificationTime < #$(BeginningThisExecTime)#;

Concatenate

LOAD PrimaryKey,

FROM File.QVD;

STORE QV_Table INTO File.QVD;

5. Binary Load

Loading data from one QlikView file is called Binary Loading. The data model of one QVW file is copied from RAM to disk in 0s and 1s for another qvw file. This Binary Load will be more useful when you want to enhance the already built qvw with the same metrices defined.

Ex:

Binary c:order.qvw;

6. ADD Load

Add Load is used to append or concatenate the data from one table to other table. During a partial reload the QlikView table, for which a table name is generated by the add load/add select statement (provided such a table exists), will be appended with the result of the add load/add select statement. Since it would not check for duplicates, it is required us suitable where condition in the script.

Ex:

LOAD

ENO,

ENAME

FROM Employees.csv;

ADD LOAD ENO,

ENAME

FROM Employees1.csv Where Not Exists(ENO);

7. Buffer Load

Buffer load helps to create the QVD files automatically. The created QVD will be maintained as defined in User Preferences Locations.

Ex:

Buffer select * from Employees.csv

8. Preceding Load

This allows you to load a table in one pass, but still define several successive transformations.

eg:

LOAD ..., ReferenceDate, Age( ReferenceDate, BirthDate ) as Age

;

LOAD *, Date( FromDate + IterNo() – 1 ) as ReferenceDate Resident Policies While IterNo() <= ToDate - FromDate + 1

;

Re: How to understand data modelling

Brett_Bleess — Tue, 18 Aug 2020 18:58:03 GMT

In addition to the post below, I would recommend reviewing the following Help link, there is a lot of additional information there to absorb on the topic too:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Loading_data.htm

Regards,
Brett

topic Re: How to understand data modelling in QlikView

How to understand data modelling

Re: How to understand data modelling

Re: How to understand data modelling