Re: creating cubes from different data sources Analysis Services 2005
- From: "Jéjé" <willgart_A_@xxxxxxxxxxxxxx>
- Date: Mon, 31 Jul 2006 16:58:40 -0400
If you think you can synchronize multiple datasources into AS then its
because your source databases are clean, synchronized (keys...) and with a
near-star schema design.
if its the case, you can simply load all of these data into a central data
warehouse database very easely because there is no transformations.
so a DW can be simple if your sources are simples too.
the only option you have in AS2005 is to create 1 DSV by connection.
create the dimensions and cubes based on these DSVs
and create a lot of calculations to synchronize your data in the cubes,
because you'll have 4 customers dimensions if you have 4 sources and 4 DSV
which produce customers.
this is a huge work, creating a DW will be more easely and efficient.
<daniel_h_levite@xxxxxxxxxxxx> wrote in message
news:1154377784.051680.24880@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
As you should know, datawarehouses are NEVER simple. They create their
own complexities.
In my reading of the new features, this UDM and data views were
supposed to mitigate multiple data source models. If that was assumed
to be from a single data connection, I do not know how that is any kind
of advantage.
Copying data, just to get it into a single database for the tool to
work is non-added value and unfortunate, especially on large data sets.
I guess they have a long way to go in this area. Thanks for the
help.
Jéjé wrote:
first, you can't create 1 DSV based on multiple connections.
second, you are right, using impersonation will use the AS service account
to access the data source when you process the cubes etc...
this require that the AS account must have access to these sources; but if
you can't use the AS account, you can use a specific username / password
(like an SQL Server account) by data source.
also, you can't create a cube (or dimension) using different DSV (so no
multiple connections is not allowed). so if you create a cube on a dev
server which access 1 DSV and you plan to access 2 or more databases in
production, this doesn't works!
in complex envirnoment there is a simple solution:
a data warehouse!
this is the "simple way" to make sure you synchronize and cleanse your
databases.
another option is to create an empty database in SQL Server and create
views
and linked servers.
each view will execute something like: a select * from
linkedserver1...table
then AS will use these views.
using this design you can virtually use data from multiple sources at the
same time in the same DSVand cube...
SQL Server will execute the query against the right source server.
but this impact the query performance to read the data from the sources
and
the data quality become a problem.
<daniel_h_levite@xxxxxxxxxxxx> wrote in message
news:1154371777.228597.195240@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am trying to create a Data View from multiple data sources. All use
impersonation to connect. However, when I go to build the cube I get
an error that login failed. This is due to the account under which
analysis services runs. This seems to be a design problem. UDM, Data
views, and data sources become pretty useless in a hetergeneous
environment if Analysis Services REQUIRES the service account to have
permissions on all the data sources and cannot use the impersonation
data for the data sources. How do you deploy a cube project to
analysis services from BI Studio using different data sources with
different logins?
I must be missing something because this makes the features meaningless
if all data sources for cube deployment have to be common sql server or
linked servers because of simple permission issues.
.
- References:
- creating cubes from different data sources Analysis Services 2005
- From: daniel_h_levite
- Re: creating cubes from different data sources Analysis Services 2005
- From: Jéjé
- Re: creating cubes from different data sources Analysis Services 2005
- From: daniel_h_levite
- creating cubes from different data sources Analysis Services 2005
- Prev by Date: Re: exsists and existing function in the calculate script and subcube issue...
- Next by Date: Re: Problem with CURRENTMEMBER on multi select data filter - Please help
- Previous by thread: Re: creating cubes from different data sources Analysis Services 2005
- Next by thread: exsists and existing function in the calculate script and subcube issue...
- Index(es):
Relevant Pages
|
