THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Microsoft Business Intelligence consultant from the Netherlands

  • SSAS – MOLAP, ROLAP and HOLAP storage types

    A big advantage of a BI solution is the existence of a cube. Data and aggregations are stored in a optimized format to offer very fast query performance.
    Sometimes, a big disadvantage of storing data and aggregations in a cube is the latency that it implies. SSAS processes data from the underlying relational database into the cube. After this is done the cube is no longer connected to the relational database so changes to this database will not be reflected in the cube. Only when the cube is processed again, the data in the cube will be refreshed.

    SSAS 2005 gives you the possibility to choose different storage types for the following objects:

    • Cubes
    • Partitions
    • Dimensions

    MOLAP (Multi dimensional Online Analytical Processing)
    MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. Data AND aggregations are stored in optimized format in the cube. The data inside the cube will refresh only when the cube is processed, so latency is high.

    ROLAP (Relational Online Analytical Processing)
    ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube.
    Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.

    HOLAP (Hybrid Online Analytical Processing)
    HOLAP is a storage type between MOLAP and ROLAP. Data will be stored in relational format(ROLAP), so there will also be zero latency with this storage type.
    Aggregations, on the other hand, are stored in multi dimensional format(MOLAP) in the cube to give better query performance. SSAS will listen to notifications from the source relational database, when changes are made, SSAS will get a notification and will process the aggregations again.
    With this mode it’s possible to offer zero latency to the users but with medium query performance compared to MOLAP and ROLAP.

    The different storage types of SSAS:



    Data storage


    Aggregations storage


    Query performance














    Relational database



    Low (none)



    Relational database

    Relational database


    Low (none)



    SSAS offers three storage types that give you all the flexibility you need. You can choose between high performance and high latency on one side(MOLAP) and lower performance but low latency(ROLAP) on the other side. There is also a possibility to choose a way in between(HOLAP).


  • SSIS – Non-blocking, Semi-blocking and Fully-blocking components

    How can you recognize these three component types, what is their inner working and do they acquire new buffers and/or threads?

    Synchronous vs Asynchronous

    The SSIS dataflow contain three types of transformations. They can be non-blocking, semi-blocking or fully-blocking. Before I explain how you can recognize these types and what their properties are its important to know that all the dataflow components can be categorized to be either synchronous or asynchronous.

    ·         Synchronous components
    The output of an synchronous component uses the same buffer as the input. Reusing of the input buffer is possible because the output of an synchronous component always contain exactly the same number of records as the input. Number of records IN == Number of records OUT.

    ·         Asynchronous components
    The output of an asynchronous component uses a new buffer. It’s not possible to reuse the input buffer because an asynchronous component can have more or less output records then input records.

    The only thing you need to remember is that synchronous components reuse buffers and therefore are generally faster than asynchronous components, that need a new buffer.

    All source adapters are asynchronous, they create two buffers; one for the success output and one for the error output. All destination adapters on the other hand, are synchronous.

    Non-blocking, Semi-blocking and Fully-blocking

    In the table below the differences between the three transformation types are summarized. As you can see it’s not that hard to identify the three types.
    On the internet are a lot of large and complicated articles about this subject, but I think it’s enough to look at the core differences between the three types to understand their working and (dis)advantages:





    Synchronous or asynchronous




    Number of rows in == number of rows out


    Usually False

    Usually False

    Must read all input before they can output




    New buffer created?




    New thread created?


    Usually True


    All SSIS transformations categorized:

    Non-Blocking transformations Semi-blocking transformations Blocking transformations
    Audit Data Mining Query Aggregate
    Character Map Merge Fuzzy Grouping
    Conditional Split Merge Join Fuzzy Lookup
    Copy Column Pivot Row Sampling
    Data Conversion Unpivot Sort
    Derived Column Term Lookup Term Extraction
    Lookup Union All  
    Percent Sampling    
    Row Count    
    Script Component    
    Export Column    
    Import Column    
    Slowly Changing Dimension    
    OLE DB Command    

  • SSAS - Visualize Attribute Relationships

    Complicated attribute relationships are difficult to oversee in SSAS 2005. Microsoft solved this problem in SQL Server 2008 with a graphical view of the attribute relationships. Unfortunately, most of us still work with SSAS 2005 and have to wait a couple of months before using the benefits of SSAS 2008.
    Until SQL Server 2008 is fully available it’s good to know there is a way to get some of the new features in SSAS 2005: BIDS Helper.
    It is free to use and offers a lot more than just the visualization of your attribute relationships:
    · Aggregation Manager
    · Calculation Helpers
    · Deploy MDX Script
    · Dimension Health Check
    · Printer Friendly Dimension Usage
    · Show Extra Properties
    · Update Estimated Counts
    · Visualize Attribute Lattice

  • SSRS - Custom expressions for subtotals in a matrix

    If you want custom expressions for your subtotals in a matrix, for example to calculate an average instead of the default sum, you need to use the InScope() and Iif() functions in your data field…

    When you create a matrix with SSRS you get the following default groups:
    A row group named:               matrix1_RowGroup1
    A column group named:          matrix1_ColumnGroup1

    With the normal functionalities you can’t change much on the behavior of your subtotals in your matrix. When you create a subtotal it calculates a subtotal and that’s about it ;-)

    If you use the following expression in the data field of your matrix you can take full control on the behavior of all your subtotals:



                                    "In Cell",

                                    "In Subtotal of RowGroup1"),


                                    "In Subtotal of ColumnGroup1",

                                    "In Subtotal of entire matrix"))


    Replace "In Cell", "In Subtotal of RowGroup1", "In Subtotal of ColumnGroup1" and/or "In Subtotal of entire matrix" with the expressions or fields that you want.

    For example, if you want to calculate an average:

    Replace "In Cell" with Sum(Fields!Amount.Value)

    Replace "In Subtotal of RowGroup1" with Avg(Fields!Amount.Value)

    More information about the InScope() function on MSDN

  • MCTS - I passed the 70-445 exam!

    Last month I passed the MCTS 70-445 BI exam.
    This makes me one of the 338(October, 2007) Microsoft Certified Technology Specialists worldwide! (
    Nr. of MCP's worldwide)

    The 70-445 exam is the first Microsoft exam I tried. After some research on the internet I only found reactions from people that concluded that this exam was a very hard one.
    So why is this exam difficult? I think it’s because of the amount of detail in the questions. You can expect very detailed questions about, for example, properties of SSIS, SSAS and SSRS.

    Let’s look at the exam topics of exam 70-445: (full list:

    Microsoft Learning)
    The following list includes the topic areas covered on this exam. The percentage indicates the portion of the exam that addresses a particular skill.
    • Managing SSAS (12 percent)
    • Developing SSAS Solutions by Using BIDS (18 percent)
    • Implementing data mining by Using BIDs (12 percent)
    • Managing SSRS (13 percent)
    • Developing Reporting Solutions by Using SSRS (19 percent)
    • Developing Business Intelligence Solutions by Using SSIS (16 percent)
    • Administering SSIS Packages (11 percent)

    Notice that data mining is an important topic of this exam. I know that a lot of people score poor on the data mining questions so take your time to master enough knowledge!
    A lot of the questions have focus on deployment and maintenance. This exam is not about, for example, SSIS tasks and transformations but it is about the deployment of SSIS packages and the working and use of transactions.

    Training Kit
    I used the 70-445 Training Kit from Microsoft Press to prepare for the exam:

    This book will help you a lot while preparing for the exam! It is full of effective information and contains a lot of hands-on labs and test questions. A must have, if you ask me!

    Number of questions and completing time
    As last I can tell you that the exam contains 53 questions and you have 150 minutes to complete it. You will have time enough, so don’t worry about it. You need to score 700 points out of 1000 to pass.

    Question types
    The 70-445 exam is multiple choice, there are no questions that you need to answer by typing text.
    There are 3 types of questions:


    This type of question is the easiest one. One answer can be selected only. I think 60% of the questions are of this type.


    This type of question is harder then type 1. More answers can be selected. In this example only 2 answers should be selected, this can vary. I remember questions with 3 answers that should be selected and also questions where you will be asked to pick 1 or more good answers. I think 25% of the questions are of this type.


    Question type 3 is the hardest. You need to slide 1 or more good answers to the right and arrange them in the right order. I think 15% of the questions are of this type.Please not that if you answer 1 of more possible good answers right(with question types 2 and 3) you get 0 points. So no partial correct answers possible!

    Below some links to other useful sites/weblogs about the 70-445 exam:

    If you have any questions, leave them as a comment and I will answer them, if I can. Also, if you have any information regarding the 70-446 MCITP exam, please leave a comment.
    Good Luck!

    Update: I recently passed the 70-446 Microsoft IT Professional(MCITP) 70-446 “PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005” exam and wrote a blog about it. Read it here.

  • SSRS – Invalid row heights, BUG?

    Because I got a few reactions regarding the screenshots below (people thought something was wrong with the screenshots) please note that :

    Some of the screenshots below look awful because I selected all the text in the matrices with CTRL-A. I did this to make the differences in row heights clear to see. I also made the numbers in the matrix unreadable.

    As you can see in the screenshot below the row height of rows that contain empty cells differ from the rows where all cells contain data. Screenshot below is taken from IE(with all rows selected), when I run the report in BIDS preview it renders fine!

    I think this is strange if you look at the properties of the rows during the problems:

    Can Grow and CanShrink are set to False so you won’t expect cells heights to grow or shrink. It seems the problem is that SSRS gives empty cells a row height of 0,25(the default) and I use a row height of 0,20. Result is that everything goes fine until there are rows with empty cells. What’s also very strange is that when I made a print of the report, the row heights were all fine!!

    When I faced this problem I just tried a couple of things and it seemed the padding property gave an outcome. When I run the report with the following properties for the rows it renders fine and there are no problems:

    As you can see in the screenshot from IE below, the row heights are fine now!

    It seems that when you
    a) have a matrix with cells that can be empty
    b) you use a row height that’s smaller then the default of 0,25
    you need to use the following properties for your rows in the matrix to prevent problems:

    · Padding: 2,2,0,0
    · VerticalAlign: Middle
    · CanGrow: False
    · CanShrink: False

    What makes this strange to me is that a print of the report and preview of the report in BIDS did not show differences in row heights but Internet Explorer did. This problem occurred with IE 7 and a SQL Server 2005 installation with SP and updates installed.

More Posts « Previous page
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement