The BI Portal offers custom value formatting, which makes support for Time Spans easy.  Here is a snapshot of the 3CX cube data, displaying Talking Durations as well as common Time Calculations, automatically done in the cube:

SSAS handles by default the selection using a logical “OR”. In the previous example, both products will be displayed since both of them contain Feature 1.   The resulting matrix from cross joining the products with the features generated in unusable number of records, even after applying the “Feature” filter.  This was an acceptable solution.  The client wanted to see ONLY the products, which had all the selected features.   In technical specs, a logical “AND” must be applied on the “Features” filter.  

I came with a solution using the “Existing” function to detect what features were selected in the filter and then count them.  The products and features dimensions were joined thru a regular relationship to a measure group called “ProductFeatures”.  The measure group contained “Feature Count” measure, which did simple count of how many features were in a given product.   If the feature count and the “existing” count were the SAME, then the product should be displayed.  So the MDX prototype was “with member [Current Feature Count] as iif(count(existing [Features]) = [Measures].[Feature Count] , [Measures].[Feature Count], NULL) select non empty [Current Feature Count] on 0, [Products] on 1 from [Features]” and worked fine in most cases.

In some cases, the above solution does not work.   The reason for that was the difference in the behavior of the “Existing” function.  If the “Feature” filter selection was placed in the “WHERE” clause of the MDX statement, then it worked as expected.  BUT if the filter selection was placed in the sub-cube clause, then the “Existing” function returned all feature members (unless the Feature dimension was placed on one of the Axis).  That made no sense but there was nothing to be done to change the “Existing” behavior.

Here are some examples of the “Existing” function behavior:

a)  “select…from…where ({Feature1,Feature2})” – this works fines with a count of two (out of 10 total features) – since the “feature” dimension is the “WHERE” clause (i.e. one of the Axis).
b) “select {Feature1,Feature2} on 0 from…” – this works fines with a count of two (out of 10 total features) since the “feature” dimension is selected on one of the Axis.
c) “select…from (select {Feature1,Feature2} on 0 from….)” – this does NOT work and comes with a count of 10 (out of 10 total features) since the “feature” dimension is NOT placed on one of the Axis.

Again, the solution with counting the existing members works fine for most cases.  If the client software generates MDX with a “sub-select/sub-cube/” then the above solution will NOT work due to the misbehaving “Existing” function. 

I took it as a challenge to make the solution suitable for all client tools (including filters in the sub-select/sub-cube).   I did some digging and found that ASSPs were needed to detect what members were selected in the “sub-select/sub-cube”; basically fix the “Existing” behavior issues.  I added some new functions to the BI Portal SSAS DLL and was able to implement the solution with success.  Yes, it’s possible but it’s not an easy task.  Here is a snapshot of my end result:

The popup dialog box (shown below) allows the user to configure the subscription. Most of the configuration options are intuitive and self-explaining. For example: a) Description (i.e. Daily Sales Report Delivered at 4am via Email); b) Delivery Method: Email or File Share; c) Recipients email addresses; d) Rendering Format (i.e. PDF, Excel, Word, Image, HTML, etc.); e) Delivery Schedule (i.e. Time, Frequency, Private or Shared); f) Report Parameters values; and more. The options are well documented in the SSRS on-line help.

The BI Portal extends the default subscription capabilities by offering the option of report delivery based on a condition or business rule. The condition is defined either by a) using a statement (which can be configured by selecting the data source and providing the statement); or b) using a report (the UI allows for report selection) – in both cases, the statement and the report needs to return a single cell, which evaluates to either “0/1” or “True/False”). If the value is “1/True”, the report will be sent. This is a powerful feature, which allows the user to receive reports ONLY when a certain condition is met – for example: if the Sales are down 10 percent from last week, then the report will be sent. This will eliminate a lot of unneeded reports, saving the server resources in preparing  them and user time in reviewing them.

The final step is for the user to save the new subscription and optionally, test it by clicking on the “Run” button. Hint: all the user subscriptions are listed in the left panel; each subscriptions has a different icon indicating its type. If the icon contains a database image, the subscription is data-driven. The user can also click on the “Refresh” button and check the status of the selected subscription in the right panel at the top “Status” label (i.e. after Running the subscription manually)



The DDS is a variation, which allows the distribution of a given report to multiple users while customizing its content for each user. The user needs to provide a SQL statement, which retrieves a list of users as well as their preferences for the report customization/rendering. Any of the options listed under the Standard Subscription (and described above) can be customized. For example: the SQL statement “select 'User1' as [To], 'PDF' as RenderFormat union select 'admin' as [To], 'Excel' as RenderFormat” can be used to create a DDS, which will target “User1” and “admin” as the recipients, and the report will be rendered in PDF and Excel formats for each user correspondingly. Think of the DDS as a preliminary step, during which the SSRS engine executes a statement and retrieves a record set from a relational database; for each record, the engine create on-the-fly standard subscription using the values in the different fields (i.e. [To], [RenderFormat], [IncludeLink], etc.). Note: For options which are not included the statement, the engine will use the default option provided in the standard subscription definition. The user needs to provide a mapping between the resulting set columns and the subscriptions options.

