Comments for Vidas Matelis Analysis Services Blog

Comment on SSIS Package to drop/create partitions based on partition list in the SQL Server table by chaitanya

chaitanya — Fri, 26 Jun 2009 04:53:10 +0000

The code is very usefull.

Comment on Script to backup Analysis Services 2005 databases by Sunny

Sunny — Tue, 16 Jun 2009 23:07:42 +0000

Hi Vidas,

Thanks for your help.

I have one more query,
How can we schedule to remove 10 days old these Analysis Services backup files?

Thanks
Sunny

Comment on Script to backup Analysis Services 2005 databases by Sunny

Sunny — Mon, 15 Jun 2009 23:04:52 +0000

Hi,

Can you please send this dtsx package on my email id sunnysqldba@gmail.com?

Thanks in advance.

Sunny

Comment on Script to backup Analysis Services 2005 databases by Richard R

Richard R — Thu, 11 Jun 2009 13:10:34 +0000

Hi,
The Microsoft.AnalysisServices namespace does not seem to be registered on the server I am using, although it has a full installation of SQL/AS 2005 Enterprise on it. Any ideas where I can find the component?

Comment on How to use MDW to collect Analysis Services 2008 performance counters by Itay Braun

Itay Braun — Wed, 03 Jun 2009 12:22:00 +0000

When I tried to collect the data using MSAS 2008:objectname it failed

On my server I noticed immediately the objects names of the “PROD” instance are: MSOLAP$PROD:objectname

This one worked.

Comment on Script to backup Analysis Services 2005 databases by santosh

santosh — Fri, 22 May 2009 06:14:16 +0000

Please send me the project on my email id its very urgetn, I am not able to download the same..
my mail id is santosh.patna.net@gmail.com

Comment on SSIS: Issue with OLE DB Source component that is based on parametrized SQL Query by Vamshi

Vamshi — Wed, 20 May 2009 21:23:12 +0000

Hi Surendra

I had a similar issue where in

select…
from..
where Changecontrol > cast(cast(? as bigint) as binary(8))

doesnt return any records…

This is because,
when you assign MAX(TimeStampColumnValue) to your parameter, it will assign it as a Byte[].

No matter you CAST or CONVERT that to BIGINT and/or VARCHAR or BINARY or VARBINARY it will still remain as Byte[]

Here is what i did to resolve this issue

I have declared a variable in SSIS package

LastChangeControl as Object (System.Object) in SSIS

in the ExecuteSQLTask, I have this query
“SELECT MAX(ISNULL(ChangeControl)) FROM xxxTableName” and assigned to the SSIS variable

Now, my SSIS variable LastChangeControl is a Byte[] with appropriate value distributed in Bytes Array

in the OLE DB Source I have selected a Statement/Query and added SQL Query like this

SELECT * FROM yyyTableName WHERE ChangeControl > ?

If you can run the Profiler for the above statement (while executing from SSIS), you can see ? coming as a Timestamp and not as VARCHAR/BIGINT/BINARY/VARBINARY

So, when you decalre your SSIS variable as Object you dont need explicit CAST or CONVERT functions.

This should work, infact it worked for me…

Comment on SQL Server 2008 Management Data Warehouse by Varun

Varun — Fri, 15 May 2009 20:49:22 +0000

Is there a way I can use MDW and deploy them as reports in my SSRS ?

Comment on SQL Server 2008 Management Data Warehouse by Varun

Varun — Fri, 15 May 2009 19:35:52 +0000

I just created centralized Management Data Warehouse for like 5 of our SQL Servers on one server, but the database is at like 29 GB in just three days, how can I manage the size of the database from getting it to grow so big, it seems quicke hard to manage its size. For my data collectors I have specified 7 days of retention period, would after 7 days the database gets cleaned up.

Comment on SSIS Package to drop/create partitions based on partition list in the SQL Server table by Frank

Frank — Thu, 07 May 2009 15:40:39 +0000

