Generic Load from QVD

[w0jt4z]

Hi,
I need Your help, Guys. I tried to use Generic Load to reversal my table with Attributes. Unfortunately, it's about 80 000 000 records, and system  memory is full. I want to store every table to QVD in Generic Load, and then join every table from qvd, not from memory. My script looks now like that:

Attributes:
Generic LOAD
%OrderBookingKey,
Code,
Value
Resident Attributes_tmp;

Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='Attributes' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo

CombinedGenericTable:
Load distinct %OrderBookingKey Resident Attributes_tmp;

Drop Table Attributes_tmp;

For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName

Store CombinedGenericTable into [lib://Transformed (win-f2om7p4maoo_followup)/Attributes_transformed.qvd] (qvd);

I think I should store the "little tables" to QVD before creating CombinedGenericTable, but I don't know how can I load all of them to my Generic table.

Thanks in advance for Your help!

[w0jt4z]

I tried to do it myself, but still memory is full.
My script now looks like:

Attributes_tmp:
LOAD
%OrderBookingKey,
 Code,
Label,
Value,
FROM [qvd]
(qvd);

Attributes:
Generic LOAD
%OrderBookingKey,
Code,
Value
Resident Attributes_tmp where Not IsNull(Value);

Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='Attributes' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
store [$(vTableName)] into [lib://QVD(win)/Attributes\*.qvd/$(vTableName).qvd] (qvd);
Drop Table [$(vTableName)];
End If

Next vTableNo

CombinedGenericTable:
Load distinct %OrderBookingKey Resident Attributes_tmp;
Store CombinedGenericTable into [lib:qvd] (qvd);

Drop Table Attributes_tmp;

for each file in filelist('lib://QVD(win)/Attributes\*.qvd')

Left Join (CombinedGenericTable) Load * from [$(file)](qvd);

next file

Store CombinedGenericTable into [lib://QVD(win)/Attributes/Attributes_transformed.qvd] (qvd);
exit Script;

Anyone has any idea how to optimize this script so that it does not overflow system memory?

[lorenzoconforti]

Out of curiosity, how many distinct Label do you have? There shouldn't be an upper limit on the number you can have but of course depends on the amount of RAM

If you debug, at what point the system goes out of memory?

[w0jt4z]

It's about 150 labels. On the server is 48 GB RAM, I think it should be enough, but it doesn't...

I didn't tried it run in debug mode, but it is happening during join the one of table in turn.

[lorenzoconforti]

So, all QVDs are generated correctly?

What if you create another QVF and try to join just the first two tables manually? Does it still give an error?

[w0jt4z]

Yes, I'm sure they are. It is just one, maybe important, thing. In QVD I have null and empty string values, but I thought it shouldn't disturb. I did something like that in another app, but i had less tables and it was more or less correctly.

I tried join now 3 labels and everything passed.

[lorenzoconforti]

I had a look at your script; there are a couple of thing I think they are not correct. Not sure if they are contributing to the problem but better fix them anyway. See the comments in your script below

Attributes_tmp:
LOAD
%OrderBookingKey,
 Code,
Label,
Value, //There is an extra comma here
FROM [qvd]
(qvd);

Attributes:
Generic LOAD
%OrderBookingKey,
Code,
Value
Resident Attributes_tmp where Not IsNull(Value);

Set vListOfTables = ;
For vTableNo = 0 to NoOfTables() //it should be to NoOfTables() -1
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='Attributes' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
store [$(vTableName)] into [lib://QVD(win)/Attributes\*.qvd/$(vTableName).qvd] (qvd);
Drop Table [$(vTableName)]; //you shouldn't drop the table like this because you are reducing at the same time the number of tables thus skipping some as your vTableNo variable is now refereing a different number of tables
End If

Next vTableNo

CombinedGenericTable:
Load distinct %OrderBookingKey Resident Attributes_tmp;
Store CombinedGenericTable into [lib:qvd] (qvd);

Drop Table Attributes_tmp;


for each file in filelist('lib://QVD(win)/Attributes\*.qvd')

Left Join (CombinedGenericTable) Load * from [$(file)](qvd);

next file


Store CombinedGenericTable into [lib://QVD(win)/Attributes/Attributes_transformed.qvd] (qvd);
exit Script;

[marcus_sommer]

Are you really sure that you need to merge all attributes within a single table? In regard to The-Generic-Load it's not recommended unless you have a very specific reason.

Beside this I could imagine that using mappings instead of joins may be faster and less RAM consuming. This means within the first loop you don't create the tablename-string else n mapping-tables and maybe also an appropriate applymap-string and afterwards you adds the applymap's to your distinct key-load.

- Marcus

[w0jt4z]

@lorenzoconfortiThank You for Your comments. I don't think it directly affects my problem, but it is certainly next step to optimize my script. I'll try use it and I'll see what happen.

@marcus_sommerI have no other idea for my case.
I have big one table with all attributes and key to the rest of my tables and second table as a Dictionary.

So it looks like:
Attributes:
$LinesKey, $KeyAttributeId, Value.

AttributePrototype (Dictionary):
$KeyAttributeId, AttributeName

Lines (rest of  my data):
$LinesKey, Others

I need to show all of these data in one row. Looks like:
LinesData, AttributeId1, AttributeId2
Others, ValueAttribute, ValueAttribute
etc.
So I don't want to use "if" for all of Columns with AttributeId like "If(AttributeId = 1, Value), if(AttributeId = 2, Value)

So it is the reason for reverse my table, where each AttributeId is separated Column and has a value.

My knowledge on this is too small, if exist another solution I'll be grateful for any hint.

[marcus_sommer]

I would such big crosstable only create if there is really no other way to calculate/display/select the needed views - means I would try at first to solve the task with a more "classical" datamodel. In your case you may just map the attributename to your fact-table or simply associate both tables. Also I would look if I could group all the attributes into some logically main- and sub-groups. And then creating a few table-charts in the UI and see if all / the most of the requirements could be resolved. If anything is missing or isn't working like expected it might be added/changed with some extra-steps.

- Marcus