Crosstable or not ?

[rulohx87]

Hi,

I have a table with 20 fields. I want to convert these fields to dimension. I thought of a crosstable. But the table contains millions of records and not think it's the best option.

CLAVECCT	BAB	BAE	BCD	BCE	BCM	BCP	BDE	BDF	BDM	BEE	BGR	BIA	BIP	BM	BMR	BPC	BPF	BT	BTE	BTM	BUC
11DK867HJ20	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
11DK867HJ20	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1

I'm thinking;

IF (GetFieldSelections (Catalog) = $ Field, Count (GetFieldSelection (catalog)))

May be..

IF (CATALOG = 'BAB', COUNT ('BAB'), COUNT ('BAB')) <----- text object  (all columns)

...

Any tips?

[MK_QSL]

CrossTable(Data,Value)

Load * From TableName;

[rulohx87]

I try a crosstab. But from a table of 4 million; the result of crossing is a table of 50 million and memory overflows.

[jonathandienst · Accepted solution]

Cross table is the correct way to go. If you cannot increase your server or PC memory to be large enough to handle the full data set, you may need to reduce the number of rows by limiting some range (like dates) or pre-aggregating the data in the source database.

The cross table has more rows but fewer fields and is usually not much larger than the original data. So without crosstabling you will probably run out of memory anyway - perhaps not during the load but almost certainly when working with the model.

[jonas_rezende]

Hi, Raul Saucedo.

Try utilize summarized table, after of made crosstable. Example:

1. CrossTable (Data,Value)

     Load * from Table;

     Store [Table] into [..\Table.qvd] (qvd);

     Drop table Table;

2. Load

    CLAVECCT,

    Count(CATALOG) as CATALOG

    From Table.qvd (qvd)

    Group by

    CLAVECCT;

Item 2 will reduce the data.

I hope this helps!