Drill Through Assistance Request

碧·染

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.

chenwu521

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

亚伯拉汉

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!

Esuom

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.

wcb79

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

ole2002ole

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.

奪命排骨飯

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!

林玫玫

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.