Data Vault Training – Australia in 2015

The Genesee Academy’s Data Vault training has been announced for 2015 in the Australian region. 

Hans Hultgren will be running classes on Brisbane, Melbourne, Sydney and Auckland in July and August this year (the dates are below with links to the courses and registration). 

It’s a great class (I’ve heard multiple recommendations about this one) so if you interested in Data Vault, why not give attend and learn from someone who literally wrote the book

 

Jul 13 – 15th 3 Day Certification – Brisbane Australia Registration
Jul 22 – 24th 3 Day Certification – Melbourne Australia Registration
Jul 27 – 28th 2 Day Certification – Sydney Registration
Aug 3 – 4th 3 Day Certification – Auckland Registration

Forcing Values into SSRS Matrix Columns and Rows

The concept of the trusty SSRS matrix control is great for dynamically expanding columns and rows based on the objects source data.  It’s behaviour operates in a very similar way to a Pivot table, that is, expanding and contracting based on underlying data.

However, when you want to force data to be shown in rows or columns, the expanding nature of the control can present a problem where the data does not exist. Consider a report where we allow the user to select multiple subcategories and then we display the number of products in each category based on their selection.

The hierarchy for this relationship is product –> product subcategory –> product category, so if we allow the user to select a subcategory, and then restrict our result set based on this, the matrix will not show the all available Categories.

Consider the base (standard query) that uses a parameter for subcategory_id (its a multi-valued parameter).

select
pc.Name as CategoryName
, count(*) as ProductCount
from [Production].[Product] p
join [Production].[ProductSubcategory] psc
on p.ProductSubcategoryID = psc.ProductSubcategoryID
and p.ProductSubcategoryID IN (@subcategory_id)
join [Production].[ProductCategory] pc
on psc.ProductCategoryID = pc.ProductCategoryID

group by pc.Name

 

image If we have a matrix (as in the image to the left), the Category values that are shown are dependent on what values are in the dataset.

If we want to extend the control to show all values, we have to append it to the dataset.  Therefore, our query would need to be changed to the following;

select
pc.Name as CategoryName
, count(*) as ProductCount
from [Production].[Product] p
join [Production].[ProductSubcategory] psc
on p.ProductSubcategoryID = psc.ProductSubcategoryID
and p.ProductSubcategoryID IN (@subcategory_id)
join [Production].[ProductCategory] pc
on psc.ProductCategoryID = pc.ProductCategoryID

group by pc.Name

union all select
Name as CategoryName
, null as ProductCount

from Production.ProductCategory

With such a small example this approach works fine. However, when the dataset includes additional fields, and the matrix groups on those fields, the grouping behaviour may create undesired results because the additional union adds another layer of data to group on.

Consider, for example if we included country, our dataset would look like

[country name (as a value)],[category],[count]   -- for the 'real data'
[country name (as a null) ],[category],[count]   -- for the 'additional data'

If a report included a grouping on country name, the nulls would be in their own group as a value and this is not what we want to display.

To solve this, we can apply a filter on the matrix to remove the blank countries. This does not remove the additional categories (that is values being shown), it just effect what country values are shown. A handy little trick :)

SSRS Report Parameters from Unfriendly Sources

We have recently developed some SSRS reports against Non SQL Server sources and have run into a few issues regarding the use of multi-value parameters.  Since, we are sure to meet this problem again (and variations of it), this post is just as much of a bookmark for ways around the problems that we’ve encountered.

So what’s the problem?  Lets assume were using an  MySql Source with the same data as AventureWorks, we can to pick a Subcategory and then have our report show a list of products.  This is pretty straight forward.  The products query uses a ? to identify a parameter, and that parameter is assigned a value to in the dataset properties;

SELECT        *
FROM            adventure_works_2012.product
WHERE        (ProductSubCategoryID = ?)

You can see how this is setup in the image below.  The SubCategory dataset is the source for the SubCategory parameter and that is used as a parameter for the Products query.

image

Unfortunately, this only works if our SubCategory Parameter DOESNOT allow multiple values.  If we enable this as an option for the Parameter, we’ll get an error like the following when we try to view the report.

