Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
Database size is huge ......what should i do ? can i go for join or concatenation ?
which database size is huge?
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.
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)'