ODBC Drill Through on any Level of Element

[fstrydom]

Hi

I searched the forum and came across various posts relating to this subject but none of them actually gave a clear solution.

So, we have a sales cube that has three dimensions, Division, Year and Month.
Division has the following elements in it.
Total Company
->Total Chemical Division
->Consumer Market
->Animal Feeds
->Bulk Products
->Total Mining Division
->South Africa
->Botswana
->Namibia

Now, we are able to drill through to the ODBC sales transactions only if we are on level zero for all three dimensions.
We also managed to convert the alias back to dimension name in the prolog by doing this.

Code: Select all

vDivision=DimensionElementPrincipalName('Division',Division);
vMonth=DimensionElementPrincipalName('Month',Month);
vYear=DimensionElementPrincipalName('Year',Year);

And in the ODBC Query we got something like.

Code: Select all

Select 
entity_code, 
entity_name, 
cal_month, 
cal_year, 
customer, 
invoice_number, 
invoice_amount
from fact_sales_invoices
where entity_code in  ('?vDivision?')
and cal_month = '?vMonth?'
and cal_year = '?vYear?'
  

So the place where we got stuck was, if we try to drill on the sales value for "Total Mining Division" we don't get any records returned from the ODBC as "Total Mining Division" does not exists as a value in the fact_sales_invoices table.
So we tried changing the variable vDivision in the prolog to.

Code: Select all

vDivision= '{TM1DRILLDOWNMEMBER( {Division}, ALL, RECURSIVE )}' ;

But we still get no result. So the question that I have is actually two fold.
1. How can I see what values are assigned to vDivision at run-time? This would allow us to model the variable to get the right string to pass to the ODBC where statement.

2. How do I populate vDivision in the example above, so that it is set to 'South Africa','Botswana','Namibia'?

[rmackenzie]

Use this code snippet in the Prolog - it will work for Division and you can adapt it for the other dimensions

Code: Select all

# variable for generated SQL
sSQL='';

# we need the children of this consolidated element in Division dimension
sDimName = 'Division';
sParentElemName=Division;

# build subset in Division dimension with all leaf children of the parent
sSubName='zTemp';
SubsetCreateByMdx(sSubName,'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {['|sDimName|'].['|sParentElemName|']}, ALL, RECURSIVE )}, 0)}');

# iterate subset and add leaf elements to SQL statement
nCounter=1;
nMaxCount= SubsetGetSize(sDimName,sSubName);
WHILE(nCounter<=nMaxCount);
  sElemName= SubsetGetElementName(sDimName,sSubName,nCounter);
  # make sure it is principal name to match values in source system
  # probably don't need this line - the MDX should return this
  sPrincipalElemName= DimensionElementPrincipalName(sDimName,sElemName);
  # update SQL
  sSQL=sSQL|CHAR(39)|sPrincipalElemName|CHAR(39);
  # need a comma unless last item in subset
  IF(nCounter<nMaxCount);
    sSQL=sSQL|',';   
  ENDIF;
  nCounter=nCounter+1;
END;

# clean up temp subset
SubsetDestroy(sDimName,sSubName);

# debug output
AsciiOutput('debug.txt',sSQL);

# assign to drill-through query
vDivision=sSQL;

[fstrydom]

Hi rmackenzie

Thanks for the snippet, I am almost there. But as soon as I drill down I get the following message.
"Drill object was successfully built but did not return a valid handle. Check the Message Log."
Looking at the message log, I get no detail.

Searching in the log files I get my a file "TM1ProcessError_20110824110259_}Drill_GLProteaReportTOTransactions.log"

Code: Select all

Error executing SQL query: 
"SELECT     
TOP (100) dim_entity.domain_code, 
dim_entity.entity_code, 
dim_entity.entity_name, 
dim_base_currency.base_currency_code,  
dim_account.account_code, 
dim_account.account_description, 
dim_sub_account.sub_account_code, 
dim_sub_account.sub_account_description,  
dim_cost_center.cost_center_code, 
dim_cost_center.cost_center_description, 
dim_layer.layer_code, dim_layer.layer_description,  
dim_effective_date.effective_fin_year, 
dim_effective_date.effective_cal_month_name, 
dim_effective_date.effective_fin_month_no,
 'ACTUAL' AS Version, fact_gl_transaction.transaction_reference, 
fact_gl_transaction.reversal_flag, 
fact_gl_transaction.line_number,  
fact_gl_transaction.posting_voucher_reference, 
fact_gl_transaction.batch, fact_gl_transaction.daybook_code, 
fact_gl_transaction.daybook_entry_num, 
 fact_gl_transaction.description, 