image

To make the products query run with multiple parameter values we need to dynamically generate the SQL associated with the parameter.  Actually there are a couple variations of this however they all use the same theme.

So, first, we change our query to recognise the need for multiple values from the parameter;

SELECT     *
FROM            adventure_works_2012.product
WHERE        ProductSubCategoryID in (?)

Then, instead of supplying the parameter value to the dataset, we use an expression that delimits parameter values.

image

Here, the source type is a integer so we are expecting something like 1,2,3 etc to be passed as the parameter’s value.  If the source type was a string, we’d just have to append quotes around the statement.

= Join(Parameters!SubCategory.Value, "’ ,’")

Variations

We can use a few variations of this approach to generate the parameter list.  For example, we could exclude the parameter definition from the Products dataset and specify the query as a function that returns an SQL string.  Using this approach, the Parameter value is still called but the parameter object is not defined in the dataset.

We can also define a some VB code that returns the SQL string (I find this a little more flexible).  Here the string value of the parameter is passed to the VB function.  So here, I’ve created a simple function and added it to the report;

Public Function SQL(ByVal Keys as String) as String
  return “select * from adventure_works_2012.product where ProductSubCategoryID in (” & Keys & “)”
End Function

Now, our dataset is defined by the expression

=Code.SQL( Join(Parameters!SubCategory.Value, ",") )

Oracle Batches & Vairables

More of a prompt for me than an informative post :)

Oracle params/variables can be defined in a batch statement through a variable keyword and then assigned a value using the exec (execute) command.  For example, we can define 2 variables and give them a value with the statements below (note the use of := for assignment)

variable v1 nvarchar2(20);
variable v2 nvarchar2(20);
variable v3 nvarchar2(20);

exec :v1 := 'Val 1';
exec :v2 := 'Val 2';
exec :v3 := 'Val 3';

select :v1 as v1, :v3 as v2,
from dual;

select :v2 as v2 , :v1 as v1
from dual;

When executing the command (I’ve tried this on TOAD and SQL Developer), the function key used with either

  1. Execute the batch  (if F5 is pressed) or
  2. Execute the current command where the cursor is (if F9 is pressed).  This relates to the statement surrounding the cursor position.

Where the command is executed (F9), any variables in the command are prompted for entry (as shown below).

oracle_batch

 

Tableau 9 – Features for User Experience

There’s no doubt about it – Tableau is synonymous with best practice data visualization.  Explain a problem to any consultant about visualization and tableau’s name will enter the conversation (well in most independent cases).  Tableau’s approach is targeted at visual investigation – allowing the user to work with with data in real time and draw conclusions from it.  An approach that was the original intentions of OLAP technology and like OLAP tableau allows the developer/user to define a model within the (tableau) workbook and create visualizations from that model.  This is an important call out because it is not dependent on a single table import and can combine data from several disparate sources (I’ve heard some consultants say that you can only display data from a single table).

So with this in mind, what do I like in the next release (V9)?  I was originally going to publish this after the Singapore conference however, a recent brief at the BBBT revealed some very nice features that are definitely worth a call out.  Surprisingly, the features are targeted at the user experience and not so much at visualization improvements

Importing/Connecting to Data

There’s a new connector for SAS and R data files which may be great for passing data along a work stream.  But from my point of view the most useful improvement is the concept of a data interpreter.  This investigates a source (Excel Sheet) to look for and interpret tabular data (or a data set) that is the true intention of the import.  This has several nice consequences of the feature.  You don’t have to have data start cell A1, infact it could be a ‘report’ somewhere in the sheet.  Headings (non tabular cells) are striped as are columns, so you could import source systems exports that are not in a strict tabular format.  The import can manage formatting and cell merging.  For example column headings that appear over 2 lines are imported without issue.  Finally, the interpreter applies its own delimiting function.  Fields that are delimited (or even the import of a delimited file) can be identified and broken down into multiple fields.

Is this a mature ETL feature?  Certainly not.  There’s practically no way to control how the the feature works but that’s not the point.  The benefit from my point of view is that there is a somewhat intelligent way to use other sources and the ‘month end’ report pack just became a lot more functional.