How can we create a DDS in SSRS? The steps are the same as for a Standard Subscription, but in the context menu the user needs to select “Create Data-Driven Subscription”. The popup dialog and settings are the same as before, except for “Data-Driven Parameters” extra button at the bottom (highlighted in Red in the snapshot below). This button allows the DDS specific options to be configured; clicking it will display the dialog below. The user first needs to select what Data Source will be used for executing the SQL statement/query and retrieving the users’ specific settings. The Query/SQL statement is provided next; it defines what fields/columns will be returned as well as the actual data (which usually comes from a relational DB table). The “Get Fields” button will execute the statement against the “Data Source” and will retrieve a list of fields/columns; this list will be available in the combo boxes in the next section. (Note: The dropped down combo box in the snapshot displays the list of returned columns after clicking on the “Get Fields” button.) The user then needs to provide the mapping between the returned fields/columns/ and the DDS settings/options/. In the example below, only the “To” and “Render Format” settings are mapped/set/ - this basically says: for each record in the returned set, use the “To” column value to get the recipient list, and the “RenderFormat” column value to specify the user preferred report format (i.e. PDF, Excel, etc). The final step is for the user to save the subscription and optionally test it by hitting the “Run” button and “Refresh” to check for errors.

To summarize the DDS execution flow: the SSRS engine uses the selected Data Source to execute the specified “Query”. The returned records are iterated, and for each record, the specified mapping is used to “fill in”/configure/ the currently created subscription – i.e. the “To” column will give the recipients list, the “RenderFormat” column will provide the current user preference, and so on. IMPORTANT: the Data Source MUST NOT require any user input (such as credentials) or any configurations coming from the assumption of an active user; the data source (DS) is used WITHOUT a current user or any user interactions.  The engine should be able to open the DS connection directly in “lights-out” fashion. In other words, specify in the DS definition the user credentials – do NOT use “current user identity” settings since there is no current user during the DDS execution!

Everything so far is standard DDS functionality. The BI Portal adds the “Report Delivery Condition”, which is a very powerful feature in controlling when a report will be sent out to a given user; this option is covered above and it’s behavior is the same with DDS. A good example will be the distribution of the “Daily Sales” report to all the sales managers in a company; if the business rule for sending the report is the same one from above, only the managers with declining sales from last week will get the report!

The information so far is a coverage of the Report Subscription and the DDS topics. The blog is about Custom Security and DDS. This is where the BI Portal solution shines.

The DDS is a very powerful feature for the Windows-Domain Enterprise Environment but it’s not very Internet friendly. In other words, it does not work well with Custom/ASP.NET Membership/ security, The BI Portal extends the DDS and makes this feature available for Internet Users. I have covered in a previous blog how our solution offers a custom security with a Single-Sign-On (SSO) for SSRS, SQL, and SSAS. This functionality is extended to cover the DDS as well. When a Data Source is define with the ESS Data Source Extensions for MS SQL and SSAS (and OLEDB Sources), there is an option, which says that “Credentials are not required” – this enables the SSO identity and its flow into the DDS.
When the DDS is executed, each user is impersonated when the data source connection is opened, and the data is filtered using the user’s identity! This customizes the report content to match the security clearance of each user. (Note: the alternative solution are a) to create multiple data sources (with different credentials) and matching reports (not a DDS solution); or b) to use “Expression Based” connection string, or c)… in any case, the resulting solution is very complicated and difficult to support and extend)
Here are some examples of the BI Portal DDS solution at work. The snapshot bellows shows several pre-defined subscriptions; the top three are DDS’ (with the DB image in the icon). All of them have been created with the steps described above. The BI Portal Data Source Extensions are used in the reports so SSO can be used during the DDS. Two users are used in the tests: “User1” and “Admin”. Both MS SQL and SSAS data sources are used by the subscribed reports to demonstrate the data filtering capabilities for the different users.

Let’s start with the “GIS Report” report, which uses the SSAS as a data source ; custom security is in place. When I click the “Run” button, I receive two reports: one for “User1”; and another one for “Admin”. Here is a snapshot of these reports. A you can see, “User1” gets the report as a PDF file and can only see “US and Canada”, while “Admin” can see all the regions and prefers the Excel format. One Data Source, One Report – multiple users served. 

The second report is the “Sql Security Test”, which uses the MS SQL as a data source and custom security with SSO. Running the subscription results in two emails with embedded report. Here is a snapshot of the reports. Again, each user only sees the records, for which s/he’s been given security clearance. 

