can we export olap cubes from sql server 2005 analysis service to sql server 2000 analysis servi

Dear all,

Thank you very much for your kind attention.

Is it possible to export olap cubes from sql server 2005 analysis service to sql server 2000 analysis service? And if it is possible, then how can it be done? Really need help for this and thank you very much in advance for your help.

I am looking forward to hearing from you.

With best regards,

Yours sincerely,

Helen

No, it isn't possible to export cubes from AS2005 to AS2K. You'll have to rebuild them from scratch, I'm afraid.

Regards,

Chris

|||

Hi, Chris,

Thank you for your advice.

With best regards,

Yours sincerely,

Helen

Can we automate and schedule Analysis Services Databases Back up and Restore Actions?

Hi, all here,

Would please anyone here give me any advice about wether or not we can automate and schedule the Analysis Services databases backup and restore actions?

Thanks a lot in advance for any guidance and help for that.

With best regards,

You just have to create a XMLA backup command, somethink like this:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDBId</DatabaseID>
</Object>
<File>c:\MyDB.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

Then create a new SQL Server Agent job of type "SQL Server Analysis Services Command" and schedule it as needed.

See:

http://msdn2.microsoft.com/en-us/library/ms186658.aspx

and

http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx

Hope this helps,

Santi

|||

Hi, Santiago, thank you very much. Got it done.

|||Hi, this is great. But I want to schedule a SQL Agent job that loops through the list of all Analysis services databases and automatically applies the xmla to the current database.

e.g. to do this with normal dbs in TSQL you could loop through all the databases from master.dbo.sysdatabases and store the current db as a parameter.

How can pass parameters to XMLA? and if parameters aren't possible, how can you backup multiple dbs from a single script? e.g. the following doesnt work if you execute both at the same time.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>|||anyone?|||

You can wrap multiple commands inside a Batch. But remember that XMLA is a general purpose scripting language like TSQL. It does not have control flow, branching, etc.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
</Batch>

|||Thanks T.K Anand for your reply. The syntax is definitely correct, but when I try running the above batch statement I get the error:

Executed as user: Domain\username. <return xmlns="urnchemas-microsoft-com:xml-analysis"><results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urnchemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urnchemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urnchemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="3239968805" Description="Backup and restore errors: Neither Backup/Restore nor Synchronize command can be invoked in a user initiated transaction." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" /></Messages></root></results></return>. The step succeeded.

I tried scheduling this as a SQL Server Agent job, but the same error occurs and there is no backup file in the location that I specified?

Can we automate and schedule Analysis Services Databases Back up and Restore Actions?

Hi, all here,

Would please anyone here give me any advice about wether or not we can automate and schedule the Analysis Services databases backup and restore actions?

Thanks a lot in advance for any guidance and help for that.

With best regards,

You just have to create a XMLA backup command, somethink like this:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDBId</DatabaseID>
</Object>
<File>c:\MyDB.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

Then create a new SQL Server Agent job of type "SQL Server Analysis Services Command" and schedule it as needed.

See:

http://msdn2.microsoft.com/en-us/library/ms186658.aspx

and

http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx

Hope this helps,

Santi

|||

Hi, Santiago, thank you very much. Got it done.

|||Hi, this is great. But I want to schedule a SQL Agent job that loops through the list of all Analysis services databases and automatically applies the xmla to the current database.

e.g. to do this with normal dbs in TSQL you could loop through all the databases from master.dbo.sysdatabases and store the current db as a parameter.

How can pass parameters to XMLA? and if parameters aren't possible, how can you backup multiple dbs from a single script? e.g. the following doesnt work if you execute both at the same time.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>|||anyone?|||

You can wrap multiple commands inside a Batch. But remember that XMLA is a general purpose scripting language like TSQL. It does not have control flow, branching, etc.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
</Batch>

|||Thanks T.K Anand for your reply. The syntax is definitely correct, but when I try running the above batch statement I get the error:

