Retrieving sub trees and levels in a nested set

Hello,

I have a nested set hierarchy set up that looks a lot like this.

TABLE
Id, lft, rgt
A, 1, 14
B, 2, 3
C 4, 13
D 5, 6
E 7, 10
F 8, 9
G 11, 12

I want to select specific nodes and return its parents like this.

SELECT one.*
FROM table AS one, table AS two
WHERE two.lft BETWEEN one.lft AND one.rgt
AND one.id IN (D, E, B)

Returns something like:

A, 1, 14
B, 2, 3
C, 3, 13
D, 5, 6
E, 7, 8

But not F or G because they are not above D, E, or B.

But I also want to know what level each of the nodes in the hierarchy is on. For example:

A, 0
B, 1
C, 1
D, 2
E, 2

Similar to what the following query returns only limited to the sub trees I select.

SELECT count(two.id) AS level, one.id
FROM table AS one, table AS two
WHERE one.lft BETWEEN two.lft AND two.rgt
GROUP BY one.id

So, what I really want to do is combine these two queries so that I can select multiple sub trees and know what each of the levels of hierarchy are.

Any help is greatly appreciated!
-RobThis is what I did...it's not very elegant so if anyone has more suggestions please let me know.

SELECT two.id, (SELECT count(*)
FROM table
WHERE lft <= two.lft
AND rgt >= two.rgt) AS level
FROM table AS one, table AS two
WHERE one.lft BETWEEN two.lft and two.rgt
AND one.id IN ('D', 'E', 'B')
GROUP BY two.id, two.lft, two.rgt
ORDER BY two.lft

It takes a bit more time than I had desired but it works for now. I am still researching other options so please feel free to reply to this message.

-Rob

Retrieving Hierarchy Captions from a Cellset

I'm trying to retrieve the hierarchy caption(s) for a given axis in a cellset, but whenever I try to retrieve this:

cellset.Axes(0).Set.Hierarchies(0).Name

I get an exception like this:

System.ArgumentException was unhandled
Message="The '[Date].[Calendar Quarter]' object was not found.\r\nParameter name: uniqueName"
Source="Microsoft.AnalysisServices.AdomdClient"
ParamName="uniqueName"
StackTrace:
at Microsoft.AnalysisServices.AdomdClient.CubeDef.InternalGetSchemaObject(SchemaObjectType schemaObjectType, String uniqueName, Boolean retryUniqueName)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetObjectData(SchemaObjectType schemaObjectType, String cubeName, String uniqueName)
at Microsoft.AnalysisServices.AdomdClient.AdomdUtils.PopulateSymetry(IAdomdBaseObject iBaseObject)
at Microsoft.AnalysisServices.AdomdClient.Hierarchy.get_Name()

I've tried setting the MEMBER_NAME dimension property but that doesn't work for this case. Is there another dimension property that I could use?

Thanks,

Terry

hello Terry,

it's not quite clear what's wrong. the following seems to work fine for me:

using (AdomdConnection con = new AdomdConnection())
{
con.ConnectionString = "Data Source=localhost; Catalog=Adventure Works DW;";
con.Open();
using (AdomdCommand cmd = con.CreateCommand())
{
cmd.CommandText = "select [Date].[Calendar].members on 0 from [Adventure Works]"; //"select [Date].[Calendar].[Calendar Quarter].members on 0, [Product].[Product Categories].[Category].Members on 1 from [Adventure Works]";
CellSet cs = cmd.ExecuteCellSet();

Console.WriteLine(cs.Axes[0].Set.Hierarchies[0].Name);
}
}

could you please provide some more info, as to what the calling code and query looks like, what's the version of the AS server, adomd.net that you are using, etc.

Thanks a lot,

|||

Thank you for your reply.

I created a bare-bones test project and verified that it does work on a simple query against Adventure Works but will not work against our own cube.

Does anyone know why we would be getting the exception thrown (as show further above) when simply trying to retrieve the hierarchy's name?

Thank you,

Terry

|||

hello Terry,

my guess is that maybe the hierarchy in question is marked as Visible=false? if this is tha case, then what happens is that cellset data does not contain the Name of the hierarchy, and so when you request this information object model has to issue a schema rowset request for this hierarchy. But it will not be able to get it in case the hierarchy is hidden.

to solve this issue you can set the ShowHiddenObjects property on the AdomdConnection to true (you need to do this before you open the connection). Then the OM would be able to retrieve the metadata for hidden hierarchy as well.

