AMO SSAS Stored Procedures - connecting to the transactional data source
| SSAS Stored Procedures - connecting to the transactional data source |
| Written by Dr. John Tunnicliffe |
| Thursday, 24 April 2008 19:00 |
|
There are often circumstances where an SSAS stored procedure needs to connect to the transactional database to perform a query. This is certainly the case in a near-real-time OLAP solution where the cube sits directly on top of the application database and dynamic dimension security is implemented by a stored proc. Here the SSAS stored procedure has to query the transactional database in order to get the user's authorisation. Clearly the SSAS database has a data source and it uses this connection to process the cube. So how do we get the connection string? Well the short code snippet below provides the solution. The code simply connects back to the current database using AMO and obtains the connection string from the data source object. It then removes the redundant "Provider=SQLNCLI.1;" before returning a valid SQL connection string that can be used by SqlConnection(). Read more... |
Tags: 2005, amo, article, stored procedure
Top Rated
- SSAS Implementation Best Practices slides in PDF format
- SSRS Report Against a SSAS Parent Hierarchy
- Using AS Data Mining to Add Forecast Values to a Cube
- Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005
- Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage
- Handling Multiple Calendars with a M2M Scenario
- Passing MDX parameters in Reporting Services reports
- Using UserName to Control Data Access and Default Member in SSAS 2K5 (Carrie Williams)