LOD Expressions & Windowing Functions

Proportions (and trends) are the bread and butter of analysis.  We naturally classify items into groups and show significance of items to the group by its proportion to the group total.  Of course the calculation of the ratio requires 2 values (detail and group total) and the group total is an implicit windowing problem.  In order to derive the total, we have to define a range of data to perform a calculation over (that is window) and this is a usually a problem because there is an associated hierarchy between an item and the group.

In MDX we can define the calculation by reference to the current members parent (provided that a hierarchy exists), however, in reports its not so easy and the most common implementation is found in control which allow this as part of there feature (pie charts are a natural fit for this). Unfortunately, the values (actually only the ratios) are artifacts of the control and unavailable outside its scope.

Tableau solves this issue in 9 with a scoped calculation called a Level Of Detail (LOD).  Its a really impressive method to define the scope of an aggregation.  This is a really cool addition because (in addition to ratios), those measures can be reused through out the workbook.  You can define a calculation with respect to the scope of data that’s shown in a control.

The only consideration i have not tested is solve order …

Measure Editor

While we are on the subject of measures, there’s also a new in-line editing feature that allows you to define calculations in the worksheet.  You just start typing the (intelli-sense enabled) formula in rows or columns and the calculation is added to the sheet.  Then, If you want to create a measure for the workbook, you can simply drag it to the measures tab.

Tableau Online

There have previously been two options for sharing workbooks in Tableau.  Firstly, you can run up your own instance of Tableau Server which was the enterprise web server.  For those not familiar with it, I would liken it to Share Point or Report Server for Tableau workbooks.   Something that you need to engage the IT department on to get up and running.  Secondly there was Tableau Public – a no security implementation of Tableau Server that is open to everyone – not really a reporting solution for a department.

This leaves some organisations that I speak to in an interesting predicament.  They like the desktop tool but can’t share the workbook because of challenges with their IT department (the direct sale of tools to business users seems to exasperate any ill feelings between IT and business doesn’t it?).  Tableau Public is just not an option and a Server wont get past IT or the business.

Enter Tableau Online.  A secure pay by the user hosted service of Tableau Server.  Actually, thinking about the offering, I’m surprised that it has taken this long to eventuate because (in hindsight) it seems like such a sweet spot for the sales model (well at least the clients I speak to).  Its a secure, private implementation of Tableau Server that’s hosted by Tableau – a very nice offering indeed.

Another very nice feature included in the new server (all versions I believe) is search functionality.  Workbooks can be tagged and searched within the site.  This type of functionality seems to be the new norm for finding what you want.

If your not interested in the server, you could try tableau reader for some method of distribution and collaboration.  This is equivalent to a PDF reader (for tableau files).

Conclusion

So not much new (well perhaps advertised) in the visualization space.  There are some nice features for grouping (lassoing) data points in maps but for the most part, I see the improvements relating to how the user interacts (and perhaps what they can define) with data.

A Brief Look at Qlik

I was fortunate enough to attend a Qlik briefing last week and this post is targeted at some of my observations about that product and the offering (or approach) that Qlik takes to the market.  To be explicit, these observations are my own thoughts and were, in no way dependent on the briefing.

The perception of most folk that I discuss Qlik with is that the product is a departmental solution and “hard core” BI pro’s dismiss it as a trivial product.  Similarly, these emotions are projected to other self-service products (Tableau, Spotfire etc).  They suggest that Qlik could never handle the scale that enterprise solutions require (or so the argument goes) and possibly this may have been the case in resource constrained environments.  Is this consideration only applicable only to Qlik? – I’m not so sure…. actually, of course NOT.

The question of this scalability is one that seems most relevant given Qlik’s history.  Why?  Because Qlik applications embed their data with the presentation engine (exactly the same approach as Power BI).  Looking at the industry, it is interesting to see that in the last 2-3 years this approach has now become accepted as the norm (dare I suggest recommended) and the proponents of ‘its not enterprise’ are quiet.