hope this helps,

|||

Thanks again for your reply, but the hierarchy in question is not hidden. This is in an OLAP browser application, so the hierarchy is visible. The ShowHiddenObjects property would not be an option; however, I did try it just to test your theory but received an error. What is the syntax for it? I tried setting "ShowHiddenObjects=true" on the connection string, but on Connect I received this error: "XML for Analysis parser: The ShowHiddenObjects property was not recognized."

Thanks,

Terry

|||

hello Terry,

the code below is a sample. if i comment out the ShowHiddenObjects line, i'll get similar error. With the line present, the code succeeds. (for this test case in the Date dimension, i have the Calendar Quarter attribute marked with AttributeHierarchyVisible=false)

using (AdomdConnection con = new AdomdConnection())
{
con.ConnectionString = "Data Source=localhost; Catalog=Adventure Works DW;";
con.ShowHiddenObjects = true;
con.Open();
using (AdomdCommand cmd = con.CreateCommand())
{
cmd.CommandText = "select [Date].[Calendar Quarter].members on 0 from [Adventure Works]";
CellSet cs = cmd.ExecuteCellSet();
Console.WriteLine(cs.Axes[0].Set.Hierarchies[0].Name);
}
}

to better understand your case, you can also try adding the following code in your original code (without setting ShowHiddenObjects), to see if we will get different row counts.... (using the hierarchy UniqueName returned, we are trying to request a hierarchies schema first without visibility restriction, and then with it; if hierarchy is invivible, it will not be returned by the first call to GetSchemaDataSet, but will be in the second call which has Hierarchy_visibility added)

AdomdRestrictionCollection rest = new AdomdRestrictionCollection();
rest.Add("CUBE_NAME", cs.OlapInfo.CubeInfo.Cubes[0].CubeName);
rest.Add("HIERARCHY_UNIQUE_NAME", cs.Axes[0].Set.Hierarchies[0].UniqueName);

System.Data.DataTable table = con.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", rest).Tables[0];
Console.WriteLine(table.Rows.Count.ToString());

rest.Add("HIERARCHY_VISIBILITY", "3" );
table = con.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", rest).Tables[0];
Console.WriteLine(table.Rows.Count.ToString());

hope this helps,

|||

Thank you Mary!!!

That explains everything. We do indeed have AttributeHierarchyVisible set to false on all of our Date dimension attributes. I'm not sure why at this point, and our UDM designer is out of town this week. However, when I set ShowHiddenObjects = True the hierarchy caption is returned just like you expected.

I will check with our UDM designer to see if there would be are any side-effects in our system to setting AttributeHierarchyVisible = true on our Date attributes. Otherwise, I will have to set ShowHiddenObjects = true when we return cellsets but not whenever we're returning the cube metadata.

