topic Re: Database size in QlikView

Database size

qlikview979 — Thu, 23 Apr 2015 09:13:38 GMT

Hi experts,

Database size is huge ......what should i do ? can i go for join or concatenation ?

Re: Database size

ali_hijazi — Thu, 23 Apr 2015 09:16:10 GMT

which database size is huge?

Re: Database size

jagan — Thu, 23 Apr 2015 09:22:32 GMT

Hi,

I think the database size is not a problem, even if you 100s of tables you are not going to use all the tables for the Dashboard.

Follow this steps

Data :
Load only the tables which are required for your analysis.

Reduce the data as much as possible, comment/drop unused fields in the Dashboard, so that the size of the file is reduced.

Reduce the number of rows, suppose if you dashboard is having 20 years of data, check with users whether they really going to use all the data for analysis, if not limit to last 5 years data.

If you are not using time part in Dashboard for analysis remove the time from date fields.

If you are having floating point numbers and if you are ignoring that then convert to integer, this will reduce the size.

Expressions:

Don't display all the records in the Straight/Pivot/Table box objects, restrict user to select some filters.

Use Set analysis in the expressions.

Don't use calculated dimensions.

Bring flags for calculation wherever possible.

Do all possible calculations in the scripting part, so that the charts loads faster.

Don't display all the charts in a single sheet, split it into multiple sheets or show/hide using buttons.

Hardware:

Since you are using 3GB Qlikview file, your system hardware should also be upto the mark, RAM and processors should be more for this. If you are having very less configuration upgrade it. Qlikview performs better if you have better hardware especially RAM.

Hope this helps you.

Regards,

Jagan.

Re: Database size

Thu, 23 Apr 2015 09:43:37 GMT

Yes, I think the same as "jagan Mohan". You must load the data in the qvd tabels for each Dimension. And the Dimensions connect about similar fields. emp_code and c_code to the same fieldnames in bookings.

Example:

sub Selects

costs:
(pk_tenant & '_' & code) AS cotype_id, // code muss später gegen pk_id ersetzt werden (s. Buchungen)
code AS cotype_code,
text(code) as c_code,
description AS cotype_desc,
(text(code) & ' ' & description) AS Kostenart,
factor AS cotype_factor;
SQL SELECT *
FROM dim_costtype;

workers:
pk_tenant & '_' & pk_id) AS emp_row_id,
code AS emp_code,
description AS emp_desc,
(text(code) & ' ' & description) AS Mitarbeiter,
searchname AS emp_search;
SQL SELECT *
FROM dim_employee;

end sub

sub Daten

SET vFilterDate = null;
call Datumkonvertieren (vDBStartDate, vFilterDate);

// Buchungen_Fibu:
bookings:
'Fibu-Buchungen' as Datenquelle,
trim(pk_tenant) AS tenant_id,
1 AS fb_count, code
autonumber(text(pk_tenant) & '_' & text(account_id), 'fb_account_id') AS fb_account_row_id,
(trim(pk_tenant) & '_' & costcenter_id) AS costcenter_row_id,
date(booking_date, 'DD.MM.YYYY') AS Datum,
date(from_date) AS fb_doc_date,
date(valuta_date) AS fb_value_date,
date(booking_date) AS fb_booking_date,
year(booking_date) AS fb_booking_year,
month(booking_date) AS fb_booking_month,
code AS emp_code, description AS fb_desc,
voucher_int AS fb_doc_no_intern, voucher_ext AS fb_doc_no_extern,
accountnummer AS c_code,
debit_credit AS fb_debit_credit,
if (debit_credit = 'S', amount, amount * -1) AS fb_amount_foreign,
if (debit_credit = 'S', amount_domestic, amount_domestic * -1) AS fb_amount_domestic,
(pk_tenant & '_' & currency_id) AS currency_row_id;
SQL SELECT * FROM facts_financebooking
WHERE booking_date >= $(vFilterDate) AND
costcenter_id <> '(0,0,0)'