Loading to Database; ideas

Post Reply
Paul-TM1
Posts: 39
Joined: Tue Jun 13, 2017 3:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Loading to Database; ideas

Post by Paul-TM1 » Tue Aug 07, 2018 6:23 pm

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.
All Attributes.PNG
If all elements are available in one iteration on the data tab, working would be simple.
All Attributes.PNG (17.57 KiB) Viewed 285 times
One Atribute at a time.PNG
Default
One Atribute at a time.PNG (5.83 KiB) Viewed 285 times

User avatar
macsir
Community Contributor
Posts: 548
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Loading to Database; ideas

Post by macsir » Tue Aug 07, 2018 9:12 pm

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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

lotsaram
MVP
Posts: 3126
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

Post by lotsaram » Wed Aug 08, 2018 6:40 am

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
paulsimon
MVP
Posts: 635
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Loading to Database; ideas

Post by paulsimon » Wed Aug 08, 2018 8:42 am

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

User avatar
tomok
MVP
Posts: 2483
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

Post by tomok » Wed Aug 08, 2018 11:45 am

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Paul-TM1
Posts: 39
Joined: Tue Jun 13, 2017 3:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Loading to Database; ideas

Post by Paul-TM1 » Fri Aug 10, 2018 7:52 pm

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.

Post Reply