Thank you SO MUCH for your help! We have been struggling (and that's an understatement) with SSAS 05 since it was released, and you have provided me with the best help we've ever received from a post on this forum!

Terry

|||

thank you Terry.

i'm glad that this info helped.

thanks again,

Retrieving Hierarchy Captions from a Cellset

I'm trying to retrieve the hierarchy caption(s) for a given axis in a cellset, but whenever I try to retrieve this:

cellset.Axes(0).Set.Hierarchies(0).Name

I get an exception like this:

System.ArgumentException was unhandled
Message="The '[Date].[Calendar Quarter]' object was not found.\r\nParameter name: uniqueName"
Source="Microsoft.AnalysisServices.AdomdClient"
ParamName="uniqueName"
StackTrace:
at Microsoft.AnalysisServices.AdomdClient.CubeDef.InternalGetSchemaObject(SchemaObjectType schemaObjectType, String uniqueName, Boolean retryUniqueName)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetObjectData(SchemaObjectType schemaObjectType, String cubeName, String uniqueName)
at Microsoft.AnalysisServices.AdomdClient.AdomdUtils.PopulateSymetry(IAdomdBaseObject iBaseObject)
at Microsoft.AnalysisServices.AdomdClient.Hierarchy.get_Name()

I've tried setting the MEMBER_NAME dimension property but that doesn't work for this case. Is there another dimension property that I could use?

Thanks,

Terry

hello Terry,

it's not quite clear what's wrong. the following seems to work fine for me:

using (AdomdConnection con = new AdomdConnection())
{
con.ConnectionString = "Data Source=localhost; Catalog=Adventure Works DW;";
con.Open();
using (AdomdCommand cmd = con.CreateCommand())
{
cmd.CommandText = "select [Date].[Calendar].members on 0 from [Adventure Works]"; //"select [Date].[Calendar].[Calendar Quarter].members on 0, [Product].[Product Categories].[Category].Members on 1 from [Adventure Works]";
CellSet cs = cmd.ExecuteCellSet();

Console.WriteLine(cs.Axes[0].Set.Hierarchies[0].Name);
}
}

could you please provide some more info, as to what the calling code and query looks like, what's the version of the AS server, adomd.net that you are using, etc.

Thanks a lot,

|||

Thank you for your reply.

I created a bare-bones test project and verified that it does work on a simple query against Adventure Works but will not work against our own cube.

Does anyone know why we would be getting the exception thrown (as show further above) when simply trying to retrieve the hierarchy's name?

Thank you,

Terry

|||

hello Terry,

my guess is that maybe the hierarchy in question is marked as Visible=false? if this is tha case, then what happens is that cellset data does not contain the Name of the hierarchy, and so when you request this information object model has to issue a schema rowset request for this hierarchy. But it will not be able to get it in case the hierarchy is hidden.

to solve this issue you can set the ShowHiddenObjects property on the AdomdConnection to true (you need to do this before you open the connection). Then the OM would be able to retrieve the metadata for hidden hierarchy as well.

hope this helps,

|||

Thanks again for your reply, but the hierarchy in question is not hidden. This is in an OLAP browser application, so the hierarchy is visible. The ShowHiddenObjects property would not be an option; however, I did try it just to test your theory but received an error. What is the syntax for it? I tried setting "ShowHiddenObjects=true" on the connection string, but on Connect I received this error: "XML for Analysis parser: The ShowHiddenObjects property was not recognized."

Thanks,

Terry

|||

hello Terry,

the code below is a sample. if i comment out the ShowHiddenObjects line, i'll get similar error. With the line present, the code succeeds. (for this test case in the Date dimension, i have the Calendar Quarter attribute marked with AttributeHierarchyVisible=false)

using (AdomdConnection con = new AdomdConnection())
{
con.ConnectionString = "Data Source=localhost; Catalog=Adventure Works DW;";
con.ShowHiddenObjects = true;
con.Open();
using (AdomdCommand cmd = con.CreateCommand())
{
cmd.CommandText = "select [Date].[Calendar Quarter].members on 0 from [Adventure Works]";
CellSet cs = cmd.ExecuteCellSet();
Console.WriteLine(cs.Axes[0].Set.Hierarchies[0].Name);
}
}

to better understand your case, you can also try adding the following code in your original code (without setting ShowHiddenObjects), to see if we will get different row counts.... (using the hierarchy UniqueName returned, we are trying to request a hierarchies schema first without visibility restriction, and then with it; if hierarchy is invivible, it will not be returned by the first call to GetSchemaDataSet, but will be in the second call which has Hierarchy_visibility added)

AdomdRestrictionCollection rest = new AdomdRestrictionCollection();
rest.Add("CUBE_NAME", cs.OlapInfo.CubeInfo.Cubes[0].CubeName);
rest.Add("HIERARCHY_UNIQUE_NAME", cs.Axes[0].Set.Hierarchies[0].UniqueName);

System.Data.DataTable table = con.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", rest).Tables[0];
Console.WriteLine(table.Rows.Count.ToString());

rest.Add("HIERARCHY_VISIBILITY", "3" );
table = con.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", rest).Tables[0];
Console.WriteLine(table.Rows.Count.ToString());

hope this helps,

|||

Thank you Mary!!!

That explains everything. We do indeed have AttributeHierarchyVisible set to false on all of our Date dimension attributes. I'm not sure why at this point, and our UDM designer is out of town this week. However, when I set ShowHiddenObjects = True the hierarchy caption is returned just like you expected.

I will check with our UDM designer to see if there would be are any side-effects in our system to setting AttributeHierarchyVisible = true on our Date attributes. Otherwise, I will have to set ShowHiddenObjects = true when we return cellsets but not whenever we're returning the cube metadata.

Thank you SO MUCH for your help! We have been struggling (and that's an understatement) with SSAS 05 since it was released, and you have provided me with the best help we've ever received from a post on this forum!

Terry

|||

thank you Terry.

i'm glad that this info helped.

thanks again,