Optimized CellSet data Retrieval

Hi,

I have customized report for displaying OLAP CellSet data on the web. It is using ADOMD.NET, and showing everything in pivot table format. I am not using OWC or any other tool, it is my company's tool.

I notice Cognos PowerPlay has this feature, "GetDataLater", which means, when user presses this button, all data will be suppressed and only member names on column and rows will be shown with correct dimensionality. It is very fast to drill down, nest/crossjoin with other dimension after data is being suppressd.

I researched in ADOMD.NET specification for such behavior, but could not find. I found that if i get two Axes spearately, Row and Column, not putting them together is faster than getting both Axes in same CellSet. But still performace of such work around is almost unnoticeable once user puts more nested/crossjoin dimensions.

So, is there way to achieve it in ADOMD.NET or some feature in SSAS 2005, that suppresses the data whenever needed thus improving performance of CellSet dramatically.

Regards,

Bakhodir Makhamadov

There are several techniques how this can be done - probably the best technique is through using BeginRange/EndRange properties to define empty cell, because it allows NON EMPTY clause to work correctly (although if you are using NON EMPTY, you are probably not winning any time by not bringing the cells back).

(For more ideas please check the last section of this blog. It is not directly related to your question, but the ideas are applicable here as well: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/11/query_dimensions_mdx.aspx)

|||

Hi Mosha,

Thanks for information on BeginRange/EndRange properties, looks like i have missed it on XMLA Specifications.

The suggestion with BeginRange/EndRange on the XMLA restricting the number of CellData being returned, is the solution i am looking for. It satisfies my requirement in terms of functionality, since i am able to control the number of celldata being returned. I did some simulaiton code with these properties on XMLA, it works.

I am curious, if these properties are used does SSAS processes my MDX script faster. Do i get huge performace gain by restricting the celldata and retrieving only metadata of Column and Row Axes.

As part of the SSAS internal team, i believe you should have good idea of what exactly happens in SSAS when these properties are used.

I am enclosing one of the MDX scripts used in OLAP report designing:

select

