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.
Loading to Database; ideas
- macsir
- MVP
- Posts: 782
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Loading to Database; ideas
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.
-
- MVP
- Posts: 3652
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Loading to Database; ideas
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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: Loading to Database; ideas
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
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
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Loading to Database; ideas
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.
-
- Posts: 124
- Joined: Tue Jun 13, 2017 3:20 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: Loading to Database; ideas
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.
I took the idea of subset and got the attrs of other members and finished. Just the way I wanted.
Thanks again.
Paul.