Retrieving Schema Info in MDX
Daren
and dims by using ADO, but I was wondering if there was a way to retrieve
this information using just MDX?
We have a Java application and middle tier and our "current" goal is to
remain that way as much as we can. I figured that we could put an SP in SQL
that would call a com component that would in turn get the info and set it
in a temp table to returned by the SP in a Tabular Data Stream to the JDBC
driver. For MDX queries we are currently using a linked OLAP server,
through SQL Server.
Does anyone have any experience with any of these things? I would
appreciate any thoughts or stories that anyone might have.
Also, we looked at XMLA, but the problem is that XML is such a pig. It
bloats the return data sets to a size that is so much bigger than the data
itself that I am afraid to use it.
Anyway, thanks in advance for any help with this.
Daren Bieniek
Mosha Pasumansky [MS]
Which schemas do you need, and what information from schemas do you need.
Based on the following comment
> Also, we looked at XMLA, but the problem is that XML is such a pig. It
> bloats the return data sets to a size that is so much bigger than the data
> itself that I am afraid to use it.
I assume you are mostly interested in the MEMBERs schema, because all others
are small, and cannot possibly be called "bloat".
Most of the information available in the MEMBERs schema can be obtained
through MDX. For example
SELECT
Customers.MEMBERS DIMENSION PROPERTIES ... ON 0
{} ON 1
FROM <cube>
Will give you no cells, but all the members of the Customers dimension with
the dimension properties specified in the DIMENSION PROPERTIES clause.
HTH,
Mosha.
--
==================================================
Mosha Pasumansky (moshap at microsoft dot com)
Development Lead in the Microsoft Analysis Server team
More info at http://www.mosha.com/msolap
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================
"Daren" <da...@righthandmanager.com> wrote in message
news:uf4HwmjoCHA.1664@TK2MSFTNGP10...
Daren
and what the dimension(s) are attached to said cube(s). The same things
that you would get by using ADO and OpenSchema, but done in pure MDX. For
example in SQL Server, if I need schema info, I can directly query the
sysobjects, etc. tables. I do not need to retrieve the schema info other
ways, although other ways are better, I admit.
On a separate note, I was hoping to get feedback on our methodology for
access to Analysis Services. Our "current" goal is to minimize the non-java
work that is being done. I know that there are multiple ways that we could
do this, like XMLA, Web Service, using JDBC to run Stored Procedures that in
turn call a com object that gets the data, flattens the result set and puts
it in a temp table and the SP simply returns the results from the temp
table, or (as we are currently doing) by using JDBC to query a linked OLAP
server, through SQL Server. I was wondering what others have tried in the
past and what their experiences were like.
My final comment was that we looked at XMLA, but we are not keen on the idea
that thousands of rows might be returned embedded in an XML document.
Thousands of rows in a tabular dataset are bad enough, but to then more
than double the size of the data being returned by adding all of the XML
tags, is not acceptable. I see XML as useful for moving small pieces of
data, but when it comes to real sizable data, XML is a pig. I mean, to
return the integer value of 1 in a field called intPurchaseFactID would take
"<intPurchaseFactID>1</intPurchaseFactID>" which is about 40 bytes instead
of the 2 bytes that would be needed to transfer "1,". This makes the data
20 times larger than it needs to be. This affects Network I/O, Disk. I/O,
Memory (Cache) efficiency, and CPU utilization. Using all of these
resources substantially more than is necessary.
My personal experience is in building multi-terabyte databases including
data warehouses (10+ TB), ROLAP cubes (4+ TB), and data repositories (20+
TB), all on SQL Server. Therefore, I have a keen focus on efficiency.
Any comments about any of these points would be welcomed, but my primary
question is about the schema retrieval using MDX.
Thanks
Daren Bieniek, MCDBA, MCSE(4.0 & 2000)
"Mosha Pasumansky [MS]" <mos...@online.microsoft.com> wrote in message
news:#nSCmUvoCHA.2276@TK2MSFTNGP12...
Mosha Pasumansky [MS]
> and what the dimension(s) are attached to said cube(s). The same things
> that you would get by using ADO and OpenSchema, but done in pure MDX.
MDX always works with the cubes, so it is not possible to obtain list of the
cubes through MDX.
If you already know the cube, you can get some information about dimensions
and levels, i.e.
WITH MEMBER Measures.X AS Dimensions.Count
SELECT {X} ON 0 FROM Sales
Will give you count of dimensions. After that you can do something like
WITH MEMBER Measures.X AS Dimensions(0).Name
SELECT {X} ON 0 FROM Sales
to get the name of the dimension, and
WITH MEMBER Measures.X AS Dimensions(0).Levels.Count
SELECT {X} ON 0 FROM Sales
to get how many levels this dimension has.
You could do couple of tricks with Generate() function, to get information
about all dimensions in a single query.
> On a separate note, I was hoping to get feedback on our methodology for
> access to Analysis Services. Our "current" goal is to minimize the
non-java
> work that is being done.
...
> or (as we are currently doing) by using JDBC to query a linked OLAP
> server, through SQL Server.
This approach seems reasonable, although not optimal (because of the extra
step you need to do). I guess, you cannot avoid doing some schemas (for the
list of cubes at least), but this is really minimal work. You can still do
it through the same linked OLAP server. You don't even need to do
MDSCHEMA_CUBES, you can get away with DBSCHEMA_TABLES, because cubes will
get exposed as tables as well.
HTH,
Mosha