How to avoid nested table columns (flattened in SQL server) when querying OLAP with MDX?

Question

Post reply

How to avoid nested table columns (flattened in SQL server) when querying OLAP with MDX?

Jens Dossé

Old Hand

Points: 314
More actions
June 4, 2007 at 10:54 am

#117265

I have OLAP cubes (SQL Server Analysis Services) where some dimensions have hierarchies (data are aggregated in higher levels by OLAP).
When trying to query the cubes from within T-SQL using OpenQuery or OpenRowset to obtain the data in a fact table format, OLAP returns the hierarchized dimensions as nested table columns that are flattened into several columns by SQL server.
For example, the T-SQL + MDX query:
Select * From OpenRowset('MSOLAP.3', 'Data Source=dotstat;Initial Catalog=CRSNEW;',
'SELECT { [Measures].[Value] } ON COLUMNS,
NONEMPTYCROSSJOIN(
[AMT].&[568273],
[DON].[DON].AllMembers,
{[YEA].[2000], [YEA].[2001], [YEA].[2002], [YEA].[2003]}
) DIMENSION PROPERTIES MEMBER_KEY ON ROWS
FROM CRSNEW')
returns an SQL table with the columns:
[AMT].[AMT].[MEMBER_KEY],
[DON].[DON].[MEMBER_KEY],
[DON].[DON (level 03)].[MEMBER_KEY],
[DON].[DON (level 04)].[MEMBER_KEY],
[YEA].[YEA].[MEMBER_KEY],
[Measures].[Value]
But, instead of 3 columns for the DON dimension (one column for each hierarchy level) I would like to get only one single column containing the MEMBER_KEY of the actual member. I do NOT need the MEMBER_KEYs of all the parent and grand-parents at higher levels.
How can this be done? Thanks in advance for any help.
Jens

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Perhaps try using [Don].[Don].Members rather than .Allmembers?

Steve.

Jens Dossé Old Hand Points: 314 More actions · Answer 2

Thanks for your reply.

I actually didn't post the correct query that corresponds to the result given (because I tested other possibilities).

Querying "[Don].[Don].AllMembers" is exactly the same as querying "[Don].[Don].Members", and retrieves only the member ("All Don") at the top level in the hierarchy (and this using just one single column for this dimension titled "[DON].[DON].[MEMBER_KEY]".

But as soon as any member at a lower dimension hierarchy level is retrieved (e.g. when using "[DON].Members" or "[DON].AllMembers" or "[DON].[DON].[France]") then SQL server creates several columns (one for each hierarchy level involved).

Jens

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Depending on your situation you could always select the fields you're actually interested in rather than simply doing a 'select *' over the openrowset.

Just remember to double-up your (closing) square brackets when specifying the field names e.g [[Steve]].[was]].[here]]]

Steve.

Jens Dossé Old Hand Points: 314 More actions · Answer 4

The whole problem is that the fields that I would need for a real fact table differ from one value to another. Actually the column in which is returned the member key that corresponds to the current value depends on the level of the member in the hierarchy.

An example:

[AMT].[AMT].[MEMBER_KEY]	[DON].[DON].[MEMBER_KEY]	[DON].[DON (level 03)].[MEMBER_KEY]	[DON].[DON (level 04)].[MEMBER_KEY]	[YEA].[YEA].[MEMBER_KEY]	[Measures].[Value]
568273	568263	NULL	NULL	1901	212928.886
568273	568263	381138	NULL	1901	131772.4997
568273	568263	381138	381139	1901	4144.50695

This is a part of the table that is returned. I have marked in yellow the member keys that correspond to each value (in red) and that I need to identify the data. You can see that it will be very difficult to match the correct column as it depends on the attachement level of the member in the dimension hierarchy. Moreover, the number of returned columns depends on the returned data. E.g. if the returned data does not correspond to any member in the last level of the dimension hierarchy ("[DON].[DON (level 04)]") then this column is dropped from the resulting table.

