AS/OLAP data with RS

Hi,
I'm totally new to reporting services (but worked with mdx/asy2k). Tried
today to build RS reports to an AS database. First, is it true you have to
make a whole mdx query (as in mdx sample application) to create the query
string/dataset ? No help or wizard ? Second, is there a way to get
drill-to-detail-functionality, either in the same list as a more detailed
level or in another list in the same report ?I assume you are asking for Analysis Services 2000 and Reporting Services
2000. Did you check the following MSDN article - it should get you started:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
RS 2005 provides new graphical drag & drop MDX and DMX query designers when
connecting to a AS 2005 or AS 2000 cube. Please check this link for a screen
shot of the MDX query designer:
http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx#EEAA
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"/jerome k" </jerome k@.discussions.microsoft.com> wrote in message
news:3CE8E430-FE18-4B31-AA04-2433D4DBE764@.microsoft.com...
> Hi,
> I'm totally new to reporting services (but worked with mdx/asy2k). Tried
> today to build RS reports to an AS database. First, is it true you have to
> make a whole mdx query (as in mdx sample application) to create the query
> string/dataset ? No help or wizard ? Second, is there a way to get
> drill-to-detail-functionality, either in the same list as a more detailed
> level or in another list in the same report ?
>

AS 2000 with 64 bit OS

I moved one of my AS 2000 olap db new server -Windows 2003 "64-bit" OS with lot more processor then old server "32-bit" Windows OS.

Good news is my cube processing I save 50% time but my query performance is slow, I was thinking my query should fly.

I have AS 2000 "32-bit" in "64-bit" Windows server. I have same settings in AS 2000 as old server.

Thank you - Ashok

Well, first of all, if you running IA 64, you should install 64bit version of Analysis Services and run natively.

Second, running on higher end machine doesnt neccessarily means single query performance is going to improve drastically. With higher-end hardware, you should get higher throughput, ability to handle larger databases.
For single query you might be way better off creating additional aggregations supporting your query patterns. With processing times improving you should be able to afford having higher persentage of aggregations that should improve perofmance.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Are there limitations to OLAP in Excel?

Hi,

Now, I use Analysis Services 2005 and Excel 2003 SP2 to deploy my OLAP model.

In Excel sheet, when creating a pivot table based on a Cube in Analysis Services, everything appears to work until I add a fourth dimension to the pivot table. Once I do that, I get a message that says "Running OLAP Query...(Press Esc to cancel)" so I must wait for a long time (about 5 minutes) to get the result. But while browsing the same data dimensions in Analysis Services Window, everything appears immediately.

Any ideas?

Best Regards!

You might qualify the question to read: "Are there limitations to using AS 2005 cubes in Excel 2003?"

Choosing client for SSAS 2005: Excel 2003 or Excel 2007

By Ramunas Balukonis

After we migrated Analysis Services from 2000 to 2005 we discovered that the same (or analogous) queries against SSAS 2005 runs even slower than AS 2000. Our users used MS Excel 2003 as front end on AS 2005. After some time I installed MS Excel 2007. Then I tried to do the same Pivot Table actions on both Excel versions (2003 and 2007): make Pivot Table from Advenure Works database placing days of [Date].[Calendar] [July 2003] and [August 2003] on rows, [Mountain-200 Silver, 38] and [Mountain-200 Silver, 42] from product [Product].[categories] on columns and [Sales Amount] as facts

Excel 2003 generates cumbersome MDX wits lots of except:

...

Excel 2007 generates elegant MDX:

...

The 1’st query from Excel 2003 runs 5 times longer!

After some time I gathered statistics from profiler and querylog. The results are as follows:

Avg MDX text length (Excel 2003): 2403
Avg MDX text length (Excel 2007): 1567
Avg query duration (Excel 2003): 507 ms
Avg query duration (Excel 2007): 96 ms

|||Thanks Ramunas!
Did you try to use the browser in Analysis Services or the Reporting Services to query your OLAP data . In my opinion, the queried time of using two ways above is better than the time of using Excel pivot table.
Could you explain me the reason why?

|||

Yeah, Excel 2003 is horrible with OLAP. It's the MDX issue, it does tons of EXCEPTS(), etc. Instead of scripting what it wants in the pivottable, it scripts what it doesn't want. Not sure how this ever got passed.

For your last reply, you will be hitting cache if you run from Excel, then run the same query somewhere else. You would have to execute the ClearCache command after every MDX statement for a true test. Or could be a network slowup if your using Excel externally (off the server) and running Management Studio on the server directly, for example.

Are there limitations to OLAP in Excel?

Hi,

Now, I use Analysis Services 2005 and Excel 2003 SP2 to deploy my OLAP model.

In Excel sheet, when creating a pivot table based on a Cube in Analysis Services, everything appears to work until I add a fourth dimension to the pivot table. Once I do that, I get a message that says "Running OLAP Query...(Press Esc to cancel)" so I must wait for a long time (about 5 minutes) to get the result. But while browsing the same data dimensions in Analysis Services Window, everything appears immediately.

Any ideas?

Best Regards!

You might qualify the question to read: "Are there limitations to using AS 2005 cubes in Excel 2003?"

Choosing client for SSAS 2005: Excel 2003 or Excel 2007

By Ramunas Balukonis

After we migrated Analysis Services from 2000 to 2005 we discovered that the same (or analogous) queries against SSAS 2005 runs even slower than AS 2000. Our users used MS Excel 2003 as front end on AS 2005. After some time I installed MS Excel 2007. Then I tried to do the same Pivot Table actions on both Excel versions (2003 and 2007): make Pivot Table from Advenure Works database placing days of [Date].[Calendar] [July 2003] and [August 2003] on rows, [Mountain-200 Silver, 38] and [Mountain-200 Silver, 42] from product [Product].[categories] on columns and [Sales Amount] as facts

Excel 2003 generates cumbersome MDX wits lots of except:

...

Excel 2007 generates elegant MDX:

...

The 1’st query from Excel 2003 runs 5 times longer!

After some time I gathered statistics from profiler and querylog. The results are as follows:

Avg MDX text length (Excel 2003): 2403
Avg MDX text length (Excel 2007): 1567
Avg query duration (Excel 2003): 507 ms
Avg query duration (Excel 2007): 96 ms

|||Thanks Ramunas!
Did you try to use the browser in Analysis Services or the Reporting Services to query your OLAP data . In my opinion, the queried time of using two ways above is better than the time of using Excel pivot table.
Could you explain me the reason why?

|||

Yeah, Excel 2003 is horrible with OLAP. It's the MDX issue, it does tons of EXCEPTS(), etc. Instead of scripting what it wants in the pivottable, it scripts what it doesn't want. Not sure how this ever got passed.

For your last reply, you will be hitting cache if you run from Excel, then run the same query somewhere else. You would have to execute the ClearCache command after every MDX statement for a true test. Or could be a network slowup if your using Excel externally (off the server) and running Management Studio on the server directly, for example.