I recently designed a cube in Analysis Services from scratch. After some brief research on all the different ways to process Analysis Services objects, some of the questions I had finally have a clear answer.
If you have being working on Microsoft’s BI platform, you would know that SSAS and SSIS actually meet quite often. Did you design SSIS packages for fact table ETL and dimension ETL? Yes, you did.
The real integration points between SSIS and SSAS, though, involve SSAS cubes, where we need SSIS to process the database sources into the OLAP cube structure.
A big portion of SSAS integration with SSIS involves processing data and managing measure group partitions. In SSIS, Microsoft has provided us with some out-of-box basic cube processing capabilities, and also tools for handling more complicated situations. I’ll show you how I used the out-of-box basic tasks in SSIS to process my cube.
Before I jump into the details, I want to ask you two questions. One, what are some of the basic objects in SSAS? Two, what are the main types of SSAS objects that must be processed? I’ll blog about these two questions later.
3 Out-of-Box methods to process SSAS Objects in SSIS
1. Using the control flow object Analysis Services Processing Task
To me, this is the most straightforward approach. This is the method I used. I’ll show you how I used it in this post. You will see the drawbacks of this approach from the example I will show you.
2. Using the control flow object Analysis Services Execute DDL Task
This object’s functionality goes beyond just Data Definition Language (DDL). It can run an SSAS XMLA script, which can not only run DDL, but also query and process. Because XMLA script can be modified in SSIS package before it is executed, this object is actually very powerful in terms of processing SSAS objects dynamically (without drawbacks of hard-coding the SSAS objects).
3. Two data flow destinations: Dimension processing destination and Partition processing destination
These two objects allow data directly from the pipeline to be pushed into a dimension or a partition. This is the only method where data is pushed directly to SSA. The above two methods essentially tell SSA to start pulling data from a data source
Use control flow object Analysis Services Processing Task to process SSAS Objects
It’s pretty straightforward with this approach. I’ll blog about it in my next post.
In one of my many SSIS blog posts, I said that if you are using SSIS, sooner or later, you are going to create variables.
In this blog, I’ll say that, if you are using SSIS, sooner or later, you are going to be frustrated by the crazy syntax in Expressions.
If you are like me, who has been in the database world for awhile, SQL-like syntax becomes intuitive to us over the time, but the VB-like syntax used in the SSIS expressions can be very foreign to us. Logical AND needs to be &&, logical OR needs to be ||, concatenation needs to be &, and == is not the same as =.
OK, here is the official link to the Integration Services Expression Reference on MSDN.
So, what are Expressions? To me, Expressions in SSIS are SISS developers’ “programming” language. Developers who have mastered tools like C# can write programs that can meet any (almost) user requirements with the ease of conditionally and dynamically controlling logic flows and creating logic branches. Compared to those application programmers, we, database developers, often look very awkward, and inadequate sometimes, in terms of being in control.
Fortunately, in SSIS, we can make friends with Expressions. From the above link in MSDN:
Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single constant, variable, or function. More frequently, expressions are complex, using multiple operators and functions, and referencing multiple columns and variables.
Did you notice that it says expressions are complex frequently?
There are 4 topics on the MSDN reference site.
Describes expression evaluator syntax, the data types that the Data Transformation Pipeline uses, data type conversion, and expression elements.
Describes the operators that the expression evaluator provides.
Describes the functions that the expression evaluator provides.
Provides expressions that use multiple operators and functions.
The Functions section can be very familiar to many database developers. To be able to feel comfortable with using Expressions in SISS, everybody needs to read the Operators section and the Advanced section at lease once.
So, next time, when you cannot get your simple Precedence Constraint working as the way you wanted it to, check out the above Integration Services Expression Reference first.
Just a side note. This blog is inspired by my late night change to a SSIS package. I wanted to add a Precedence Constraint so that my package will not run if it’s a holiday or weekend.
Here is a “simple” expression ( simple only after I remembered AND needed to be &&):
@varHolidayInd == "N" && @varWeekendInd == "N"