Qlik seems to have this scalability concern covered with clustering and load balancing capabilities (of course I have not tested this against any large datasets).  Notwithstanding that, I have no reason to expect that their ability would not provide enterprise grade solutions or performance.

Now onto the product.  Qlik’s approach is to build applications based around data – developers/users connect & import their data to the workbook (or application) and then chose various presentation methods to present it to end users.  Again, I think this is so similar to the concepts of Power BI and specifically, the development of an “Excel Application” that conceptually, the two approaches are indistinguishable.  For me this is a very important clarification because a very large number of visitors to this site search for methods to integrate and create those Excel Applications.  Managing the worksheet objects through VBA is a very common requirement and suggests that the concept of an Excel application (or reporting pack) is alive and well (still).

The Data Driven App

One of the impressive features of the Qlik data is the idea of an associative model.  If you look at the image below you’ll notice that there are relationships between tables and (as we would expect) this causes filtering at the data level.  A really interesting feature of this model is that it is bi-directional and the concept of a key table (lookup table) with a unique key in a one way relationship is not required.  This means that many-to-many relationships are handled intuitively by the data model.

image

Actually, the idea of ‘modelling’ is redundant in Qlik because there is really nothing for a traditional modeller to do.  Data is loaded and relationships are automatically created between between tables based on field names.  This extends to relationships that would otherwise be created between composite fields (multiple columns).

Controls

Continuing with the idea of a developing a data driven application – controls access the underlying model an and either restrict visibility (if data elements are chosen) or display the current data set (for example display a visualisation based on the active filter).  Here is where I think the ‘developer’ has some real power.

Just as in the development of an Excel application you can add tabs (additional pages with different controls) and, in a similar way to the usage of a slicer/pivot connection apply filters across the entire application.  The great thing is that the controls are so powerful and customisable that the developer really has an open slate to design the application the way they want.  For example (as below), you can include a spark chart in a list box and set its order by any value.  Everything seems to be customisable visually: backgrounds, fonts (with visual cues), headings … its all there.

image

image

Other Cool Features

There were a few other cool features that I thought the product had and are worthy of mention.  The first is search controls.  Here (as below), you can filter your data by searching for values, and a search is applied to all data in the model.  Say for example, I’m looking for ‘%ar%’.  Using the search control shows any occurrences of Ar something in my entire model.

image

There is also a really neat screen sharing option.  Here’s the business case.  Say for example, I’m doing some analysis and I want to talk about an issue with an associate – I can send them a URL so they can see my Qlik application in a browser as I do (even as I make changes).  Its effectively an terminal service of my screen.  Really cool :)

Finally (and I’m sure this list is not exhaustive), there is the ability to annotate and snapshot the application.  For example, you can send an email to your associate with comments/instructions (that are also stored in the app) and include a ‘snapshot’ that opens the application with the same filters that you had (and the same data).

The Conclusion?

There are some great features for developing an application and I think that’s the key word.  Its designed to build and application for users so in my mind its not what we would call a data discovery tool.  But oh…. such power and (I’m sure) these features have only scratched the surface. :)

Biml XIII – The Biml Script for the Entire SSAS Database

This final post on biml SSAS processing and provides the full script to produce two packages.  It really ties in the first 2 posts to provide a complete solution for our processing scenario.  Remember, our goal is to point our biml at a SSAS database and have a processing package built that only processes the last 2 fact partitions of each measure group based on their last processed date.

The First Method

Firstly, we look at a very conventional arrangement that would process dimensions, partitions and indexes.  This looks like the image below.  In order to get the most performance (lowest processing time), we need to specify that each processing task uses parallelisation.

image

The code is similar to prior posts so we wont dwell on it too much.  Basically, we use AMO (note the reference to the AMO dll in the first line) to connect to our cube, discover dimensions and partitions and these in an iterative manner to each processing task.  The two methods in the C# code of importance are DimensionKeys (which returns all dimensions in the database) and Partitions (which returns the last two processed partitions for our intended measure group).

<#@ assembly name="C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.AnalysisServices.dll" #> 

<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="Microsoft.AnalysisServices" #> 

