ODBC Drill Through on any Level of Element

xiphiasjoe

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: vDivision=DimensionElementPrincipalName('Division',Division);
vMonth=DimensionElementPrincipalName('Month',Month);
vYear=DimensionElementPrincipalName('Year',Year);

And in the ODBC Query we got something like.
Code: 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: 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'?

luozhongli

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

Code: # 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;

酥糖公子

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: 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.&#91;document&#93;,
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: " '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: AND (dim_entity.entity_code in  ('?vDivision?') )

to
Code: 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.

pakyarms

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.

fuanxx

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: 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!

落日清风

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?)