The DDS feature requires the Enterprise Edition of SSRS. The BI Portal solutions brings this feature to the MS SQL Standard and Express/Free/ Editions. One of our clients requested the DDS concept but with custom reports; the BI Portal offers ad-hoc reports based on SSAS. We offered a custom service, which prepares and sends ad-hoc and other custom reports (i.e. by “custom”, I mean non-SSRS reports). Eventually, we discovered that the DDS boils down to executing a SQL statement against a Datasource and retrieving a record set.  Then for each records (after making the mapping adjustments), we can fire an event in SSRS and accomplish the same functionality offered by the standard DDS. Since we don’t have any SSRS version dependencies, our DDS implementation supports all version of SSRS including the free edition. In other words, we bring DDS to everyone.

In summary, the BI Portal solution offers custom/asp.net/ security for the Data-Driven Subscription in SSRS. With a single identity (aka SSO), the user can access data in both MS SQL and SSAS. There is no need for the administrator to create multiple data sources and reports – one for each security requirement. The data is securely served on the Internet with filtering done for each user or assigned role. The DDS takes advantage of the custom security and the SSO. With one report and one data source, unlimited number of users with different security needs can be served.

SSAS is a file-based database engine.  It reads data from different files, makes the joins, and services the requests.  All the files requests are going the System File Cache so the overall used memory may not be necessarily found only in the SSAS process.  I have already blogged about how file reading and memory usage is done at the OS level. The same applies for SSAS – it makes a file a request to the OS for file; the OS checks to see if the file is in the Cache.  If it’s not, the file is retrieved from Disk, placed in the Cache, and returned back to the SSAS (aka hard fault).  If it’s found in the Cache, it’s returned back to the process (aka soft fault).  Hence, when analyzing SSAS memory usage, we need to consider the System File Cache.   There is an excellent tool “RAMMap.exe” from Sysinternals, which gives you a list of all cached files.  You can use the tool to view what SSAS files are cached at the OS level. Note: If you run the exe with a text file name as a parameter (i.e. “RAMMap.exe output.txt”), the tool will export all the file paths into a text file; we considered embeding the tool and giving you this list into our app, but we gave up on the idea due to the size of the output file (300Mb on 450GB cube) and the limited value delivered by the report.

I am going to use a server with 1TB of physical memory in the examples to follow. It makes it easier to see the difference in memory behavior when events happened.  Speaking about System File Cache, there is an option in the msmdsrv.ini file called “LimitSystemFileCacheSizeMB”, which controlls how many of the SSAS files will be cached; the cache usage limitation is at the OS level and affects other processes. In the Companion snapshot below, we set the option to “0” which means, that there is no limit; the OS will cache as much as it can (note: this may affect other processes; on the same server, there is a SQL instance running with 100/300GB configured for Max Memory, which is dynamically changed during the ETL).  

So let’s start with several common scenarios, displaying the memory usage and its variations in SSAS:

1. Min/Max Memory: The SSAS properties allow the configuration of the Min and Max memory settings. This is a bit confusing if you apply common sense. The Minimum Memory does NOT mean that this is the minimum amount of memory that will be used by SSAS.  It means that SSAS will start trimming its cache and start taking some other actions if this value is reached!  in my experience, the SSAS will stop accepting new connection when its memory consumption goes above this value.  Compared to MS SQL, there is not YET a concept of an administrator connection in SSAS, so if the server decides not to take new connection requests, you are out of luck. (Note: inside our service, we do maintain an open connection, which we call Admin connection, so we can be sure that we can talk to the server, when new connections are NOT accepted.  This allows us to perform management tasks if needed.)   So when the “LowMemoryLimit” is surpassed by the current usage, the server will stop accepting new connection; in other word, it will NOT talk to anyone new.   It continues to execute its currently running queries, and as far as the outside world is concerned, the service is simply NOT responding – so the usual approach is: let’s kill the process and restart it! Note: Our software handles this condition automatically, by terminating the most expensive queries until the server becomes “responsive” – please, see the blog on Resource Governor for SSAS, which we introduced as a concept back in 2004! When the “HighMemoryLimit” is reached, the SSAS is supposed to start automatically terminating queries…but the exact details about the server behavior with this setting as well as the “TotalMemoryLimit” one are not very clear.  Anyway, we have in production our software, which successfully prevent the “non-responsive server condition” and automatically handles it! Put it differently, without our software, the SSAS is restarted multiple times a DAY due to clients complains; with our software, the server is NEVER restarted due to non-responsiveness!  So if your server is not responding, most likely it’s processing expensive queries…check your PerfMon counters.    

    

   Here is an example of when the sever becomes non-responsive but in reality, it simply continues to process the current requests, while refusing new work due to lack of resources. The “Active Requests” chart show “bubbles/requests”, which represent current actively-processed requests with diameter representing their duration times (i.e. the bigger, the longer the duration of the request). The drop in the Memory usage below the “Limit Low” represents our software taking proactive actions and terminating expensive requests until the server is back to a responsive state! Please, notice that we are talking about 900GB of physical RAM; check also the “In Use” System Memory, “Standby Cache”, and “Free” Memory.  

    

    

   
   

   The High and Low memory setting can be changed dynamically without a server restart and as you can see from the snapshot below, the changes will take effect immediately:

    

    

    

   
   
   
   

