Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

SSAS Design

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?
1 Solution
I would pull all the data into a Staging Database; Perform any calculations/malipluations/data cleanup in here; then push this onto your final DB from which the Cube will be built.

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").

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now