SSAS Design

Posted on 2009-02-17
Last Modified: 2016-02-12
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?
Question by:ilikemycar
    LVL 13

    Accepted 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.


    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now