Database Reference
In-Depth Information
Defining data sources
Each Analysis Services project could use multiple data sources. The traditional ap-
proach is to first build a staging relational database where you import data from vari-
ous data repositories within your enterprise. Subsequently, you would build a dimen-
sional model using a Star or Snowflake schema, as opposed to a normalized model
you would typically use for a transactional database, for your data warehouse that has
fact and dimension tables. Lastly, you build the Analysis Services solution using the
dimensional model within the relational data source. This approach is still recommen-
ded, because it allows you to have more control over your data cleansing routines
prior to building Analysis Services objects.
On the other hand, SSDT does give you the flexibility to connect to various relational
databases and define necessary data structures within data source views, if you don't
have the luxury of building the staging area or the star schema database. However,
this flexibility comes with a price because your transactional relational databases
aren't likely to have data in the format you need for Analysis Services. Transform-
ing data while you build SSAS objects adds an unnecessary overhead. Furthermore,
you can accomplish certain data cleansing and transformation operations much more
simply and more efficiently while using
SQL Server Integration Services
(
SSIS
)
rather than doing so within Analysis Services. Keep this in mind as you learn how to
create data sources.
How to do it...
Once you launch SSDT, go to
File
|
New Project
and then select
Analysis Services
Multidimensional
and
Data Mining Project
. Provide a descriptive name for the new
project, for example,
SSAS Cookbook Chapter 2
, and save it to the location of
your choice. At this point you should see the project with the necessary folders within
the SSDT's
Solution Explorer
window. To define a data source perform the following
steps:
1. Right-click on the
Data Sources
folder in the
Solution Explorer
window and
select
New Data Source
. This activates
Data Source Wizard
, as shown in
the following screenshot.
2. Now, click on
New…
.
Search WWH ::
Custom Search