fact_gl_transaction.[document], 
fact_gl_transaction.document_type, 
fact_gl_transaction.source_gl_description,  
fact_gl_transaction.user_id, 
fact_gl_transaction.correction_flag, 
fact_gl_transaction.amount_base 
FROM         fact_gl_transaction 
INNER JOIN dim_account ON fact_gl_transaction.dim_account_key = dim_account.dim_account_key 
INNER JOIN dim_cost_center ON fact_gl_transaction.dim_cost_center_key = dim_cost_center.dim_cost_center_key 
INNER JOIN dim_entity ON fact_gl_transaction.dim_entity_key = dim_entity.dim_entity_key 
INNER JOIN dim_layer ON fact_gl_transaction.dim_layer_key = dim_layer.dim_layer_key 
INNER JOIN dim_sub_account ON fact_gl_transaction.dim_sub_account_key = dim_sub_account.dim_sub_account_key 
INNER JOIN dim_base_currency ON fact_gl_transaction.dim_base_currency_key = dim_base_currency.dim_base_currency_key 
INNER JOIN dim_effective_date ON fact_gl_transaction.dim_effective_date_key = dim_effective_date.dim_effective_date_key 
WHERE      (dim_entity.domain_code = 'PCHEMZA')  
AND (dim_entity.entity_code in  (' 'PCINL','PCWC','PCKZN','PCBR','PCEC'') )  
AND (dim_base_currency.base_currency_code = 'ZAR')  
AND (dim_account.account_code  in  ('621610'))  
AND (dim_effective_date.effective_fin_year = '2012')  
AND (dim_effective_date.effective_cal_month_name = 'Apr')"

The debug.txt file contains:

Code: Select all

" 'PCINL','PCWC','PCKZN','PCBR','PCEC'"

The double quotes are like that in the file. Is it just the way the file is outputted or is it the actual value stored in sSQL?

Anyhow, I guess I should fix the syntax in the where statement from

Code: Select all

AND (dim_entity.entity_code in  ('?vDivision?') ) 

to

Code: Select all

AND (dim_entity.entity_code in  (?vDivision?) ) 

Yes, that worked!!!!
Just a reminder, the SQL parser complains that the SQL fails as soon as I save it, but when I drill, it works like a bomb.

rmackenzie, I thank you very much.

[David Usherwood]

That's an interesting approach to the drillthrough problem. I'm too busy to explore it at present but will when I get a moment.
In the past I have dealt with this by inverting the dimension hierarchies, pushing them to SQL lookup tables and using a view which would give a database purist a panic attack. This is neater - but it does rely on TM1's rather flaky, non-standard and under-documented implementation of MDX. And I wonder what performance would be like if you had a fair number of large dimensions which you needed to build the subsets for the query.
Might be worth looking at populating small tables with the subsets and using them in an IN (select * from xx) type query.

[rmackenzie]

That's an interesting approach to the drillthrough problem. ... This is neater - but it does rely on TM1's rather flaky, non-standard and under-documented implementation of MDX.

Of course, there are pros and cons to all the different approaches. In some circumstances, using MDX may be sub-optimal; there is always a way to recreate a subset created via dynamic expression with a bit of TI:

Code: Select all

sDimName = YOUR_DIMENSION_NAME;
sSubName = 'zTemp';
sParentElemName = YOUR_CONSOLIDATED_ELEMENT;

# create subset
IF(SubsetExists(sDimName,sSubName)=0);
  SubsetCreate(sDimName,sSubName);
ELSE;
  SubsetDeleteAllElements(sDimName,sSubName);
ENDIF;

# find n-level descendents of named parent
nCounter=1;
nIndex=1;
nMaxCount=DIMSIZ(sDimName);
WHILE(nCounter<=nMaxCount);
  sElemName=DIMNM(sDimName,nCounter);
  sElemType=DTYPE(sDimName,sElemName);
  # filter non n levels
  IF(sElemType@='N');
    # is parent an ancestor
    nCheck=ELISANC(sDimName,sParentElemName,sElemName);
    IF(nCheck=1);
      # add to subset
      SubsetElementInsert(sDimName,sSubName,sElemName,nIndex);
      nIndex=nIndex+1;
    ENDIF;
  ENDIF;
  nCounter=nCounter+1;
END;

And I wonder what performance would be like if you had a fair number of large dimensions which you needed to build the subsets for the query.

I guess the overhead could get noticeable - using straightforward TI might help... at some point it might be worthwhile implementing a limit in the drill rules about drilling from very high levels of aggregation in the cube to mitigate against this.

Might be worth looking at populating small tables with the subsets and using them in an IN (select * from xx) type query.

Sounds like an equally interesting approach!

[lotsaram]

I think Robin's approach is pretty neat and simple but I would be concerned when executing this on a node of a dimension that had a lot of leaf descendants simply due to the length of the WHERE clause of the SQL. It could easily break some per line character limit or per query character limit either in TI or the ODBC driver. There are a few reports of TIs failing or server crashes from nothing more than a DataSourceQuery variable that was too big (maybe this has been fixed now in TM1 but who knows what the limitations of each particular driver are?)

[kest]

Thank you guys.
Great code and examples!

I little will update the topic.

First
In TM1 Drill Process in the Prolog Tab write (Thx rmackenzie):

Code: Select all

vUserLogin = TM1User () ;
vUserName = ATTRS ( '}Clients' , vUserLogin , '}TM1_DefaultDisplayValue' ) ;

