MDX problems with drill down

[dumbom]

hi Guys

i have created a drill process, and it works perfectly at 0 level when we drill from a cube. the problem i am having is that i want to be able to drill at any level of a dimension. i have used info i have found to create a subset using MDX in the prolog tab of the drill process. unfortunately, i do not have a vast knowledge on MDX, so this is all new to me.

Code: Select all

# variable for generated SQL
sSQL=' ';

# we need the children of this consolidated element in BusinessUnit dimension
sDimName = 'Business Unit';
sParentElemName=BusinessUnit;

# build subset in BusinessUnit dimension with all leaf children of the parent
sSubName='zTemp';
SubsetDestroy(sDimName,sSubName);
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
vBusinessUnit=sSQL;

this allows me to still drill on the 0 level but not on any of the ancestors/ Parents of the elements. my thoughts are that TM1DRILLDOWNMEMBER starts at the parent, and expands to the children. should i be using the opposite of TM1DRILLDOWNMEMBER?

what would the best MDX statement to use as there are multiple parents for some elements, as well as multiple levels?

[rmackenzie]

Ultimately, the SQL issued for the relational drill-through needs to read something like:

Code: Select all

SELECT COL_X, COL_Y, BUSINESS_UNIT, COL_Z
FROM YOUR_TABLE
WHERE BUSINESS_UNIT IN ( YOUR_STRING_GOES_HERE)

Where YOUR_STRING_GOES_HERE is the result of your code building up the sSQL variable.

You are assigning the sSQL to the vBusinessUnit variable but that won't do anything unless the vBusinessUnit is a parameter in the drill process. Or, have you looked at using the DataSourceQuery (a 'local' variable') ?

[dumbom]

hi Robin

i must apologize, i didn't give all the information. i am bringing in the data from a Sql table in the data source query, and have vBusinessUnit as a parameter in the drill process. the drill works perfectly, but only on the lowest level of each dimension. the data that comes through from Sql on the data source query is only at its lowest level.

what i am trying to do, is drill at the higher level in each dimension.

so for example:

'Town' is the lowest level for vBusinessUnit, and i can drill on this level with no problems.
'City' is the parent of 'town', but i cant drill on it.
'state' is the parent of 'city', but i can drill on it
and so forth.

from searching online for a solution, it seemed that people with similar problems used the code i mentioned above in their prolog tab and came right, but it hasnt worked for me. that is why i thought that maybe TM1DRILLDOWNMEMBER was possibly the incorrect MDX statement to use.
i have this problem for each dimension in the cube, and once i come right with this one, i will replicate the process for the other ones.

[rmackenzie]

Hi - Your usage of TM1DRILLDOWNMEMBER looks correct to me - you should be returning the n-level elements that roll-up to the consolidation. Also, your code to deal with the resulting subset looks right so I can only think that the way you are passing the content of sSQL back to the parameter is the faulty party of the process. As you use an AsciiOutput to debug sSQL I imagine that you are happy with the result? If so, then something like this should work:

Code: Select all

SELECT COL_X, COL_Y, BUSINESS_UNIT, COL_Z
FROM YOUR_TABLE
WHERE BUSINESS_UNIT IN ( ?vBusinessUnit? )

As long as 'vBusinessUnit' is a Parameter in the Parameters tab.

The only thing that looks a bit weird is that you assign BusinessUnit (not vBusinessUnit) to sParentElementName - is that the bug?

[lotsaram]

from searching online for a solution, it seemed that people with similar problems used the code i mentioned above in their prolog tab and came right, but it hasnt worked for me. that is why i thought that maybe TM1DRILLDOWNMEMBER was possibly the incorrect MDX statement to use.
i have this problem for each dimension in the cube, and once i come right with this one, i will replicate the process for the other ones.

Apologies if you have understood Robin perfectly well with what is meant by YOUR_CODE_GOES_HERE in the SQL statement, but my read of what you wrote is that you didn't understand this in which case the prolog code you posted is incomplete. It isn't enough to generate an N level subset from the consolidated node where the drill is initiated from, you then have to do something with that list of N members, namely iterate them in order to build the string being used in the WHERE statement of the SQL query in the drill process and then replace the parameter (which will be the single consol element name) with the string created by the subset iteration. You will then be able to drill from any level so long as your dimension isn't quite large and blow out the SQL query.

Note that you don't have to just pass or change parameter values, you can build the whole query and set DataSourceQuery as a variable.

e.g. change your example slightly such that City is the N level and the SQL statement WHERE clause currently looks like this
WHERE City = '?pCity?'
So if drilling from N level city 'Los Angeles' it works but drilling from C level California it doesn't. You need to use the subset created of N level descendants of California and replace pCity, but not just as a list. Say the Californian cities in your dimension are: Sacramento,San Francisco,San Jose,Fresno,Bakersfield,Los Angeles,San Diego
you need your where clause to therefore be
WHERE City = 'Sacramento' OR City = 'San Francisco' OR City = 'San Jose' OR City = 'Fresno' OR City = 'Bakersfield' OR City = 'Los Angeles' OR City = 'San Diego'
so you need to concatenate together your N elements with OR and with single quotes, and then replace the parameter value by setting it to your concatenated string on the prolog, you get the picture ...

EDIT:
my bad I only looked at the visible portion of the code box and didn't scroll down. You are iterating the subset and using IN should work just as well as stringing together ORs, more efficiently in fact. So disregard what I wrote and my contribution should be, "whatever Robin said".

[tomok]

So if drilling from N level city 'Los Angeles' it works but drilling from C level California it doesn't. You need to use the subset created of N level descendants of California and replace pCity, but not just as a list. Say the Californian cities in your dimension are: Sacramento,San Francisco,San Jose,Fresno,Bakersfield,Los Angeles,San Diego
you need your where clause to therefore be
WHERE City = 'Sacramento' OR City = 'San Francisco' OR City = 'San Jose' OR City = 'Fresno' OR City = 'Bakersfield' OR City = 'Los Angeles' OR City = 'San Diego'
so you need to concatenate together your N elements with OR and with single quotes, and then replace the parameter value by setting it to your concatenated string on the prolog, you get the picture ...

Building a query string like this can and will work but if you have a large number of children in the parent then you can exceed the maximum allowable string size for the query and it will fail. If this is going to be a commonly used drill feature, and it is possible to modify the database source, I would instead add a new column or columns to the query, namely a parent column for each level in the hierarchy. Then you can pass the element name and level into the SQL to pull all records where the record has a parent equal to the value. In the example above the leaf is cities, Parent 1 is the state, Parent 2 is the country and Parent 3 is the planet. So your DB columns would be look like:

City,Parent1,Parent2,Parent3
Sacramento,California,USA,Earth
San Jose,California,USA,Earth
New York,New York,USA,Earth
Chicago,Illinois,USA,Earth
......

Then if your drilled item is USA your SQL would be SELECT * FROM Cities WHERE Parent 2 = 'USA' or if the drilled item was San Jose it would be SELECT * From Cities WHERE City = 'San Jose'. They key is the parameters passed to the TI have to include both the drilled value AND the appropriate hierarchy column.

[dumbom]

hi Guys

i managed to speak to someone who works with SQL and they managed to find my problem. the problem was in the query tab, and every thing i tried kept saying SQL statement failed, so i kept trying different things so it wouldn't give me that message

i had Business_Unit = (?BusinessUnit?) and it should be Business_Unit in (?BusinessUnit?)

it still says SQL statement failed, but the drill works. i didn't realize that it would still work *Facepalm*

thanks Robin for your suggestion to look there.