Building Basic Dimensions and Cubes - Expert Cube Development with SSAS Multidimensional Models - page 38

It is a good idea to install BIDS Helper, an award-winning free
community-developed tool that adds a lot of useful functionality to
BIDS. You can download it from http://www.codeplex.com/
bidshelper . We'll be referring to some of its features later on in
this chapter.
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 available 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 ( SSIS ) to push data into the cube by using the Dimension
Processing and Partition Processing destinations in a Data Flow.
Remember to install the same version of any OLE DB provider you're
using on all of your development, test, and production machines.
Also, while SSDT is a 32-bit application and needs a 32-bit version of
the driver to connect to a relational database, if your Analysis Services
instance is 64-bit, it will need the 64-bit version of the same driver to
process cubes successfully.