##### Company
# variable for generated SQL
   sSQL='';
# we need the children of this consolidated element in Company dimension
   sDimName = 'Company';
   sParentElemName=Company;
# build subset in Company dimension with all leaf children of the parent
   sSubName='zTemp_TM1_DRILL_DOWN_PL_V_' | vUserName ;
   SubsetCreateByMdx(sSubName,'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {['|sDimName|'].['|sParentElemName|']}, ALL, RECURSIVE )}, 0)}');
# iterate subset and add leaf elements to SQL statement
   nCounter=1;
   nMaxCount= SubsetGetSize(sDimName,sSubName);
   WHILE(nCounter<=nMaxCount);
      sElemName= SubsetGetElementName(sDimName,sSubName,nCounter);
      # make sure it is principal name to match values in source system
      # probably don't need this line - the MDX should return this
      sPrincipalElemName= DimensionElementPrincipalName(sDimName,sElemName);
      # update SQL
      sSQL=sSQL|CHAR(39)|sPrincipalElemName|CHAR(39);
      # need a comma unless last item in subset
      IF(nCounter<nMaxCount);
         sSQL=sSQL|',';   
      ENDIF;
      nCounter=nCounter+1;
   END;
# clean up temp subset
   SubsetDestroy(sDimName,sSubName);
# debug output
# AsciiOutput(LogProcess | 'searchCompany_' | vUserName | '.txt',sSQL);
# assign to drill-through query
   searchCompany=sSQL;

Next the same code for all other Dimensions...

Second
Write query like this with Totals and Details or only with Details..:
(The Amount is MONEY type in SQL, and for my country a good representation of the sum is 1234567890,1234, so i "convert" is
REPLACE(CAST(SUM([Amount]) AS VARCHAR),'.',',') )

Code: Select all

/*Totals*/
SELECT 
'' AS Version, '' AS Company, '' AS YearsMonths, '' AS CFR, '' AS AccountsPL, GetDate() AS AccountsDate, '' AS Projects, '' AS Contractors, '' AS [Other fields from accounting system],
REPLACE(CAST(SUM([Amount]) AS VARCHAR),'.',',') AS [Amount],
'?Company?' AS Company_Filter, 
'?YearsMonths?' AS YearsMonths_Filter,
'?CFR?' AS CFR_Filter,
'?AccountsPL?' AS AccountsPL_Filter,
'?Projects?' AS Projects_Filter,
'?Contractors?' AS Contractors_Filter,
'?vUserName?' AS ReportUser
FROM [VD].[dbo].[TM1_DRILL_DOWN_PL_V]
WHERE 
[Company] IN (?searchCompany?)
AND [YearsMonths] IN (?searchYearsMonths?)
AND [CFR] IN (?searchCFR?)
AND [AccountsPL] IN (?searchAccountsPL?)
AND [Projects] IN (?searchProjects?)
AND [Contractors] IN (?searchContractors?)
UNION
/*Details*/
SELECT 
Version, Company, YearsMonths, CFR, AccountsPL, AccountsDate, Projects, Contractors, [Other fields from accounting system],
REPLACE(CAST([Amount] AS VARCHAR),'.',',') AS [Amount],
'?Company?' AS Company_Filter, 
'?YearsMonths?' AS YearsMonths_Filter,
'?CFR?' AS CFR_Filter,
'?AccountsPL?' AS AccountsPL_Filter,
'?Projects?' AS Projects_Filter,
'?Contractors?' AS Contractors_Filter,
'?vUserName?' AS ReportUser
FROM [VD].[dbo].[TM1_DRILL_DOWN_PL_V]
WHERE 
[Company] IN (?searchCompany?)
AND [YearsMonths] IN (?searchYearsMonths?)
AND [CFR] IN (?searchCFR?)
AND [AccountsPL] IN (?searchAccountsPL?)
AND [Projects] IN (?searchProjects?)
AND [Contractors] IN (?searchContractors?)

Third
To make the beauty for TM1WEB add to the end of the file standaloner.css this (or similar) code:
( If you do not use Totals, then remove fourth line "...first-child + tr..." )

Code: Select all

table.relationalDrillThroughGrid tr:nth-child(even) {background-color: #eeeeff;}
table.relationalDrillThroughGrid tr:hover {background-color: #FFE669}
table.relationalDrillThroughGrid tr:first-child {background-color: #DFE3E8;color: #333333;font-weight:normal}
table.relationalDrillThroughGrid tr:first-child  + tr{background-color: #DFE3E8;color: #333333;font-weight:bold}
table.relationalDrillThroughGrid td {height: 15px;vertical-align: top;padding: 5px;text-align:left;border-bottom: 1px solid #000;font-family: tahoma,arial,sans-serif;font-size:11px}

(after: stop the TM1 Applications service, delete the contents of the ..\tm1web\excelsheet directory, start TM1 Applications service, сlear the web browser cache)

Thx David Nyman. Link for details: http://blogg.attollo.se/2016/02/pimpa-u ... drill.html

FIN