You see that this dynamic structure of the result set is very enoying and would force me to dynamically construct select statements... 🙁

Why do I have to double-up the (closing) square brackets?

Jens

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

I get the feeling you're trying to query the cube to reverse engineer yourself a fact table... fun...

Looking at what you've provided above, why not take a multi-part approach? Runt he select * out in to a sql table and then use the relational engine to extract the fact table data from table? I'm guessing that if you were presented with the table you'd provided above, using TSQL to get the appropriate keys wouldn't be that difficult? Even at first glance you could try something like

select ..... , isnull([[don]].[don (level 04)]].[member_key]]], [[don]].[don (level 03)]].[member_key]]], [[don]].[don]].[member_key]]]), ... from ....

Having said that, theoretically you could run that same statement against the openrecordset too.

Interesting data set - it looks like you have data against non-leaf level nodes in the tree; or i guess you may have a three part key (but then you'd want to keep those nulls).

The double closing is because the square brackets are TSQL identifiers (e.g. table name is 'steves table' you would normally write this as [steves table]. Doubling the closing is required when your names include square brackets (as yours do).

Steve.

Jens Dossé Old Hand Points: 314 More actions · Answer 6

You are exactly right. We use the OLAP cube for fast retrieval of pre-calculated aggregates (within dimension hierarchies) as an alternative to the very slow dynamic SQL aggregations. Because the classic MS OLAP client applications (e.g. Excel pivot tables, OWC) have many serious disadvantages (access stopped by firewall, impossibility of adapting fonctionality and design, needs specific heavy client tools like OWC or Excel services), we prefere to re-inject the OLAP aggregations into SQL. We have already a very nice web interface that uses SQL and can display the data in a kind of pivot table based on HTML (see here an example of how we can display data coming out of SQL: http://stats.oecd.org/wbos/default.aspx?DatasetCode=PATS_COOP).

The ISNULL command is not quite correct because it takes only 2 parameters, you would need nested ISNULL commands. Fortunately, there is another command doing exactly this: COALESCE.

But there is another problem: All those 3 columns are not always present in the result set. E.g. if I query only for [DON].&[568263] than I only get the first column but not the columns of the lower dimension levels for which no member is present in the result set.

However, I have somehow good news. I managed to get the names of the present columns from the syscolumns table, and generate the select statement dynamically:

Select * into #temp From OpenRowset('MSOLAP.3', 'Data Source=dotstat;Initial Catalog=CRSNEW;',

'SELECT { [Measures].[Value] } ON COLUMNS,

NONEMPTYCROSSJOIN(

[AMT].&[568273],

[DON].[DON].AllMembers,

{[YEA].[2000], [YEA].[2001], [YEA].[2002], [YEA].[2003]}

) DIMENSION PROPERTIES MEMBER_KEY ON ROWS

FROM CRSNEW')

declare @string varchar(8000)

set @string = 'COALESCE('

declare @temp1 table (colid int, [name] varchar(128))

insert into @temp1

select colid , [name] from tempdb.dbo.syscolumns where object_id('tempdb.dbo.#temp') = id

and name like '[[]DON]%'

order by colid desc

select @string = @string + '[' + replace([name],']',']]') + '],' from @temp1

select @string = 'select [[amt]].[amt]].[member_key]]] as [AMT], ' + left(@string, len(@string)-1) + ') as [DON], [[yea]].[yea]].[member_key]]] as [YEA], [[Measures]].[Value]]] as [VALUE] from #temp'

exec( @string)

The string generated is:

select [[amt]].[amt]].[member_key]]] as [AMT], COALESCE([[DON]].[DON (level 04)]].[MEMBER_KEY]]],[[DON]].[DON (level 03)]].[MEMBER_KEY]]],[[DON]].[DON]].[MEMBER_KEY]]]) as [DON], [[yea]].[yea]].[member_key]]] as [YEA], [[Measures]].[Value]]] as [VALUE] from #temp

This solution is workable even if it is not as elegant as I would have wished. Thanks again for your help.

Jens