2. Normal Memory with a Query: below is a snapshot of a single query and the IO footprint it causes.   Let me point you to the interesting facts.  a) The CPU usage is high which most likely means that data is retrieved from the Storage Engine (since it’s a multi-threaded data access); b) The “System Files/IO” chart shows high Logical IO but NO physical IO, which means that the data is coming from the System File Cache and the process memory containing cached data!

1. Backup:  Below is an example of Memory and IO usage while a Backup is taking place. There are several interesting observations to point out.   Let’s start with the “Stanby”(593GBs) and “In Use”(293GBs) memory in the System Memory chart, and a total of 193GB in "Shrinkable,Nonshrinkable, and Other" memory used by SSAS in the "Usage[GB]" chart. 100GBs are allocated to MS SQL so we get a good match between what’s reported at the OS level and at the SSAS level (293GBs vs. 193GBs).   At this point, the logical question from an MS SQL professional will be: why only 193GB when the cube is close to 880GBs?  Check out the 592GBs of Standby cache; further analysis with the “RAMMap” tool will reveal that the OS has cached all the SSAS file, and the reported “592GBs” of Stanby cache is actually used for caching SSAS files! This is very helpful if you need to restart the SSAS instance  or reprocess of the SSAS files; the cached files are still cached (i.e. 592GBs) after the restart! Note: another interesting observation is the central chart “Logical IO by Process” and its neighboring chart “System IO”. The “Logical IO by Process” chart displays what processes are causing the Logical IO on the System; hovering the mouse over the chart will give you this dynamic information. In our case, we have SSAS causing the most of the IO (i.e. SQL is the other process on this server, which may be responsible for high IO).  The “System IO” chart allows us to compare the “Logical” vs. “Physical” vs. “Cached” IOs on the system; in this case, we ca see that the Logical and the Physical IOs are overlapping, which is common when we are doing a Backup of the cubes. The Cached IO is of little use in this case.         

1. The Famous Storage Engine (SE) vs. Formula Engine (FE) processing times:  This is a common topic worth covering.  Below are  a couple of snapshots with SE and FE queries. Here is a brief review. The first chart represents an SE query. Characteristic: Multi-threaded data retrieval from the storage engine (i.e. higher CPU utilization), high IO, and not much processing of the data (i.e. calculated cells are low!).  As you can see, the CPU is relatively high on a 64 CPU system! The Logical IO is also high (even though it’s coming from Cache!).   The second snapshot represents a FE query.  Characteristics: Single-threaded data processing (i.e. Lower CPU utilization), lower IO, and high “Calculated Cells” count (see Total Cells Calculated/sec)!  What is important to point out here is the fact that the SSAS service is using very little physical memory (i.e. Zero = 100Gb Total – 100Gb SQL usage) and yet, there is NO physical IO on the system! All data is coming mostly from System File Cache!

    

Storage Engine Query:

Formula Engine Query:

1. Processing Command and Memory Behavior:  Below is a snapshot of the SSAS server behavior during multiple-partition processing. Here are some highlights: a) The SSAS process memory usage will drop since most of the cached data and aggregations will be invalidated (note: two Usage charts are placed next to each other to show the change in memory usage). b) the bottom left "Raw Data" chart shows the connection between the number of rows retrieved from SQL and the number of rows processed by the SSAS sever when processing the partitions – as you can see they match exactly! When you issue a ProcessData/ProcessFull/ProcessDefault command against partitions, the number of SQL/data source/ records will match this counter. When the aggregations are processed, the number of records read from the cube will be shown in the chart next to the right - "Aggregatioins".  This will give you a sense of the processing command progress.   Note: the software contains the “Partition Processing” node, which will give you a visual progress on the raw data/records retrieval from the data source as well as estimate completion times. There will be a separate blog on the topic.

The above examples shows some of my observations.   How can you draw your own conclusions?   We give you the tools to run your own tests.  There are a couple of features of our software that can assist you with these tasks. The first one with the “Stress Test”; it allows you to select queries, parameterize them (optional), and execute them in parallel against the server while watching its behavior (note: there is a separate blog on this task). Second, the “Cache Warmer” allows you pre-warm the SSAS and System File Cache with commonly used data; the tool also allows you to empty either  the SSAS or/and the System File cache, so you can analyze queries behavior under different conditions and scenarios (Note: there is a separate blog on this task)  Both features give you the option to empty either the SSAS or/and the System File Cache.

