Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2013-02-04 07:22 AM

Handling Big Fact table.

performance tuning...

I have a big, very big Fact table ~ billion lines, filled with highly granular data.

no dimention tables. just one big fact with ~20 fields.

this data is used in one chart object and, obviously... it chokes.

how would you recommend modifying the ERD, or perfomance tune the object?

it's basically a fact with a key of period-subscriber-promotion containing event data.

it was not meant for QV, but will splitting that giant table in 3 dims and one event based fact help performace?

after all... there will still be ~billion events.

thanks.

2,412 Views
0 Likes
3 Replies
swuehl
MVP
MVP
‎2013-02-04 08:03 AM

It's hard to give any detailed advice without knowing your data model and objects in detail, too, but here is a link to a thread with some general recommendations:

http://community.qlik.com/thread/18473?start=0&tstart=0

935 Views
0 Likes
Not applicable
‎2013-02-04 09:47 AM
Author

In response to swuehl

yea... I'm talking about 1 BILLION lines... not 30 million...
need something more powerful.

we are going to implement Hadoop soon as we are trasitioning to big data, but I have no Idea what kind of QV data models are equipped with handling this much data.

the suggestions made in your refferal are good practive for small-medium data sets.

on the hardware side we have servers running 24+ cpu's and 256GB+ ram...

935 Views
0 Likes
hic
Former Employee
Former Employee
‎2013-02-04 10:28 AM

In response to

We have run tests with this amount of data and although we have shown that it is theoretically possible, you need to be aware of some things that can invalidate the theory...

  • Reduce the number of fields: 20 fields is a lot. Can you cut away some of them? Splitting into three dimensional tables and one fact table could potentially reduce the number of fields in the fact table. So, yes, you should try that.
  • Reduce the number of distinct values in a field: Can you cut down on number of distinct values? E.g. if you have a timestamp, you may be better off splitting it into a date field (low number of discrete values) and a time field where you round to nearest 15 seconds (also fairly low number of discrete values).
  • Partition data/Reduce the number of records: Do you really need all data in one qvw? Or can you solve the problem having e.g. one file per day?
  • Aggregate data/Reduce the number of records: Do you really need the granularity?

ALso

Read http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/20/symbol-tables-and-bit-stuffed-pointers to better understand some of the QlikView internals.

Load 5% of the data and see how much memory QlikView uses and what the response times are. Extrapolate to get a feeling for what you will get/ what you'll need when you load all data.

HIC

935 Views