<#@ template language="C#" #> 

<#
    String _ssas_server = @".\SQL2008R2";
    String _ssas_database = "Adventure Works DW 2008R2";
    int _max_partitions = 2;
#> 

<#+ 
Dictionary<string, string> DimensionKeys(string Server, string Database)
{
     Dictionary<string, string> _dimensions = new Dictionary<string, string>();
     Server _ssas_server = new Server();
     _ssas_server.Connect(Server); 

     Database _db = _ssas_server.Databases[Database];
     foreach (Dimension _dim in _db.Dimensions)
         _dimensions.Add(_dim.ID, _dim.Name); 

     _ssas_server.Disconnect();
     _ssas_server.Dispose(); 

     return _dimensions;
} 

public static List<PartitionStruct> Partitions(string ServerName, string DatabaseName, string CubeId, string MeasureGroupId)
{
        var _ret = new List<PartitionStruct>(); 

        Server _ssas_server = new Server();
        _ssas_server.Connect(ServerName);
        Database _db = _ssas_server.Databases[DatabaseName];
        Cube _c = _db.Cubes[CubeId];
        MeasureGroup _mg = _c.MeasureGroups[MeasureGroupId]; 

        foreach (Partition _p in _mg.Partitions)
        {
                _ret.Add(new PartitionStruct(_p.Name.ToString(), _p.ID.ToString(), _p.LastProcessed));
        }
        _ssas_server.Disconnect();
        _ssas_server.Dispose(); 

        _ret.Sort((y, x) => x.PROCESS_DATE.CompareTo(y.PROCESS_DATE)); 

        return _ret;
} 

public struct PartitionStruct
{
        public readonly string PARTITION_NAME;
        public readonly string PARTITION_ID;
        public readonly DateTime PROCESS_DATE;
        public PartitionStruct(string partition_NAME, string partition_ID, DateTime process_DATE)
        {
                PARTITION_NAME = partition_NAME;
                PARTITION_ID = partition_ID;
                PROCESS_DATE = process_DATE;
        } 

} 