Memory is an interesting and common topic. I am going to start it with the famous question “Who is going to need more than 64K of RAM?”  My response is that I am able to cause an OutOfMemory exception on a box with 1TB of physical RAM.  Luckily, the operating systems were written when memory was a very scarce resource.  As a foreign student, I did not have the right to work off campus so I was making a good 2.50$/hour after taxes.  Spending 800$ on 16Mb memory chip from the Gateway outlet was a major investment, which eventually paid off with good dividends.  The OS was working with very little memory, attempting to make all the processes happy and satisfy their memory allocation requests.

So how is memory working in Windows? When a process is started, it sees a flat directly addressable memory space with addresses ranging from 0 to the max of its bit-ness (i.e. 32-bit gives 2^32 or 4GBs; 64-bit gives 2^64, etc.). This means that at any time the process executing code can request the content from any of these addresses. This is also known as virtual address space since in reality there are only a few MBs behind it.  So how the OS can trick the process that this space exists?   The main smoke and mirrors are Physical Memory (aka Random Access Memory – RAM) and disk files.  There is the concept of Memory Mapped Files (MMF), which basically map disk files into the process virtual address space.  What that means?  The OS create a map between a starting memory address (say 101) and the first byte of the file; if the file is 50bytes long, then then address 150 will correspond to the end of the file. Every time the process requests the content of an address between 101 and 150, the OS will retrieve the corresponding byte(s) from the file.  Hence it’s called a map – i.e. when you request “a”, it’s looked up in the map, “b” is returned. 

What is the need for this complexity? There are a few reasons:

1.  The OS does NOT have to read the whole file. Have you wondered how a file that is 1GB in size can be opened by some processes instantly, while Notepad chokes on it?  The 1GB can be mapped into the process address space, and ONLY what the process need will be requested by the file (with issuing a memory read request which will be mapped back to the offset within the file). Hence, the OS only needs to make an instant metadata entry, creating the map, and the process is ready to access the file. Alternatively, physically reading the file and copying it into memory will take a while…think Notepad.

2. The same file can be shared among multiple processes.  The same file/or already cached bytes/ can be mapped into multiple processes. A good example is a Terminal Server with a 100 active clients using MS Word.  Without the MMF, the server needs to load 100 copies of MS Word binaries; instead only ONE copy is loaded and it’s mapped to 100processes. As you can see, this makes a very efficient use of the available memory, not to mentioned excellent performance and user experience.       

3. The mapped file is used as an extension to the physical memory, to which the process has access.  Think of it as a page file (indeed it’s an extension the system page file; it uses the same concepts).   Instead of loading the file into physical memory, it mapped to the process and can be access as if it was a physical memory.

Now that we have our 100 TS clients starting MS Word at the same time, what will the OS do? Read a 100 times the same file bytes?  It doesn’t sound very efficient. Instead the OS uses a buffer using the physical memory (RAM), aka Cache, to store the bytes so it will physically read only once from the disk, cache the bytes and on subsequent requests, it will avoid the need to access the disk.  Why avoiding the disk sounds like a big deal?  Memory access is a 1000+ times faster than disk access so there is considerable performance difference. (Note: SSD’s are shrinking this difference.)  The used buffer/cache/ is called File System Cache (i.e. it caches read bytes from files).  Since the Cache is using physical memory, at one point the system may decide to take away the physical memory (which may be needed by a process MemAlloc request). Hence it’s called “Stand-By Cache”.

Let take some of this concept, put them together and map them to the more common terminology used in Windows.  The process has Virtual Memory but only some of this memory is actually backed up by Physical Ram or Disk Files.  When a file can be mapped to one or more processes, then the memory it consumed is called “Shared”, since it is NOT specific to the given process.  Some of the data is specific to the process; hence it's called "Private". An example will be a user Word document.  When the user makes modifications to the document, then the memory, storing the document, is called “Modified” (as well as Private).  The combination of Shared plus Private memory is known as a process Working Set.  When a file/bytes/ is requested, it is first checked if it’s present in the File System Cache. If it’s not found, it is physically read from disk, placed in the Cache, and returned to the process.  This is also known as Hard Fault – when a memory page cannot be found in memory and needs to be retrieved with physically access to the disk.  When a requested file is found, then it’s returned from the Cache – this is also known as Soft Fault.

With this in mind, when analyzing memory, we need to be aware of the flow of bytes in the OS.   To summarize the concepts event at a higher level, multiple processes make concurrent requests, resulting in Logical IO. Out of this requests/IO, some of it will be used from the Cache, and some of it will be retrieved from Disk.   There are Performance Monitor Counters that can give us the current status of the system. The “Process->Data Bytes/sec->Total” will give you the Logical IO. The “Physical Disk->_Total” will give you the Physical IO. The “Cache->Copy Reads/sec” (i.e. copy cached bytes into the process address space) will give you the Cached IO.   Of course, this is a simplified view of the actual flow but it makes concepts easier to digest. The Internet is full with in-depth analysis of the topics above so feel free to dig deeper.  “Windows Internals” is an excellent book to read on the topic.

