Page 1 of 1

Loading to Database; ideas

Posted: Tue Aug 07, 2018 6:23 pm
by Paul-TM1
Hi All,
I am trying to write Project attributes to Database and have a question. I am looking for ideas on loading multiple attributes in one iteration of loop.
The cube is 2 dimensional (Projects and Attributes) and giving intersection values. Now, when I create a view, it gives one intersection point in each go. Instead, if all the attribute elements for a project loaded in a one go, I will have to make so many number of DB calls. Any ideas? Please let me know if there are questions.
If all elements are available in one iteration on the data tab, working would be simple.
If all elements are available in one iteration on the data tab, working would be simple.
All Attributes.PNG (17.57 KiB) Viewed 3646 times
Default
Default
One Atribute at a time.PNG (5.83 KiB) Viewed 3646 times

Re: Loading to Database; ideas

Posted: Tue Aug 07, 2018 9:12 pm
by macsir
So, if not that way, the simplest way to do that is to use nested loop. The inner loop for all attributes and outer loop for all projects, if you know what I mean.

Re: Loading to Database; ideas

Posted: Wed Aug 08, 2018 6:40 am
by lotsaram
Paul-TM1 wrote: Tue Aug 07, 2018 6:23 pm if all the attribute elements for a project loaded in a one go, I will have to make so many number of DB calls. Any ideas? Please let me know if there are questions.
If the data source is a view then TI sees for data processing it as if all dimensions are nested on rows and processing of views is always one-cell-at-a-time. Therefore I don't get the point you are making.

An alternative would be to set the processing view with only one attribute and then on the data tab loop through the }ElementAttributes dimension. Or as already suggested outer loop of dimension elements, inner loop of attributes. If you want to include blank attribute values then this is probably faster anyway since the loops will be very fast and it avoids any overhead of creating and destroying the view.

Re: Loading to Database; ideas

Posted: Wed Aug 08, 2018 8:42 am
by paulsimon
Hi

By DB calls do you mean that you are writing to a SQL database and you are concerned about the number of INSERTS?

As others have said, read the dimension elements and get the attributes in a loop so you have all attributes for the element to be inserted as one row.

However, each ODBCOUTPUT will still be a transaction in the SQL.

The way around this is to concatenate the VALUES part of the INSERT to make a big string and only run the ODBCOUTPUT after every x number of rows, and in the Epilog to get the final set of rows. I believe that the max size of a string is still 32k so set the count for number of rows accordingly.

Regards

Paul Simon

Re: Loading to Database; ideas

Posted: Wed Aug 08, 2018 11:45 am
by tomok
Another idea would be to use a subset of the dimension as the datasource and then do an ATTRS or ATTRN to get the values and write one SQL statement to insert all the values for each Project at one time. I wouldn't use a cube view with just one attribute because if the attribute is empty and you are using zero suppression on the view you would miss all the other potential attribute values. I also wouldn't loop because it doesn't give you the same kind of control over which records are in the source like a subset does.

Re: Loading to Database; ideas

Posted: Fri Aug 10, 2018 7:52 pm
by Paul-TM1
Thanks Tomok, Paul Simon, Lotsaram, Macsir.
I took the idea of subset and got the attrs of other members and finished. Just the way I wanted.

Thanks again.

Paul.