{

NonEmpty

(

{[Network Product].[Network Product].[All].children}

*

{

[Estimated TT Impact].[Estimated TT Impact].[All].[0.5 day],

[Estimated TT Impact].[Estimated TT Impact].[All].[1.5 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[2.0 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[0.0 day]

}

*

{

[Measures].[Total Shipments],

[Measures].[% of Shipments Delivered]

}

)

}

on 0,

{

NonEmpty

(

{[Account].[Account].[All].children}

*

{[Shipment Weight].[Shipment Weight].[All].children}

)

}

on 1

from [Expected Delivery Standard]

The above MDX query generates more than 1000 row members as [Account] dimension has many children, and as you can see it is being crossjoined with other dimensions.

So, aside from functionality i need, restricting/suppressing data, do I get big performace gain when using BeginRang/EndRange properties?

Thank you in advance,

Regards,

Bakhodir Makhamadov

sql

Optimized CellSet data Retrieval

Hi,

I have customized report for displaying OLAP CellSet data on the web. It is using ADOMD.NET, and showing everything in pivot table format. I am not using OWC or any other tool, it is my company's tool.

I notice Cognos PowerPlay has this feature, "GetDataLater", which means, when user presses this button, all data will be suppressed and only member names on column and rows will be shown with correct dimensionality. It is very fast to drill down, nest/crossjoin with other dimension after data is being suppressd.

I researched in ADOMD.NET specification for such behavior, but could not find. I found that if i get two Axes spearately, Row and Column, not putting them together is faster than getting both Axes in same CellSet. But still performace of such work around is almost unnoticeable once user puts more nested/crossjoin dimensions.

So, is there way to achieve it in ADOMD.NET or some feature in SSAS 2005, that suppresses the data whenever needed thus improving performance of CellSet dramatically.

Regards,

Bakhodir Makhamadov

There are several techniques how this can be done - probably the best technique is through using BeginRange/EndRange properties to define empty cell, because it allows NON EMPTY clause to work correctly (although if you are using NON EMPTY, you are probably not winning any time by not bringing the cells back).

(For more ideas please check the last section of this blog. It is not directly related to your question, but the ideas are applicable here as well: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/11/query_dimensions_mdx.aspx)

|||

Hi Mosha,

Thanks for information on BeginRange/EndRange properties, looks like i have missed it on XMLA Specifications.

The suggestion with BeginRange/EndRange on the XMLA restricting the number of CellData being returned, is the solution i am looking for. It satisfies my requirement in terms of functionality, since i am able to control the number of celldata being returned. I did some simulaiton code with these properties on XMLA, it works.

I am curious, if these properties are used does SSAS processes my MDX script faster. Do i get huge performace gain by restricting the celldata and retrieving only metadata of Column and Row Axes.

As part of the SSAS internal team, i believe you should have good idea of what exactly happens in SSAS when these properties are used.

I am enclosing one of the MDX scripts used in OLAP report designing:

select

{

NonEmpty

(

{[Network Product].[Network Product].[All].children}

*

{

[Estimated TT Impact].[Estimated TT Impact].[All].[0.5 day],

[Estimated TT Impact].[Estimated TT Impact].[All].[1.5 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[2.0 days],

[Estimated TT Impact].[Estimated TT Impact].[All].[0.0 day]

}

*

{

[Measures].[Total Shipments],

[Measures].[% of Shipments Delivered]

}

)

}

on 0,

{

NonEmpty

(

{[Account].[Account].[All].children}

*

{[Shipment Weight].[Shipment Weight].[All].children}

)

}

on 1

from [Expected Delivery Standard]

The above MDX query generates more than 1000 row members as [Account] dimension has many children, and as you can see it is being crossjoined with other dimensions.

So, aside from functionality i need, restricting/suppressing data, do I get big performace gain when using BeginRang/EndRange properties?

Thank you in advance,

Regards,

Bakhodir Makhamadov

Optimization in retrieval using indexes

I am posting this question here because of the fact that it has more to do with database theory than a particular product.

Supposing i have a table TABLE1(ID,ID2,field1,field2,...,fieldn)

The primary key is ID.
There is a secondary index on ID2. ID2 is a candidate key.
These are the only indices.

1)Do modern database systems like Oracle,DB2,SQL Server provide a way to execute the following query

SELECT * FROM TABLE1 ORDER BY ID

in approximately the same time taken for the foll. query

SELECT * FROM TABLE1

Particularly if the primary index (or file organisation) can be organised in a way (maybe i choose the file/index orgn. offered by the DB) so that the above is achieved.

Any help is appreciated.
Thanks in advance.For Oracle (any version > V7), your PK must have an index and
your other key, you say, is indexed so if you order by either of
these columns that are indexed, the query will not take very much
longer that your #2 statement.

You mention "approximately" so... this'll really have to do with
how many records are in the database. If all you have is a four (4)
column table with, lets say, < 5,000 rows, querying that table
and accessing index columns (in either the "where" or "Order by"
clause, will be very fast.

For exact results and complete details as to what the query will
do, you'd need to "anaylze" the query.|||One more doubt please.
With ref. to the first post,

1)SELECT * FROM TABLE1 WHERE ID>M AND ID<N

AND

2)SELECT * FROM TABLE1 WHERE ID2>M AND ID2<N

Won't 1) execute very much faster coz of using a primary index that matches the file organisation leading to virtually sequential retrieval?|||It also largly depends on your optimizer mode.
If you are using COST Based Optimisation, then the oracle database internally calculates the load and decide on wether to use the index or not.sql

Optimization

I just wanna know what is the optimized way for fast retrieval if there are
billions of rows in a single table.
Can any one let me know any idea except table partitioning for the fast
retrieval of data
Thanks in advance.
Hi
Optimization needs to take into account the whole environment of your
application and be part of your design. This will include hardware as well
as the software. Partitioned views (see Books online) may be an option if
you have the hardware to run them on, if you are not wanting to change the
design look at what indexes are available. Other things to look at would be
using table variables or temporary to reduce the size of data you are trying
to manipulate.
John
"John" <naissani@.hotmail.com> wrote in message
news:%23Hhk6pJMFHA.1472@.TK2MSFTNGP14.phx.gbl...
>I just wanna know what is the optimized way for fast retrieval if there are
> billions of rows in a single table.
> Can any one let me know any idea except table partitioning for the fast
> retrieval of data
> Thanks in advance.
>
|||Thanks John but can you explain me more about Other things to look at would
be using table variables or temporary to reduce the size of data you are
trying
to manipulate.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:#vpfVfSMFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi
> Optimization needs to take into account the whole environment of your
> application and be part of your design. This will include hardware as well
> as the software. Partitioned views (see Books online) may be an option if
> you have the hardware to run them on, if you are not wanting to change the
> design look at what indexes are available. Other things to look at would
be
> using table variables or temporary to reduce the size of data you are
trying[vbcol=seagreen]
> to manipulate.
> John
> "John" <naissani@.hotmail.com> wrote in message
> news:%23Hhk6pJMFHA.1472@.TK2MSFTNGP14.phx.gbl...
are
>
|||Hi
By using a temporary table to restrict the data you are looking at, it
may be possible to create a more proformant than it would be otherwise.
e.g
http://www.windowsitpro.com/SQLServe...hreadid=102472
With SQL 2000 table variables were introduced as an alternative to
temporary tables.
http://www.windowsitpro.com/Articles...layTab=Article
Using temporary tables may introduce it's own problems
http://www.sql-server-performance.co...ottlenecks.asp
and using them unnecessarily may also degrade performance
http://www.sql-server-performance.co...ved_tables.asp
Therefore you would have to try out different methods to see what is
the best in your circumstances.
John
John wrote:
> Thanks John but can you explain me more about Other things to look at
would
> be using table variables or temporary to reduce the size of data you
are[vbcol=seagreen]
> trying
> to manipulate.
> Thanks.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:#vpfVfSMFHA.1176@.TK2MSFTNGP12.phx.gbl...
your[vbcol=seagreen]
as well[vbcol=seagreen]
option if[vbcol=seagreen]
change the[vbcol=seagreen]
would[vbcol=seagreen]
> be
are[vbcol=seagreen]
> trying
there[vbcol=seagreen]
> are
the fast[vbcol=seagreen]