well I will give it a shot. What other processing scripts were you referring to?

Thanks btw Vidas!

Comment on SSIS Package to drop/create partitions based on partition list in the SQL Server table by Vidas Matelis

Vidas Matelis — Thu, 07 May 2009 02:05:02 +0000

Frank,
That code is currently not available for public. But based on my other processing scripts you could create it quite easily. Simple logic – if ALL partitions require processing, then I do full dimension processing and then full cube processing. If at least one partition does not require processing, then I do incremental dimension processing and full processing just for partitions that require it.

Comment on SSIS Package to drop/create partitions based on partition list in the SQL Server table by Frank

Frank — Wed, 06 May 2009 19:56:06 +0000

Vidalis – I got it working. Seems I was selecting the wrong .dll in my references. Awesome code! Is there a processing piece of it that use the NeedsProcessing column of the table?

Comment on Using SSRS to report SSAS 2008 database structure using DMVs by Vidas Matelis

Vidas Matelis — Wed, 06 May 2009 18:53:36 +0000

Frank,

I just reviewed my source code and noticed that I do use everywhere single quotes. Just by blogging software replaces them and it is hard to recognize after that if it is double quote or single quote. Sorry about this confusion… So I would suggest to download my source code and get sample scripts from there.

Comment on Using SSRS to report SSAS 2008 database structure using DMVs by Vidas Matelis

Vidas Matelis — Wed, 06 May 2009 18:49:22 +0000

Frank,

In such case for “Measures” replace double quotes with 2 single quotes on each side. Then you should be OK. Not sure why there is an issue with double quotes…

Comment on Using SSRS to report SSAS 2008 database structure using DMVs by Frank

Frank — Wed, 06 May 2009 18:46:03 +0000

it does work but only after I replace the double quotes with single quotes. Maybe some setting to change?

Comment on SSIS Package to drop/create partitions based on partition list in the SQL Server table by Vidas Matelis

Vidas Matelis — Wed, 06 May 2009 01:32:35 +0000

Frank,

I have not tested this package on 2008 yet. But I would expect it to work.

Have you copied file
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.DLL to folder:
C:\WINDOWS\Microsoft.NET\Framework\v3.5

What specific error message are you getting?

Comment on Using SSRS to report SSAS 2008 database structure using DMVs by Vidas Matelis

Vidas Matelis — Wed, 06 May 2009 01:23:40 +0000

Frank,

Try to execute this DMV query in SSMS (connect to SSAS, new DMX query) and see if you will get any errors:

SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
WHERE DIMENSION_NAME = “Measures”

Make sure you replace double quotes from this website and retype them yourself. My website replace double quotes with special quote symbols that will generate errors in SQL and SSAS.
If you have no problems executing that query in SSMS (connected to SSAS), that means problem is with linked server. Maybe you should specify MSOLAP.3 and not MSOLAP.
Is there anything unique to your environment? Maybe servers are case sensitive?

Comment on SSIS Package to drop/create partitions based on partition list in the SQL Server table by Frank

Frank — Tue, 05 May 2009 20:17:32 +0000

Anyone have a VS2008 version of this package? I am having a difficult time getting this to work in 2008.

Comment on Using SSRS to report SSAS 2008 database structure using DMVs by Frank

Frank — Tue, 05 May 2009 19:23:06 +0000

sorry, I missed that when I pasted the query in there. I actually do have the = sign in the query I am running, but I am still getting the same error

select * FROM OPENQUERY(SSAS_Reporter
, ‘SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
WHERE DIMENSION_NAME = “Measures” ‘
)

Comment on Using SSRS to report SSAS 2008 database structure using DMVs by Vidas Matelis

Vidas Matelis — Tue, 05 May 2009 18:14:01 +0000

Frank,

I believe your query is missing = before measures:
select * FROM OPENQUERY(SSAS_Reporter
, ‘ SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
WHERE DIMENSION_NAME = “Measures” ‘
)
Also, just in case – this works just on SSAS 2008.