<div>
SSIS was designed for doing data integration. &nbsp;You can use it to access data from different types of sources (Oracle, SQL Server, Flat files, Excel). &nbsp;You could use SSIS to populate your server tables.<br />
<br />
You would design a dimensional data warehouse/mart, then restructure (using SSIS, and or t-Sql procedures, etc.) the data &nbsp;in the staging tables to populate that warehouse. Finally, you use the warehouse, (a star schema or something similar) to populate an analysis services (SSAS) database ('Cube&quot;).
</div>


SSIS was designed for doing data integration.  You can use it to access data from different types of sources (Oracle, SQL Server, Flat files, Excel).  You could use SSIS to populate your server tables.

You would design a dimensional data warehouse/mart, then restructure (using SSIS, and or t-Sql procedures, etc.) the data  in the staging tables to populate that warehouse. Finally, you use the warehouse, (a star schema or something similar) to populate an analysis services (SSAS) database ('Cube").

<div>
<div class="content wysiwyg-content">
I am designing an Analysis Services system, the end goal of which is to throw up some pre-defined reports to end users. The SSAS side of things will be required to pull data in from several different databases on several different servers. The data will be updated daily, so possibly a scheduled ETL job will have to run every night to update with latest updates / inserts / deletions. We're not talking massive here, but several million rows being pulled from each system, and eventually there could be up to 20 odd different databases involved.<br />
How am I best designing this? Should I create a Data Mart / Warehouse into which I should pull all the data and then design the SSAS project around this?
</div>
</div>


I am designing an Analysis Services system, the end goal of which is to throw up some pre-defined reports to end users. The SSAS side of things will be required to pull data in from several different databases on several different servers. The data will be updated daily, so possibly a scheduled ETL job will have to run every night to update with latest updates / inserts / deletions. We're not talking massive here, but several million rows being pulled from each system, and eventually there could be up to 20 odd different databases involved.
How am I best designing this? Should I create a Data Mart / Warehouse into which I should pull all the data and then design the SSAS project around this?

SSAS Design

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors - Multidimensional and Tabular.