#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <AnalysisServicesConnection Name="olap" ConnectionString="Data Source=<#= _ssas_server #>;PROVIDER=MSOLAP;Impersonation Level=Impersonate;" Server="<#= _ssas_server #>" />
  </Connections>
  <Packages>
    <Package Name="SSAS_PROCESSING_ssas_process_all" ConstraintMode="Linear" ProtectionLevel="EncryptAllWithUserKey">
         <Tasks>
            <AnalysisServicesProcessing Name="Process_Dimensions_Update" ConnectionName="olap" ProcessingOrder="Parallel" >
              <ProcessingConfigurations>
                <# 
                Dictionary<string, string> _dimensions = DimensionKeys("Data Source=" + _ssas_server , _ssas_database);
                foreach (string _dim in _dimensions.Keys){ #>            
                        <DimensionProcessingConfiguration DatabaseId="<#= _ssas_database #>" ProcessingOption="ProcessUpdate"  DimensionId="<#= _dim #>" />
                <# } #>
                  </ProcessingConfigurations>
            </AnalysisServicesProcessing>
            <AnalysisServicesProcessing Name="Process_Partitions_Data" ConnectionName="olap" ProcessingOrder="Parallel">
                <ProcessingConfigurations>
                <#    
                Server _ssas_server_ = new Server();
                _ssas_server_.Connect(_ssas_server); 

                Database _db = _ssas_server_.Databases[_ssas_database];
                foreach (Cube _c in _db.Cubes) 
                { 
                    foreach (MeasureGroup _mg in _c.MeasureGroups)
                    {  
                        List<PartitionStruct> _listPartitions = Partitions(_ssas_server, _ssas_database, _c.ID, _mg.ID);
                        for (int _i = 0; _i < _max_partitions & _i < _listPartitions.Count; _i++ ) { #>
                        <PartitionProcessingConfiguration DatabaseId="<#= _db.ID #>" CubeId="<#= _c.ID #>"  MeasureGroupId="<#= _mg.ID #>" PartitionId="<#= _listPartitions[_i].PARTITION_ID #>" ProcessingOption="ProcessData" />
                     <# }
                    }
                    } 
                    #>
                </ProcessingConfigurations>
            </AnalysisServicesProcessing>
            <AnalysisServicesProcessing Name="Process_Indexes" ConnectionName="olap" ProcessingOrder="Parallel">
                <ProcessingConfigurations>
                    <# 
                    _db = _ssas_server_.Databases[_ssas_database];
                    foreach (Cube _c in _db.Cubes) { #>
                    <CubeProcessingConfiguration ProcessingOption="ProcessIndexes"  DatabaseID="<#= _db.ID #>" CubeId="<#= _c.ID #>"/>
                    <# } 
                       _ssas_server_.Disconnect();
                    #>
                </ProcessingConfigurations>
            </AnalysisServicesProcessing>
        </Tasks>
    </Package>
  </Packages>
</Biml>

A slight word of caution … If you look into the biml code, you will see that we explicitly set the ProcessingOrder as parallel.  This specifies that the Processing Order (mode) for the task should be in parallel and that the batch XMLA sent to your SSAS Server should include that option.  Unfortunately, this may not materialise on all versions of BIDS & BIML and the default (sequential) may be used.  I would check the outcome before implementation.

Method 2

Our second approach is essentially the same as the first, however, in this approach, we encapsulate each each measure group processing command in its own container with sequential constraints between each partition process.  Why?  Because we want to ensure that our partitions are processed according to last processed date.  That is, the last 2 processed partitions should be processed and we should process them in the order that we originally processed them.  If we add a new partition, we want to ensure that the one with the oldest data drops off the list.  You’ll also notice that we use SSIS containers to gain parallelisation for each Cube and Measure Group.

This code relies on the same methods (DimensionKeys and Partitions) used in the prior to build its output.

image

<#@ assembly name="C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.AnalysisServices.dll" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="Microsoft.AnalysisServices" #> 
<#@ template language="C#" #> 

<#
    String _ssas_server_name = @".\SQL2008R2";
    String _ssas_database_name = "Adventure Works DW 2008R2";
    int _max_partitions = 2;
#>
<#+ 

Dictionary<string, string> DimensionKeys(string Server, string Database)
{
     Dictionary<string, string> _dimensions = new Dictionary<string, string>();
     Server _ssas_server = new Server();
     _ssas_server.Connect(Server); 

     Database _db = _ssas_server.Databases[Database];
     foreach (Dimension _dim in _db.Dimensions)
         _dimensions.Add(_dim.ID, _dim.Name); 

     _ssas_server.Disconnect();
     _ssas_server.Dispose(); 

     return _dimensions;
} 

public static List<PartitionStruct> Partitions(string ServerName, string DatabaseName, string CubeId, string MeasureGroupId, int PartitionNum)
{
        /* returns a number of partitions (PartitionNum)
         * based on their processed_date -> ie last n processed partitions
         * sorted in asc order
         */ 
        List<PartitionStruct> _ret = new List<PartitionStruct>(); 

        Server _ssas_server = new Server();
        _ssas_server.Connect(ServerName);
        Database _db = _ssas_server.Databases[DatabaseName];
        Cube _c = _db.Cubes[CubeId];
        MeasureGroup _mg = _c.MeasureGroups[MeasureGroupId]; 

        foreach (Partition _p in _mg.Partitions)
        {
                _ret.Add(new PartitionStruct(_p.Name.ToString(), _p.ID.ToString(), _p.LastProcessed));
        }
        _ssas_server.Disconnect();
        _ssas_server.Dispose(); 

        _ret.Sort((y, x) => x.PROCESS_DATE.CompareTo(y.PROCESS_DATE)); 

        /* get first PartitionNum */
        while (_ret.Count > PartitionNum)
                _ret.RemoveAt(PartitionNum); 

        // ret asc
        _ret.Sort((x, y) => x.PROCESS_DATE.CompareTo(y.PROCESS_DATE)); 
        return _ret;
} 

public struct PartitionStruct
{
        public readonly string PARTITION_NAME;
        public readonly string PARTITION_ID;
        public readonly DateTime PROCESS_DATE;
        public PartitionStruct(string partition_NAME, string partition_ID, DateTime process_DATE)
        {
                PARTITION_NAME = partition_NAME;
                PARTITION_ID = partition_ID;
                PROCESS_DATE = process_DATE;
        }
}
#> 

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AnalysisServicesConnection Name="olap" ConnectionString="Data Source=<#= _ssas_server_name #>;PROVIDER=MSOLAP;Impersonation Level=Impersonate;" Server="<#= _ssas_server_name #>" />
    </Connections>
    <Packages>
        <Package Name="SSAS_PROCESSING <#= _ssas_database_name #>" ConstraintMode="Linear">
            <Tasks> 

                <AnalysisServicesProcessing Name="Process_Dimensions_Update" ConnectionName="olap" ProcessingOrder="Parallel" >
                      <ProcessingConfigurations>
                        <# 
                            Dictionary<string, string> _dimensions = DimensionKeys("Data Source=" + _ssas_server_name , _ssas_database_name);
                                foreach (string _dim in _dimensions.Keys){ #>            
                                    <DimensionProcessingConfiguration DatabaseId="<#= _ssas_database_name #>" ProcessingOption="ProcessUpdate"  DimensionId="<#= _dim #>" />
                        <# } #>
                      </ProcessingConfigurations>
                </AnalysisServicesProcessing> 

                <Container Name="FACT_PROCESSING" ConstraintMode="Parallel">
                    <Tasks>
                    <#
                    Server _ssas_server_ = new Server();
                    _ssas_server_.Connect(_ssas_server_name);
                    Database _db = _ssas_server_.Databases[_ssas_database_name];
                    foreach (Cube _c in _db.Cubes) { #>
                        <Container Name="CUBE <#= _c.Name #>" ConstraintMode="Parallel">
                            <Tasks>
                                <# foreach (MeasureGroup _mg in _c.MeasureGroups){#>
                                <Container Name="MEASURE GROUP <#= _mg.Name #>" ConstraintMode="Linear">
                                    <Tasks>
                                        <# List<PartitionStruct> _listPartitions = Partitions(_ssas_server_name, _ssas_database_name, _c.ID, _mg.ID, _max_partitions);
                                           foreach (PartitionStruct _partition in _listPartitions) { #>
                                              <AnalysisServicesProcessing Name="PARTITION <#= _partition.PARTITION_NAME #>" ConnectionName="olap">
                                                  <ProcessingConfigurations>
                                                      <PartitionProcessingConfiguration CubeId="<#= _c.ID #>" DatabaseId="<#= _db.ID #>" MeasureGroupId="<#= _mg.ID #>" PartitionId="<#= _partition.PARTITION_ID #>" ProcessingOption="ProcessData" />
                                                  </ProcessingConfigurations>
                                              </AnalysisServicesProcessing> 
                                        <# }#>
                                    </Tasks>
                                </Container>
                                <# }  #>
                            </Tasks>
                        </Container>
                    <#} #>
                    </Tasks>
                </Container> 

            <AnalysisServicesProcessing Name="Process_Indexes" ConnectionName="olap" ProcessingOrder="Parallel">
                <ProcessingConfigurations>
                    <# 
                    _db = _ssas_server_.Databases[_ssas_database_name];
                    foreach (Cube _c in _db.Cubes) { #>
                    <CubeProcessingConfiguration ProcessingOption="ProcessIndexes"  DatabaseID="<#= _db.ID #>" CubeId="<#= _c.ID #>"/>
                    <# } 
                       _ssas_server_.Disconnect();
                    #>
                </ProcessingConfigurations>
            </AnalysisServicesProcessing> 

            </Tasks>
        </Package>
    </Packages>
</Biml>

Conclusion

You might suggest that this process is a very long way to build a processing schedule – and you may be right.  If you had to build it from scratch once to create a package that does some processing, you might have achieved it faster by hand.  However, here’s the thing about automation … you don’t have to code it, its there for you can you can use it to build out your packages and now package creation takes 10 sec!