Creating data sources

Once we've created a new project and configured it appropriately, the next step is to create a data source object. Even though you can create multiple data sources in a project, you probably shouldn't. If you've read the previous chapter, then you'll know that we recommend that all of the data needed for your cube should already be present in a single data mart.

You are then faced with the choice of which OLE DB provider to use, since there are often several different options for any given relational database. For SQL Server data sources, you have the option of using the SQLClient .NET data provider, the Microsoft OLE DB provider for SQL Server and the SQL Server Native Client (often referred to as SNAC). You should always choose the SQL Server Native Client since it offers the best performance. For Oracle data sources, the choice is more complicated since, even though Oracle is a supported data source for Analysis Services, there is a long list of bugs and issues. Some are addressed in the white paper at http://tinyurl.com/asdatasources, but if you do run into problems, the best approach is to try using Microsoft's Oracle OLE DB Provider, Oracle's own OLE DB Provider, the .NET Provider for Oracle or any of the third-party OLE DB Providers on the market to see which one works. Access, DB2, Teradata and Sybase are the other officially supported relational data sources, and if you need to load data from another source, you can always use SQL Server Integration Services to push data into the cube by using the Dimension Processing and Partition Processing destinations in a Data Flow.

Analysis Services must also be given permission to access the data source, and how it does so depends on the type of data source you're using and how its security is set up. If you're using Windows authentication to connect to SQL Server, as Microsoft recommends you to, then you should set up a new Windows domain account specifically for Analysis Services, and then use the SQL Server Configuration Manager tool to set the Analysis Services service to run under that account. You should then give that account any permissions it needs in SQL Server on the tables and views you'll be using. Most of the time 'Read' permissions will be sufficient. However, some tasks, such as creating Writeback fact tables, will need more. You'll notice on the Impersonation Information tab in the Data Source Designer dialog in BI Development Studio there are some other options for use with Windows authentication, such as the ability to enter the username and password of a specific user. However, we recommend that you use the Use Service Account option so that Analysis Services tries to connect to the relational database under the account you've created.

If you need to connect to your data source using a username and a password (for example, when you're using SQL Server authentication or Oracle), then Analysis Services will keep all sensitive information, such as passwords, in an encrypted format on the server after deployment. If you try to script the data source object out you'll find that the password is not returned, and since opening an Analysis Services project in online mode essentially involves scripting out the entire database, you'll find yourself continually re-entering the password in your data source whenever you want to reprocess anything when working this way. This is another good reason to use project mode rather than online mode for development and to use Windows authentication where possible.