:::: MENU ::::

Data Source Configuration for SQL Server Analysis Services

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger

Data Source Configuration for SQL Server Analysis Services

When you create a new database within an Analysis Services instance, one of your first tasks is to define the data source for the database. A data source contains the information necessary to access source data for the database objects. The term “data source” actually refers to the data source object that is created, not the source data itself. When you define the data source in Analysis Manager, the name given to the object is either – or -. However, to eliminate confusion when the database is moved to another server, you should change the default naming convention by creating a logical name for the data source unrelated to the name of the original computer on which the database was initially created.
To create a logical name for the data source object in Analysis Manager, create the data source object. Then copy the new data source object and paste it into the same Analysis Services database. You are then prompted to define a new name for the data source object. The name you choose should reflect the logical type of data such as Sales Data, or Personal Data. After you define the new logical name, delete the original data source
object. Thereafter, when you move an Analysis Services database between computers, you can simply change the underlying server and database in the connection string by modifying the properties of the data source object in Analysis Manager (or in your script).
In addition to renaming your data sources to logical rather than physical names, you should ensure that your deployment computers use the same name. If your development computer has its data source named Sales Data, then your QA computer should have its data source named Sales Data, and your production computer should have its data source named Sales Data. Using consistent names across the development, QA, and production computers makes migration of individual pieces easier by cutting and pasting between the Analysis Services databases. If you do not change the name of the data source object before you create objects in the database, you will not be able to change the name of the data source object without using third-party utilities.

Consult us to explore the Databases. Contact us