TM1 Forum

Hi Guys,

I am wanting to add a drill which basically dynamically creates the SQL string based on the SQL statements stored against the element as a text attribute.

For example in the "Account" dimension I would store the string "account=4001" where the element = 4001.

Now in the drill TI prolog it takes reads the SQL string attribute of each element in each dimension to build the appropriate SQL string.

pAccount = ATTRS('Account',pAccount,'Drill SQL Movex');
pDepartment = ATTRS('Department',pDepartment,'Drill SQL Movex');
pLocation = ATTRS('Location',pLocation,'Drill SQL Movex');
pGBU = ATTRS('GBU',pGBU,'Drill SQL Movex');
pSalesOffice = ATTRS('Sales Office',pSalesOffice,'Drill SQL Movex');
pCustomer = ATTRS('Customer',pCustomer,'Drill SQL Movex');
pProject = ATTRS('Project',pProject,'Drill SQL Movex');

The following variables are then substituted into the SQL string:

SELECT
Company,
AccountingDate,
Account,
CostCentre,
ProfitCentre,
Customer,
Project,
FunctionalCurrencyAmount,
JournalNum,
Desc1
FROM GeneralLedger
where ?pCompany?
and ?pYear?
and ?pPeriod?
and ?pAccount?
and ?pDepartment?
and ?pLocation?
and ?pGBU?
and ?pSalesOffice?
and ?pCustomer?
and ?pProject?


However some of these strings go beyond the 256 character limit.

Therefore my question is is there away I can store the SQL strings within text files and assign values to my turbo integrator variables directly from files. The files could simply be named identical to the element name. Thefore I could get around the 256 character limit.

Any help would be highly appreciated.. Please let me know if my question is not clear and I will try and explain it in more detail and use some graphics.

Thanks guys!

Hi,

You can write the query (with the '?parameter?' variables) you need on the datasource tab of the drill-through query. This field is not limited to 256 characters. The ATTRS lookups can be performed on the Prolog and will be carried out before the actual query is send to the database for processing.

Michel

Hi Michel,

Thanks for your reply.. In your solution, where would the SQL string for each element get stored? I can't save it in the element attribute as this has a limit of 256 characters.

Maybe I have misunderstood your response? But where would you store the SQL strings.

For the Natural Account dimension has not only code and name attributes, but also contains an attribute called "Drill SQL" which contains the appropriate portion of the WHERE clause for a particular element.

For example account 4001 would have have "NaturalAccount=1000" and this would be appended to the dynamically generated SQL statement in datasource tab via the prolog.

However with consolidation accounts such as "Total Assets" this string would become large and require more than 256 characters, for example Total Assets would equal "NaturalAccount IN ('4001', '4002' ................................. , '9990')".

Therefore where else can I store these, and one thought I had was the possibility of storing them in text files and having the prolog read from the appropriate file when requested instead of reading from the cube "ATTRS('Account',pAccount,'Drill SQL Movex');"?

Hi

After re-reading your initial posting and your second post I understand that the individual attribute string parts can be longer than 256 characters, mainly because of the IN construct when selecting on consolidated TM1 elements.

The way I usually solve drill-through from consolidated elements (where the consolidation is defined in TM1) is by creating dimension tables with parent-child relations in the relational database, which are joined with the main table. The element selection is done through the dimension table.

F.i., if your Account dimension looks like this:

Total Balance
Total Assets
4001
4002
4009
Total Liabilities
4501
4502
4509

You could create a table (f.i. 'DimAccount') in the relational database (which you can fill from TM1 with TI, based on the current dimension structure):

TM1element DBcomponent
Total Balance 4001
Total Balance 4002
Total Balance 4009
Total Balance 4501
Total Balance 4502
Total Balance 4509
Total Assets 4001
Total Assets 4002
Total Assets 4009
Total Liabilities 4501
Total Liabilities 4502
Total Liabilities 4509
4001 4001
4002 4002
4009 4009
4501 4501
4502 4502
4509 4509

In your drill-through SQL statement you can now select on DimAccount.TM1Element='?Account?' in the WHERE clause. The DimAccount table will be joined with the GeneralLedger table on GeneralLedger.NaturalAccount=DimAccount.DBcomponent.


I hope this helps.

Michel

Hi Michel,

Yes, I have done what you suggested and it works well. Thankyou so much for your help.

Damien