Drill Through Assistance Request

[shopkins]

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.

[tomok]

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

[shopkins]

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!

[tomok]

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: Select all

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.

[Michel Zijlema]

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?

This is the solution that I often use for drill-through on consolidated level(s).

Michel

[shopkins]

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.

[tomok]

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;

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.

I have found the documentation to be quite lacking

Welcome to TM1!

[David Usherwood]

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.

[shopkins]

Here is the crux of the situation:
I am trying to create a proof of concept for this. I think the final endgame will result in an amalgam of the pieces that have been suggested here (table containing the subsets and their values, view, stored procedures). However, I am mostly new to TM1 programming. All of our code was written by an outside consultant, and I have been able to make small changes when required. I don't know much of the processes or language.

I am going to try and complete a 'Create Drill Process' to at least get something done for a demonstration. My detail data is in another database and I have the 60 line query to get the detail from the summary values which are in TM1 (that's before I have to update the query with the values being pulled).

I noticed that when I create this drill process. There is a screen to provide all the default values. What does this do? The requirement that I was given was that the user would have to choose a specific value for each dimension (no subsets - which I know will changed in the future).

Once the drill process is created, I know what may have to be added to the query:
if ?DimensionValue? = 'No <Dimension Name>' then go forward else add to a join line
I am thinking that I should create the SQLStr with everything up to where it can be modified by the dimension values then add the pieces as I go through the dimension values. Would that make sense?

I am going to play around in the meantime and see what I can come up with as well which may generate more questions.

[shopkins]

Here are some quick questions (hopefully):
I created a process and pasted my query into the query window, but dont I want to create this in the Prolog step?
What is the best way to start creating the process in the Prolog? Would you remove the query from the query step?

[shopkins]

One other question - when I have my query in the query window,some of the variables that are coming up as values with decimals attached when they are a numeric ID. How can I remove these decimal places?