Executed as user: Domain\username. <return xmlns="urnchemas-microsoft-com:xml-analysis"><results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urnchemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urnchemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urnchemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="3239968805" Description="Backup and restore errors: Neither Backup/Restore nor Synchronize command can be invoked in a user initiated transaction." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" /></Messages></root></results></return>. The step succeeded.

I tried scheduling this as a SQL Server Agent job, but the same error occurs and there is no backup file in the location that I specified?
sql

Can we automate and schedule Analysis Services Databases Back up and Restore Actions?

Hi, all here,

Would please anyone here give me any advice about wether or not we can automate and schedule the Analysis Services databases backup and restore actions?

Thanks a lot in advance for any guidance and help for that.

With best regards,

You just have to create a XMLA backup command, somethink like this:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDBId</DatabaseID>
</Object>
<File>c:\MyDB.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

Then create a new SQL Server Agent job of type "SQL Server Analysis Services Command" and schedule it as needed.

See:

http://msdn2.microsoft.com/en-us/library/ms186658.aspx

and

http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx

Hope this helps,

Santi

|||

Hi, Santiago, thank you very much. Got it done.

|||Hi, this is great. But I want to schedule a SQL Agent job that loops through the list of all Analysis services databases and automatically applies the xmla to the current database.

e.g. to do this with normal dbs in TSQL you could loop through all the databases from master.dbo.sysdatabases and store the current db as a parameter.

How can pass parameters to XMLA? and if parameters aren't possible, how can you backup multiple dbs from a single script? e.g. the following doesnt work if you execute both at the same time.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>|||anyone?|||

You can wrap multiple commands inside a Batch. But remember that XMLA is a general purpose scripting language like TSQL. It does not have control flow, branching, etc.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
</Batch>

|||Thanks T.K Anand for your reply. The syntax is definitely correct, but when I try running the above batch statement I get the error:

Executed as user: Domain\username. <return xmlns="urnchemas-microsoft-com:xml-analysis"><results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urnchemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urnchemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urnchemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="3239968805" Description="Backup and restore errors: Neither Backup/Restore nor Synchronize command can be invoked in a user initiated transaction." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" /></Messages></root></results></return>. The step succeeded.

I tried scheduling this as a SQL Server Agent job, but the same error occurs and there is no backup file in the location that I specified?

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.Yes, for such a high-selectivity query, an index can be used. SQL Server wou
ld use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the
pointer in that index
to go fetch the data row, in descending order. The lower selectivity you hav
e, the less of a perf
gain you see by using the index, as a data page has to be visited *for each
row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) a
nd then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even th
e FIRST hint and also
make sure that statistics are up to date.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegroups.c
om...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.
Yes, for such a high-selectivity query, an index can be used. SQL Server would use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the pointer in that index
to go fetch the data row, in descending order. The lower selectivity you have, the less of a perf
gain you see by using the index, as a data page has to be visited *for each row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) and then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even the FIRST hint and also
make sure that statistics are up to date.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegr oups.com...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.Yes, for such a high-selectivity query, an index can be used. SQL Server would use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the pointer in that index
to go fetch the data row, in descending order. The lower selectivity you have, the less of a perf
gain you see by using the index, as a data page has to be visited *for each row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) and then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even the FIRST hint and also
make sure that statistics are up to date.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegroups.com...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>

Can not show all level in analysis manager MSSQL Server 2000

Hi,

I use MSsql server 2000 and analysis service for create cube.But when I drilldown many Dimension on Analysis manager it can not show all level.
If I drilldown some level it can show data but when I drilldown many level so it have many row (I test about 60,000) it can not show result.
How can I show all result in Analysis manager or other tool ?

Thank you : )I sometimes use MDX sample aplication i got with the installation of Analysys Services to run MDX queries on cube data.

If you simply want to browse some specific portion of the data, maybe it would help to write MDX queries specifying smaller subsets of the data?