How to write stored procedures in SSAS

Question

How to write stored procedures in SSAS

sindhu.rm

SSC Veteran

Points: 290
More actions
May 26, 2008 at 11:56 pm

#186141

Hi All,
Can u say, How to write stored procedures in SSAS(Sql Server Analysis Service).
If it is possible to write stored procedure, then...Can u say how to invoke that automatically from SSAS.
For instance:
If the Value in one dimension table exceeds it's limit then the stored procedure has to be called automatically.
Thanks in Advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

elegantkvc SSCommitted Points: 1910 More actions · Answer 1

hi sindhu,

You can use the Execute sql Task of SSIS to execute the stored procedure...For triggering the sored procedure on changing dimension..I have no idea .:)

Regards

vijay

Neelakandan V-453229 Ten Centuries Points: 1109 More actions · Answer 2

Hi Sindu,

As far as I know, there is no stored proc in SSAS. But, I presume that you are using a ETL tool for loading your dim table, so you can call a stored proc there. Or, have a trigger in the dim table.

Does it help?

Regards,

Vijay.

Warm Regards,
Neel aka Vijay.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Yes, you can create stored procedures in Analysis Services:

http://msdn.microsoft.com/en-us/library/ms176113.aspx

No, you cannot "trigger" them based on data changes. If your cube is real-time, you will need to do something on a schedule. If your cube is being updated on a schedule, you can create a KPI that would indicate if the procedure needs to be run and then just have the application you are using to process the cube (probably SSIS) check the status of the KPI and run the procedure.

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 4

Stored procedures in SSAS are implemented via assemblies (and they're different from what you're thinking of.)

The question here, is what are you looking to do? Why are you looking to call a "stored procedure automatically?".

Remember, MSAS is about reporting, not processing (just in case). However, depending on your reporting tool, you could trigger things "automatically". For example, in MSRS, you could have a report that queries the results and have a VBA function that triggers a specific SQL Stored procedure (i.e. passing certain key values).

We'd need more details to be more specific.

Anamika SSCertifiable Points: 6202 More actions · Answer 5

hi,

i'm trying something what you people are discussing.

it means that, store procedures should be written in C#or VB.NET, (but the coding is nothing but mdx queries and not conventional coding??)kind of languages and that can be called through assemblies as DLLs.

correct me if my understanding is wrong.

Thanks,

Regards,

Viji

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 6

An assembly is written in .Net, so any .Net language would apply. MDX is the "data access language" (much like SQL).

Cases for an assembly vary, and it's still an emerging strategy. (I'm interested in practical implementations myself.) But some possible examples:

a regression function, callable via MDX

meta data functions (MDX isn't quite complete)

DML functionality.

Or anything else that's useful.

If you're interested in this topic, I suggest you check out: http://www.codeplex.com/ASStoredProcedures