Thursday, February 17, 2011

SQL queries for Essbase developers

So when I first set up this blog I posted that I would not only write about Essbase all of the time and I would include other content as well. Looking back over my posts, I have not lived up to that too well, so here's one in the "other technology" category. Of course it still has an Essbase slant on it, but even if you are one of those non-believers that uses a different OLAP technology, some of these examples might still be of interest to you.

Recursive (Parent/Child) queries in SQL.
Let's say you are well organized and have your metadata structures in relational tables, rather than just living in some Excel files. Now suppose you want to spin off a cube from one of these structures, but you don't want the entire structure, maybe you just want a particular branch of the hierarchy (i.e. all descendants below a certain parent). Pulling that kind of query in a relational database used to be kind of tough. Here's an example of how to do it.

Let's say you start off with an Outline extract of the Sample Basic database for the Market dimension and you want to query the table to get all descendant of "East".


WITH Descendants (child,parent) AS
(SELECT [CHILD0,Market], [PARENT0,Market]
FROM dbo.Market
WHERE [PARENT0,Market] = 'East'
UNION ALL
SELECT dbo.Market.[CHILD0,Market],
dbo.Market.[PARENT0,Market]
FROM dbo.Market
INNER JOIN Descendants ON dbo.Market.[PARENT0,Market]= Descendants.child)

SELECT DISTINCT dbo.Market.[PARENT0,Market], dbo.Market.[CHILD0,Market]
FROM dbo.Market
INNER JOIN Descendants ON dbo.Market.[CHILD0,Market] = Descendants.child




Your results will be


PARENT0,Market CHILD0,Market
East Connecticut
East Florida
East Massachusetts
East New Hampshire
East New York



Play around with it and see what you can do with it.
Be forewarned, if the table is big this might take a while.
Also note that this "Descendants" feature in MS SQL Server didn't show up until SQL Server 2005.


Another one I have heard is how do I take a parent/child table and turn it into Generations or Levels.

Using a basic Parent/Child table going to Generations would look like this


SELECT a_1.Parent AS Gen1, b_1.Child AS Gen2, c_1.Child AS Gen3, d_1.Child AS Gen4, e_1.Child AS Gen5
FROM (SELECT Parent
FROM dbo.Test_table
WHERE (Parent NOT IN
(SELECT DISTINCT Child
FROM dbo.Test_table AS A))) AS a_1 INNER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS B) AS b_1 ON a_1.Parent = b_1.Parent LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS C) AS c_1 LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS D) AS d_1 LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS E) AS e_1 ON d_1.Child = e_1.Parent ON c_1.Child = d_1.Parent ON b_1.Child = c_1.Parent
GROUP BY a_1.Parent, b_1.Child, c_1.Child, d_1.Child, e_1.Child



If you want to go to levels it would look like this.


SELECT a_1.Child AS lev0, b_1.Parent AS lev1, c_1.Parent AS lev2,
d_1.Parent AS lev3, e_1.Parent AS lev4
FROM (
SELECT Child
FROM dbo.Test_table
WHERE(Child NOT IN
(SELECT DISTINCT Parent
FROM dbo.Test_table AS A))) AS a_1 INNER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS B) AS b_1 ON a_1.Child = b_1.Child LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS C) AS c_1 LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS D) AS d_1 LEFT OUTER JOIN
(SELECT DISTINCT Child, Parent
FROM dbo.Test_table AS E) AS e_1 ON d_1.Parent = e_1.Child ON
c_1.Parent = d_1.Child ON b_1.Parent = c_1.Child



Lastly, let's say you have Generations and you want to go to Parent/Child.
It is important to have a sort key on your source table to maintain the proper order.

So assume the source table looks like

Sort_ID, Gen1, Gen2, Gen3, Gen4

You would create a target table then run some insert queries to cycle through the source table. Lastly I like to run a couple of update queries to tag lev0 members and upper level members.


