Count of dimension in cube; levels in dimension

Count of dimension in cube; levels in dimension

Post by 9Finge » Wed, 30 Apr 2003 03:03:40



I want to find out all the dimensions in a given cube, and all the
levels in each dimension. I run this query:

with member [Measures].[test] as 'Dimensions(1).uniquename'
        member [Measures].[test1] as
'Dimensions(1).levels(0).uniquename'
        member [Measures].[test2] as
'Dimensions(1).levels(1).uniquename'
select  {[Measures].[test], [Measures].[test1], [Measures].[test2]} on
columns from [Warehouse and Sales]

which returns:

[Store] [Store].[(All)] [Store].[Country]

However, I need to know how many Dimensions are in the cube, and how
many levels in each dimension. Otherwise, for example, to get all the
dimensions in
[Warehouse and Sales] I would have to keep doing queries with
Dimensions(n), incrementing n until a query failed. How can I find out
what the highest value of n is?

Thank you.

 
 
 

Count of dimension in cube; levels in dimension

Post by Sean Boon [MS » Wed, 30 Apr 2003 03:12:22


Hi David,

The best way to get this information would be via ADOMD and not by running
queries.  Look at the topic "Using the CubeDef Object" in SQL Server Books
on line.

Sean

--
Sean Boon
SQL BI Product Unit

**This posting is provided AS IS, with no warranties, and confers no
rights.**


Quote:> I want to find out all the dimensions in a given cube, and all the
> levels in each dimension. I run this query:

> with member [Measures].[test] as 'Dimensions(1).uniquename'
>         member [Measures].[test1] as
> 'Dimensions(1).levels(0).uniquename'
>         member [Measures].[test2] as
> 'Dimensions(1).levels(1).uniquename'
> select  {[Measures].[test], [Measures].[test1], [Measures].[test2]} on
> columns from [Warehouse and Sales]

> which returns:

> [Store] [Store].[(All)] [Store].[Country]

> However, I need to know how many Dimensions are in the cube, and how
> many levels in each dimension. Otherwise, for example, to get all the
> dimensions in
> [Warehouse and Sales] I would have to keep doing queries with
> Dimensions(n), incrementing n until a query failed. How can I find out
> what the highest value of n is?

> Thank you.


 
 
 

Count of dimension in cube; levels in dimension

Post by David Cohe » Wed, 30 Apr 2003 04:15:08


Sean,

Thanks. However, we are trying to avoid using ADOMD in this situation.

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Count of dimension in cube; levels in dimension

Post by Dave Wickert [MSFT » Wed, 30 Apr 2003 08:00:58


How about ADO.NET?
What you want to use is schema rowsets.
Here's how to get a listing of what OLAP databases are on a server. It is a
simple extension to look at the cubes.
See BOL under "Schema Rowsets" and "OLAP Schema Rowsets".

cross-posted from microsoft.public.data.oledb.olap by ChrisH: 4/17/2003

---------------------------

Private Sub DatabaseList(ByVal sServerName As String)
   Dim cnActive As New ADODB.Connection()
   Dim rs As ADODB.Recordset
   Dim strCnxn As String

   cnActive = New ADODB.Connection()
   strCnxn = "Provider=msolap.2;Data Source=" & sServerName & ";Integrated
Security='SSPI';"
   cnActive.Open(strCnxn)
   rs = cnActive.OpenSchema(ADODB.SchemaEnum.adSchemaCatalogs)
   Do Until rs.EOF
       sCatalogCur = rs.Fields.Item(0).Value
       Debug.WriteLine(sCatalogCur)
       rs.MoveNext()
   Loop
End Sub

----------------

Hope this helps.

--
Dave Wickert [MSFT]

Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


Quote:> Sean,

> Thanks. However, we are trying to avoid using ADOMD in this situation.

> David

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

1. Create a virtual cube from two cubes with different time dimensions(levels)

Hello,

We have two cubes:
1. Cube 1 has a time dimension with the levels year-month
2. Cube 2 has a time dimension with the levels year-month-day

We want to create a virtual cube from these two cubes. We tried to create a
new shared time dimension Year-Month-Day and changed the member property
"Disabled" of the Day level to true when using the dimension in Cube 1. But
the aggregated sum of the measure at the Month level in the cube is still
wrong. Does anyone know how the property "Disabled" is supposed to work?

Disabled means - "Determines whether the level is disabled. Disabled levels
are not available in the cube. They are used when the most detailed level of
data is not available in the fact table"..

We can fix this in the fact table behind Cube 1 (add the day 01 to every
time member), but we would like to do it in the cube.

Any suggestions?

/Anneli

2. CREATING TRIGGER ERROR

3. Dimension level member count problem

4. InterBase NOT NULL Attribute

5. Count members at each level of a dimension

6. Oracle8 for Linux Installation - SHHMAX, etc.

7. error counting level members whilst building dimensions

8. SP3

9. migrating databases and cubes with shared dimension level security between servers

10. Custom Rollups in Virtual Cube Dimension Levels

11. processing a cube with disabled dimension levels

12. MDX for last(leaf) level level in the dimension

13. how to split one dimension into two dimensions