AS2005 Redistributable DLLs

I am referencing Microsoft.AnalysisServices.dll in my C# project to get to the sweet AS2005 object model for automated cube processing among other things but know I need to start getting the deployment of these DLLs figured out for client releases. Currently our installs are deployed to relative paths on the client machines and we have avoided any GAC installations. So my questions revolve around - Are we free to redistribute the Microsoft.AnalysisServices.dll? What other dependencies does it have or redistribution requirements Microsoft may have for it

Thanks in advance for any help or if someone could point me into the correct direction

PS - I have been extremely impressed with the programmability and ease of deployment of AS2005 databases in these latest revs - well done, I am a huge fan of the toolset and the exposed technology.

Brad Osterloo

Probably Microsoft.AnalysisServices.XmlaClient.dll will be enough.|||

1. Microsoft.AnalysisServices.dll does't depends on Microsoft.AnalysisServices.XmlaClient.dll

2. If you want to have a compression in XMLA over TCP, your schould have msasxpress.dll

in %PROGRAMFILES%\Microsoft SQL Server\90\shared\

|||

It took me a bit while before I looked at your thread.

I would try and explan the general policy for redistibuting Analysis Services components:

You cannot redistibute any single file, or group of files with your application. It is going to create huge mess of files with different versions copied all over on user's machine. Also known as: "dll hell".

To redistribute Analysis Services functionlity you should use Redstributable packages found on http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

The one you need in this particular case is "Microsoft SQL Server 2005 Management Objects Collection "

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

|||OK, Thank you Edward for your response. Much appreciated.

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.

are there anything wrong?

update Countries set CountryName = XMLTable.CountryName,Region = XMLTable.Region, HPEntity = XMLTable.HPEntity, Model = XMLTable.Model, Incoterms = XMLTable.Incoterms, IncotermsDesc = XMLTable.IncotermsDesc, DestCountry = XMLTable.DestCountry
from
(
select CountryCode,
CountryName,
Region,
HPEntity,
Model,
Incoterms,
IncotermsDesc,
DestCountry
from OPENXML(@.xmldoc, N'/Countries/Country')
with ( CountryCode char(2) '@.id',
CountryName varchar(50) 'CountryName',
Region varchar(10) 'Region',
HPEntity varchar(2) 'HPEntity',
Model varchar(50) 'Model',
Incoterms char(3) 'IncotermOffer',
IncotermsDesc varchar(50) 'IncotermDescription',
DestCountry char(2) 'DestinationCountry/@.id')) as XMLTable
where Countries.CountryCode = XMLTable.CountryCode

Are you getting an error when you try to execute this DML? If so, what is the error?

are there anything wrong?

update Countries set CountryName = XMLTable.CountryName,Region = XMLTable.Region, HPEntity = XMLTable.HPEntity, Model = XMLTable.Model, Incoterms = XMLTable.Incoterms, IncotermsDesc = XMLTable.IncotermsDesc, DestCountry = XMLTable.DestCountry
from
(
select CountryCode,
CountryName,
Region,
HPEntity,
Model,
Incoterms,
IncotermsDesc,
DestCountry
from OPENXML(@.xmldoc, N'/Countries/Country')
with ( CountryCode char(2) '@.id',
CountryName varchar(50) 'CountryName',
Region varchar(10) 'Region',
HPEntity varchar(2) 'HPEntity',
Model varchar(50) 'Model',
Incoterms char(3) 'IncotermOffer',
IncotermsDesc varchar(50) 'IncotermDescription',
DestCountry char(2) 'DestinationCountry/@.id')) as XMLTable
where Countries.CountryCode = XMLTable.CountryCode

Are you getting an error when you try to execute this DML? If so, what is the error?

are sqlclr stored procs subject to the same SQL Execution Model as other sessions?

are sqlclr stored proc sessions running in the same SQLOS execution model (Running, runnable and suspended status, Runnable Queue and Wait List) as other types of sessions and therefore ending up in the same queues, contending for very similar resources (except maybe for memory type because of MTL's role) etc?...or do they get treated in a very different way?

They are part of the execution model.

Niels