There are also some common topics about configuration options.  The “3GB” and “AWE” boot.ini option was a hot topic in the 32bit world. When a process is started the OS splits the virtual address into two and it gives 2GB to the user process and 2GB to the kernel.  With some application, such as MS SQL server, there was  a need to access as much memory as possible, so while 2GB are not enough for the process, the other 2 are sitting mostly unused.  The “3GB” basically tells the OS to give an extra 1GB of addressable virtual memory to the process, and leave only 1GB to the kernel…but the “SAN’s” came out with their memory hungry kernel drivers and at one point the 1GB of kernel memory was not enough, so there was a need to further tune the memory “split”…  At the same systems usually has more than 4GB, given than 4GB is per-process, the SQL server need physical memory, which is available but cannot be addressed to the virtual address space limitation of the 32bit OS.  Hence, the Addressable Windows Extensions (AWE) was introduced, which basically allowed windows to be created in a process and mapped to physical memory beyond the 4GB boundary using offsets… Not a new concept, it was heavily used with DOS (Disk Operating System) and the “himem.sys” file (if anyone remembers it).  The topics keep going but alas, most of them are obsolete, the 64-bit OS took care of them.  The extra memory also created other issues.  I have done over 300 performance tuning labs. When I am asked what equipment I need, I go for the slowest machine with the least resources.  This type of underpowered machines makes the performance and other issues appear immediately. So a table scan, a badly written TSQL or MDX, or a memory leak show up pretty early in the stress tests.   An “IIS” worker process will be restarted at 1.3GB of memory usage…so it was easy to isolate problematic areas and fix them. The introduction of more memory and faster disk drive makes this task very difficult.  The OS resource limits were forcing developers to pay more attention to how apps are written and create better apps…but those days are gone.    

Now that we know everything about the OS Memory and how it works, let‘s look briefly at the Disk subsystem.  There are a lot of advance topics (i.e. Scatter/Gather Win32 APIs) on how requests are made to the Disk, and more specifically, how to fill up the 64K buffer so it a single request we can get more work done.  There are also the concepts of IO queuing and Queue depth. Multiple Performance Monitor Counters further complicate the discussion.  Over the year, I found a simple approach to analyzing the Disk IO usage.  First, if you have physical access to the server, look at its disk LEDs – if they blink like a Christmas tree, then there is a very good chance that there is an opportunity for tuning.  Again with more memory, a lot of data is cached and the issues are not as easy to spot.  Second, check the Disk sec/Read and Disk sec/Write Performance Monitor Counters.  They are an excellent indication of how the Disks are performing and whether if they are overwhelmed.  This really simplifies the analysis and is very consistent with its results no matter what technology is behind the disk subsystem.  The counters represent how milliseconds are needed for single disk IO – this also matches the disk manufacture definition for disk performance.  For disk with spindles: a) for transactional systems: 2-4msec are excellent; 4-10msec is Ok, and above 15-20msec is bad; b) for Decision Support Systems (DSS)/Reporting: 15-20msec is excellent; 25-30 is good; above 50-80msec is bad.  These are rough guidelines, which worked for me over the years.

In the spindle-disk world, there are also some other good ideas.  The most common one is: “How to keep the physical head from moving between tracks?” Some of the common answers are: a) don’t place files on the same drive that are used in random-access fashion; SQL transaction log placement discussion fits here; b) use only 50% of the disk and use defrag software which pushes the data to the outer rims of the disk (thus achieving maximum performance since the heads are least moved and the most data can physically fit on a track); c) make sure that the disk partitions are properly aligned (W2003 Server and before were creating default partitions with 512byte off the boundary causing misalignment; W2008+ fixed that); Note: our software checks and reports on partition alignments.

The Solid State Disks (SSD’s) appears was a game changer.  In a few months, all the heavy investments in SANs and spindle-based disks were quickly eroded.   This technology is far superior and becomes the de-facto standard for the computer/data industry.   And again, having faster hardware makes more difficult to optimize systems.   

1) Windows Integrated Authentication:

