Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 9, 2005

Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS - Page 2

By William Pearson

DTS' Analysis Services Processing Task

Overview and Discussion

One of the most critical administrative tasks surrounding an Analysis Services implementation is the recurring need to process our cubes. Processing is essential to keeping our OLAP data sources in sync with the data sources that they summarize. The Analysis Services Processing task exists to perform the processing options that we typically perform manually from within Analysis Manager, at least initially, in the design and development of our cubes. We can use it to automate our processing cycles, and thus keep our cubes updated to reflect the latest data in the cubes' underlying sources, all with minimal manual intervention.

Because some Analysis Services processing capabilities, including the processing of databases, dimensions, cubes, or other objects we can manually process from Analysis Manager, could not be easily managed using other DTS tasks, we are provided with a means of performing these activities with a task specifically designed for that purpose. The Analysis Services components to which we can apply our DTS-driven capabilities include those detailed, hierarchically, in Table 1.

Component

Use This Option to:

Database

Process all dimensions, cubes, virtual cubes, and partitions in the selected Analysis Services database.

.. Cubes Folder

Process all cubes and virtual cubes contained in the selected folder.

... Cubes

Process, refresh, or incrementally update the selected cube (see Table 2 below).

.... Cube Partitions

Process or incrementally update the selected partition.

.... Remote Cube Partitions

Process or incrementally update a selected partition on another machine.

... Linked Cubes

Process, refresh, or incrementally update a cube based upon a cube on another machine.

... Virtual Cubes

Process, refresh, or incrementally update the selected virtual cube.

.. Dimensions Folder

Process all dimensions contained in the selected folder.

... Shared Dimensions

Process or incrementally update the selected shared dimension.

... Virtual Dimensions

Process or incrementally update the selected virtual dimension.

.. Mining Models

Process all Mining Models contained in the selected folder.

... Relational Mining Models

Process the selected Relational Mining Model.


Table 1: Component Selection Options for the Analysis Services Processing Task

We also have the options depicted in Table 2 with regard to the manner in which we perform processing.

Component

Processing Options

Database

  • Full Process

  • .. Cubes Folder

  • Full Process

  • Refresh Data

  • ... Cubes

  • Full Process

  • Refresh Data

  • Incremental update

  • Incrementally Update Dimensions

  • .... Cube Partitions

  • Full Process

  • Incremental update

  • Incrementally Update Dimensions

  • .... Remote Cube Partitions

  • Full Process

  • Incremental update

  • Incrementally Update Dimensions

  • ... Linked Cubes

  • Full Process

  • ... Virtual Cubes

  • Full Process

  • .. Dimensions Folder

  • Rebuild the dimension structure

  • Incremental update

  • ... Shared Dimensions

  • Rebuild the dimension structure

  • Incremental update

  • ... Virtual Dimensions

  • Rebuild the dimension structure

  • .. Mining Models Folder

  • Full Process

  • Refresh Data

  • ... Relational Mining Models

    7     Full Process

  • Refresh Data

  • ... Relational Mining Models

  • Full Process

  • Refresh Data


  • Table 2: Processing Options and the Components to Which They Apply

    Other considerations affect the options that are available, as well. An example is illustrated in the selection of multiple objects in the tree structure provided within the Analysis Services Processing task dialog; anytime we select two or more objects within the nodes of the tree, full processing / rebuild is our only selection option.

    As we note in the tables above, the Analysis Services Processing task allows us the flexibility to leverage the time savings that accompany incremental updates. While, as I mentioned in the last paragraph, we are forced to do full processing in a scenario where we select a cubes folder to process multiple cubes, we can perform an incremental update on a cube group if we create an individual task for each cube (which we can then "chain" together within a DTS package). The same is true when we select a dimensions folder: we are forced to do a complete rebuild of these dimensions, or to create a separate task for each one. (In general, we can meet any processing sequencing need not specifically "built in" to the Analysis Services Processing task through chaining tasks in a larger DTS package.)

    NOTE: For more information on processing cubes and dimensions in general, see my other articles in my Database Journal series, Introduction to MSSQL Server Analysis Services, indexed at

    http://www.databasejournal.com/article.php/1459531

    We will gain some hands-on familiarity with the Analysis Services Processing task in the Practice section below. In walking through the creation of an Analysis Services task, and the subsequent execution of the DTS package we construct to house it, we will comment on various settings and approaches as we encounter them.



    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















    Thanks for your registration, follow us on our social networks to keep up-to-date