CREATE TABLE [dbo].[Target] (
[ID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[parent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[member] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[group_item_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


INSERT INTO [dbo].[Target] ([parent], [member])
SELECT [Gen1], [Gen2]
FROM [dbo].[Source]
GROUP BY [Gen1], [Gen2]
HAVING (NOT ([Gen2] IS NULL))
ORDER BY MIN([sort_key])

INSERT INTO [dbo].[Target] ([parent], [member])
SELECT [Gen2], [Gen3]
FROM [dbo.[Source]
GROUP BY [Gen2], [Gen3]
HAVING (NOT ([Gen3] IS NULL))
ORDER BY MIN([sort_key])

INSERT INTO [dbo].[Target] ([parent], [member])
SELECT [Gen3], [Gen4]
FROM [dbo.[Source]
GROUP BY [Gen1], [Gen2]
HAVING (NOT ([Gen2] IS NULL))
ORDER BY MIN([sort_key])

UPDATE [dbo].[Target]
SET [group_item_flag] = 'G'
WHERE [member] IN (
SELECT DISTINCT [parent]
FROM [dbo].[Target])

UPDATE [dbo].[Target]
SET [group_item_flag] = 'I'
WHERE [member] NOT IN (
SELECT DISTINCT [parent]
FROM [dbo].[Target])



Let me know some of your favorites in the comments.

Been a long time between posts

Wow, it has been a long time between posts. It's not that I haven't wanted to but it just seems things have been so busy lately. I am living the cliche "do more with less". Actually it's just do more with what you got and more and more and more. Oh well, not that I'm complaining. Anyway it seems like Kaleidoscope is coming around the corner soon and that usually gets me to dust off my blog as I get excited about attending. This year will be particularly important to me since I had to miss last year when I had to cancel at the last minute due to personal reasons. I'm sorry to anyone who was planning to see my presentation, I made a point of forwarding all of my content and I know my session wasn't canceled, a fill in speaker took the slot for me and presented my material, so hopefully that made up for my last minute drop out. This year will hopefully not see anything unexpected. I'm working on a very cool MDX presentation for administrators. Two years ago at Kaleidoscope I was co-presenting an MDX presentation with Mike Nader. IN the banter back and forth some questions came up about how much could we really use MDX as a query language. We get that we use it for member formulas but as a stand alone query language how much would we use it? Essbase users are spoiled with our rockin addin and the idea of actually having to code our queries seems like a step backwards to us. Still you can do some pretty cool things with MDX and one thing in particular that came up was when I mentioned that as an admin, MDX had some great functionality for querying the member outline and understanding things about your dimensions and members. It got me thinking that it would make a pretty cool session. So this year that is what people who attend my session will get. I'm going to explore the ways you can use MDX as an administrator to understand your outline. We'll start off simple by returning members of a dimension, then looking at how to pull children or descendants. We'll go through how to pull a list of members with a particular attribute or UDA. Maybe you want to find all members with a member formula or count how many level 0 members you have in a particular dimension. I plan to have a lot of sample code which attendees will be able to take back and use in their own shops. And since MDX can query both BSO and ASO cubes, I'm going to show people how to run an MDX query that will guide them towards optimizing their BSO outlines when ordering their sparse dimensions.
Beyond my presentation, which should be enough to make anyone want to attend :) there are looking to be some amazing presentations this year. Kaleidoscope has grown considerably, there will be more Oracle presence, a larger variety of vendors and partners, the hands on labs are going to be awesome and the usual fun and networking events will be included. I think we are also finally past those few awkward years of vying amongst the different user groups about whose conference is the best or who is going to be the "next solutions" (please, see my previous post) These days I think people have come to recognize that if you are a Hyperion professional and you want the best Hyperion training for the best value, along with a front row seat to all the new product features coming down the line, there is no conference better than Kaleidoscope. I have no doubt this year will live up to and surpass previous years. I'm looking forward to seeing everyone in June. If you haven't registered yet, you really should. http://www.kscope11.com/