Out-of-the-box SSAS supports only  Windows Integrated security.  Features that are present in MS SQL eventually make their way into SSAS but not with custom security.   Windows Authentication requires the presence of a Windows Domain; its Domain Controllers (DC) are the trusted authority (the assumption is that everyone trusts them) and are responsible for handling authentication requests (such as: can you prove to the accessed server that I am who I claim to be with my logon credentials).  While this works pretty well in most cases, there are some challenges.  The infamous “multi-hop” authentication is one common example.  Before Kerberos, there was (and still is) the Windows NT LAN Manager (Local Area Network Manager, aka NTLM). It was using a challenge-response algorithm, in which the user provides a set of credentials and the server checks if they are correct with a table lookup.  The disadvantage of this authentication type is that if the user cannot make more than one jump/hop.   For example: a user accesses a web site on one server, the web server (IIS) needs to make a call to a SQL database on another server. In this case, we have two server hops (client->IIS->SQL. The NTML does not allow the originating caller identity to be forwarded on the second hop (IIS->SQL) since it does not know the original password (but rather a hash of it); one exception is the use of “Basic Authentication” where the credentials are transmitted as base64 encoded strings in the HTTP header; hence the web server knows them and can forward them to the next tier/server.   NTML is still in use today; for example: if you have multiple domains that don’t trust each other and you need to access servers/resources in them, you can create the same account in all of them and as long as the passwords are synchronized, you can access the resources without a problem. It’s a common trick to bypass the non-trusting domain environment but it’s also a security concern… 

Back to SSAS, the engine uses “UserName()” function to retrieve the currently-connected Window user identity. The function returns a string with the logon and can be used in configuring the SSAS security (not covered in this article). For example: SSAS can be configured that user “Ivan” has access to “East and Central US”; the two geo members are assigned to “Ivan” so when he logs in, the data is automatically filtered for only these two regions.  Another way of describing this is that the MDX is adjusted to filter out all but these two regions.  Configuring access to individual user can quickly become a cumbersome, labor-intensive, and error-prone task.  Hence SSAS supports the concept of “Roles”, which is basically a “group” with one or more users. Security is configured for the group, and all its members inherit the security rights and restrictions for the given group.  Users can also belong to multiple groups and the security is additive – i.e. most rights (vs. Windows with least rights) – a union vs. an intersection of multiple group rights).  Configuring the security inside SSAS is usually done thru an extra “User” dimension, which is related to different fact tables/measure groups/. These relationships are then used to filter the current user and the data that will be returned.  For example: Dimension “Users” contain a member “Ivan”; “Ivan” has access “East and Central” Geo dimension members. When “Ivan” accesses the cube, the engine will resolve/get his identity thru the “UserName()” function call and then it will find the Dimension “Users” corresponding member (i.e. using “StrToMeber” function) and then it will filter the data for only the two Geo Dimension members. 

SSAS also supports “EffectiveUserName” connection string option. This option allows for a multi-hop scenario where the service account has elevated privileges and can impersonate the user.  SharePoint and PerformancePoint are good examples of using this approach; in my experience, it’s very difficult to debug and support these types of installations!   The “Roles” connection string option is another good trick to provide some flexibility in integrating applications; the SSAS client (i.e. usually the web/mid-tier server) can embed the roles that are needed for the current user, and if the connecting account has elevated rights, it can include this option in the connection string, so when the connection is opened, SSAS will ONLY apply the rights given the provided Roles! Both of these two options are commonly used to integrate custom security web apps with SSAS.  

There is much more on the topic but it’s well covered in the whitepapers published on the Web.

2) Anonymous Authentication

This is the simplest authentication method for SSAS. The engine is told that NO authentication and authorization (A&A) should be performed so anyone is allowed to access any data.  The SSAS properties has a configuration option ‘Security\RequireClientAuthentication’ that can dis/enable the authentication service level.  Alternatively, a role can be configured and everyone/guest is make a member of it; the role is then given full access to the cube (i.e. not administrator rights are implied).

3) Custom/Internet Authentication

This type is commonly requested since the industry trend is to push data to the Internet.  The basic algorithm for authenticating a user, is to retrieve the credentials in clear text (note: commonly over Securied Socket Layer – aka SSL used in HTTPS comm channel security), and then for a custom algorithm to do a look-up against a relational database table or a flat file. Example: “select * from tblUsers where UserName = ‘Ivan’ and Password = ‘my password’”.  If a record is return then the user is authenticated/valid. It’s a simple implementation and works very reliably.  It’s worth mentioning that I’ve seen a lot of enterprise systems where user inputs are not checked and SQL-injections attacks are possible (for example: if the user name is “’Ivan’--“ the extra dashes will comment out the password check in TSQL and the password check will be skipped!).  This issue has been drastically eliminated over the years.    The password should be also stored as “salted hash” instead clear-text; this will prevent internal employees to access the users passwords in clear text! 

Back to SSAS, the engine does NOT support this form of authentication or provided an elegant way to implement it in SSAS.  The most common workaround is the use of another connection string option: “CustomData”, which basically allows the client to pass some text data into the SSAS session it’s about to open. An example: Connection.Open(“Data Source=SsasServer; Initial Catalog=AW; CustomData=Ivan”) – when the connection is established, the MDX “with member [User] as CustomData() select [User] on 0 from [MyCube]” will return “Ivan”.  The “CustomData()” call returns the information that was passed from outside SSAS and can be used while configuring the SSAS security.

