企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 1044|回复: 7

Drill Through Assistance Request

[复制链接]

80

主题

401

帖子

588

积分

高级会员

Rank: 4

积分
588
QQ
发表于 2014-6-28 10:15:14 | 显示全部楼层 |阅读模式
Hello

I am trying to create a drill-through process to get back to the detail that created a cell.

We have a cube that is created from a summary table. The details which make up the summary table are stored in another database on another server.

This cube has 21 dimensions. However, I have been told that for the original concept design that the drill-through work when each dimension has a single value defined. For the summary records, the combination of 16 dimensions will make a unique ID which with some of the other remaining dimensions make up a unique way to find the records in the summary table.

So the basics of a query would be to the details:
select * from DetailTable
where Dim1 =
and Dim2 =
and Dim3 =
and Dim4 =
and ID = (select ID from IDLookUpTable
        where Dim5 =
        and ....
        and Dim20 = )

I am trying to determine if this is even possible and if so, to create the drill-through SQL, what do I need to put behind each = for each of the values defined.

We have never done drill-throughs here before so I am trying to get a good starting point on this. The documentation is limited and I am trying to learn this on the fly.

I will gladly supply any information that I can if I am missing anything in the description.
回复

使用道具 举报

87

主题

397

帖子

595

积分

高级会员

Rank: 4

积分
595
QQ
发表于 2014-6-28 12:15:58 | 显示全部楼层
The code in the query box in the TI drill-thru process should look like:

select * from DetailTable
where Dim1 = '?Dim1?'
and Dim2 = '?Dim2?'
and Dim3 = '?Dim3?'
and Dim4 = '?Dim4?'
and ID = (select ID from IDLookUpTable
where Dim5 = '?Dim5?'
and ....
and Dim20 = '?Dim20?')
回复 支持 反对

使用道具 举报

77

主题

397

帖子

570

积分

高级会员

Rank: 4

积分
570
QQ
发表于 2014-6-28 12:28:28 | 显示全部楼层
Here is something that I have setup in our system:
A dimension can be set to choose 'All <Dimension>' which will pick up all values.  How would that fit into the query above?  Would there need to be logic to show all of the possible values?

Also, is there a way to get the SQL that is created after the values are passed into the query?  I would like to see the generated SQL so I can try to debug if/when any issues come along.

Thank you!
回复 支持 反对

使用道具 举报

86

主题

415

帖子

604

积分

高级会员

Rank: 4

积分
604
QQ
发表于 2014-6-28 13:40:40 | 显示全部楼层
shopkins wrote:A dimension can be set to choose 'All <Dimension>' which will pick up all values.  How would that fit into the query above?
Well, logic would tell you that if you don't want to filter records in the data source based on values in a specific field then you would remove the WHERE clause that is specific to that field. In the case where you want to both allow a WHERE clause and not have a WHERE clause, all based on user selection, then you are not going to be able to use the hard-coded construct of the query. You will have to build your query string on the Prolog tab, using a comination of dimension selections (or TI paramaters), IF statements, and the string functions in TM1. Then you can assign that new query string to the process using the DatasourceQuery local paramter in TI. Something like:

Code: QueryStr = 'SELECT * FROM MyTable':
IF(Dim1@<>'All elements');
  QueryStr = QueryStr & ' WHERE Dim1=char(39)&Dim1&char(39);
ELSEIF(Dim2@<>'All elements');
  QueryStr = QueryStr & ' WHERE Dim2=char(39)&Dim2&char(39);
.....
ELSEIF(DimX@<>'All elements');
  QueryStr = QueryStr & ' WHERE DimX=char(39)&DimX&char(39);


In this case the string 'All elements' would correspond to the node at the top of the house for that dimension. If you further complicate matters by saying you want users to be able to pick ANY node, and have the drill-thru show only children of that node then it gets very tricky. Everyone has their own opinion of how it should work in that case. My preference is to modify the drill thru source to add fields for every level of every drill dimension and place the parent for that level in the field (the parent for level 0 would just be the element itself). Then you can just modify the query to pick values in the parent field and it will work regardless of whether the drill thru selection was a leaf or a node. In either case there is a lot of work to do in the prolog tab because you have to check every dimension variable to find out what level it is in the dimension and then construct the query to look at the correct field in the source. All this, of course, is assuming you have control over the drill thru source and can have someone change it for you if needed. Sometimes this is not the case, depending on your environment. I don't know what you do then. The bottom line is the drill thru setup in TM1 is not childs play and the documentation around it is quite poor.

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

86

主题

396

帖子

584

积分

高级会员

Rank: 4

积分
584
QQ
发表于 2014-6-28 13:46:43 | 显示全部楼层
shopkins wrote:A dimension can be set to choose 'All <Dimension>' which will pick up all values.  How would that fit into the query above?  Would there need to be logic to show all of the possible values?
is the solution that I often use for drill-through on consolidated level(s).

Michel

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

82

主题

429

帖子

624

积分

高级会员

Rank: 4

积分
624
QQ
发表于 2014-6-28 14:10:55 | 显示全部楼层
Michel - I saw your idea in the other post.  It might be something that I will have to ask you more about in the future.  I really like that idea.

I was finally able to get the query and determine what will need to be added, but now I am a bit stuck on how to code some things in the Prolog.
In the original direction that I was given for this, I was told that every dimension will have to be selected to a single value (probably wont be liked but at least I have a direction to go with for now until it changes on me).

One option for each dimension is 'No <DimName>'.  If the dimension has this value, I would not need it in my join.
So my pseudo-code would be:
If ?Dim1? = 'No Market' then nothing else SQLjoinline = SQLjoinline + ' AND BUF.BU_CODE_MKT = ?Dim1? '

I also need to know how to incorporate that into the final query.

Once I have the query in the Prolog, is there any way to see the final SQL that gets created so I can see it and tweak it before it tries to hit the database?

I have found the documentation to be quite lacking and I am very new to how the TI programming works.
回复 支持 反对

使用道具 举报

81

主题

411

帖子

598

积分

高级会员

Rank: 4

积分
598
QQ
发表于 2014-6-28 14:28:11 | 显示全部楼层
shopkins wrote:I also need to know how to incorporate that into the final query.
Once you've built the final query string you assign it to the DatasourceQuery local TI variable in the Prolog, like:

DatasourceQuery=SQLStr;
shopkins wrote:Once I have the query in the Prolog, is there any way to see the final SQL that gets created so I can see it and tweak it before it tries to hit the database?
No. One of the shortcomings of TM1 we all have to live with.
shopkins wrote:I have found the documentation to be quite lacking
Welcome to TM1!
回复 支持 反对

使用道具 举报

78

主题

403

帖子

578

积分

高级会员

Rank: 4

积分
578
QQ
发表于 2014-6-28 14:42:44 | 显示全部楼层
You wrote it (into SQLStr) - why not output it to a file to inspect?
My own preference here would be to build a stored procedure (and probably a bunch of views) to do the heavy lifting, then call that.
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2020-11-30 10:09 , Processed in 0.086289 second(s), 11 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

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