企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 1027|回复: 5

ODBC Drill Through on any Level of Element

[复制链接]

81

主题

429

帖子

608

积分

高级会员

Rank: 4

积分
608
QQ
发表于 2014-3-18 14:24:42 | 显示全部楼层 |阅读模式
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'?

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

69

主题

368

帖子

521

积分

高级会员

Rank: 4

积分
521
QQ
发表于 2014-3-18 15:31:03 | 显示全部楼层
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;

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

76

主题

356

帖子

534

积分

高级会员

Rank: 4

积分
534
QQ
发表于 2014-3-18 15:55:09 | 显示全部楼层
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.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

79

主题

384

帖子

573

积分

高级会员

Rank: 4

积分
573
QQ
发表于 2014-3-18 15:59:22 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

87

主题

373

帖子

564

积分

高级会员

Rank: 4

积分
564
QQ
发表于 2014-3-18 16:09:01 | 显示全部楼层
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!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

71

主题

393

帖子

560

积分

高级会员

Rank: 4

积分
560
QQ
发表于 2014-3-18 16:35:29 | 显示全部楼层
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?)
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-11-24 14:41 , Processed in 0.100105 second(s), 11 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表