A step further is to use SSAS stored procedures (SP) to extend the functionality of SSAS.   A popular common approach is to use such an SP, which in turns calles into a SQL database and passes either the UserName() or CustomData() outputs.  The SQL calls retrieves a set of members from a table and returns it back to SSAS, which in turn uses the “StrToSet()” function to resolve the output set in Dimension Members and use them for filtering.  This is a very effective way to provide DYNAMIC security  in SSAS; a change in the SQL table records will immediately result in different security inside SSAS!  There are a couple of caveats to watch out for with this approach.  First, the security stops working one in a while; luckily the returned SSAS data is empty so there is no security breach. The setup needs to be reset to get it going.   Second, if your are an SSAS administrator, the whole model is bypassed, which makes it difficult to test and troubleshoot.  Also, watch out which stored procedure is being called: SSAS or SQL, use proper naming of the procs!

4) External SSAS Authentication  

Enterprise Software Solution invented another way of doing SSAS security.   I haven’t seen anyone in the industry doing this approach.  Basically, the incoming MDX requests are intercepted and modify to include an extra filter, which limits the sub-cube space for the current user and then performs the request.  The SSAS server does not know anything about security.  The advantages of this approach is that the security is dynamic, there are no disruptions of service mentioned in 3) above, and the solution is easy to support and debug (i.e. it’s not a black box running MS compiled DLLs).   Another major advantage is that Caching is OPTIMAL with the server since all the request are coming from the same user and there is no reason for creating multiple caches!

Our product, “BI Portal”, is a solution, which supports all of the above security authentication models. All the components, needed for the various models, are included.   The “BI Portal” also takes it a step further with its Single-Sign-On feature for SSRS, SSAS and SQL.   The user needs to login only once, and then his/her security identity flows thru SSRS, SSAS, and SQL.  Please, see my “SSAS Security” blog under the “BI Portal” category.

In general, the enterprise-accumulated data (aka data warehouse) is  mostly used for reporting.  The business reporting needs are usually around some sort of aggregation of data. For example: “how much were our sales yesterday?”.  To answer this inquiry, we filter the sales records for yesterday, and we ask that the engine adds up the resulting records individual values and give us a total.  In T-SQL code: “select sum(Sale) from tblSales where Time = <yesterday> group by Time”.   

 So if a relational engine can do it, why do I need SSAS?   Here are a few reasons:

 1) TCO (Total Cost of Ownership) -  The reporting requirements change frequently so the end user may ask that the yesterday totals are broken down by product category.  The existing report either  needs to be adjusted or a new one will be created. In either case, an expensive technical expertise is required.   As soon as the new report is ready, a new requirement may come – perhaps, “totals further needs to be broken down by Employee”…which turns into a never-ending queue for change requests. On top of that, the number of reports explodes in time, which results in high support/ownership/ costs while their usefulness becomes doubtful.  With SSAS, the end user can slice and dice the data with the PC mouse on their own, without the need for technical expertise.  In other words, a static report becomes dynamic and the end user can control what it displays. Note: the SSAS contains metadata (data describing data) so all the relationships between the different tables/data are published and known; this allows for the end user interactively select objects and include them in the report.  

 2) Single Version of the Truth – Creating a single database allows for centralized reporting. Eventually, everyone agrees with the numbers and there is a consistency in the reported data.  Alternatively, different departments run their own reports and the numbers usually do not match. A good example is the sales department want to boost the numbers for higher commissions, while the accounting departments want to lower them to keep cost in check.  A relational database can also provide this service, which leads us into the next reason.

 3) Data Volumes – with disk storage becoming ridiculously low, data collection and volumes have exploded in recent years. It’s not uncommon these days do discuss Terabytes or Petabytes of data. Relational engines are very powerful but even with their pre-aggregation features, they cannot scale well beyond a certain point when it comes to ad-hoc querying and processing of large volumes of data. It doesn’t take much for one user to mistakenly query a non-indexed columns on an extremely large table and bring even the most power servers down to their knees.  SSAS solves the scalability challenge with features such as data partitioning, pre-aggregations, extremely power aggregation engines, and more.  MS SQL engine also has some of those features but it doesn’t scale as good plus it’s not as easy to create ad-hoc reports (addressed in (1) above).

4) MDX (Multi-Dimensional Expressions) Language – T-SQL (Transactional Structured Query Language) is used for querying relational data. MDX is used for multi-dimensional data. MDX is much more powerful language for data analysis.   For example: with MDX, it’s easy to perform Year-Over-Year comparison, Year-To-Date aggregations, Last Period, hierarchy navigations, member references, and much more.  The equivalent requests in TSQL have a tendency to be very challenging to implement and usually result in very slow execution times and expensive server resource usage.

 In summary, MS Analysis Server can give you scalable ad-hoc reporting for anyone who knows how to use the PC mouse (aka self-service BI).

Note: SSAS is a different engine from MS SQL.  Since Microsoft packaged them together, it’s a common misconception that they are the same engine( or very close).  With the popularization of SSAS, I’ve seen quite a few times for existing MS SQL DBAs/DEVs to inherit the responsibility of developing and supporting SSAS deployments.  Due to the difference in the engines and the complexity of the SSAS, this decision should be carefully considered before it’s made.