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.   

Prerequisites and Deployment Checklist

Prerequisites

The following prerequisites are required for the proper operation of the Companion for MS Analysis Server:

o  Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider

o  Microsoft SQL Server 2008 Native Client

o  Microsoft Data Access Components (MDAC) 2.8 SP1

Most likely you will not have to install them since these Microsoft products usually are installed with the SQL Server installation.

Deployment Checklist 

Database location and size:

Select a centralized SQL Database server that will be used for storing collected data from all services.

The database size will vary based on your settings. The software is self-trimming, so old data (usually over 90 days) will be trimmed automatically. However please allow at least 1GB for the database.

Services Security:

Determine how the installed services will connect to the database server. If you are going to use SQL server authentication you can create the necessary SQL account with the Database Connection Wizard inside the ASDC Manager. If you are going to use windows integrated security, please pre- create an AD resource account which will be used by the installed services. If you are installing the software on one server and will be storing the data in a local database, then you can use Network service or LocalSystem. All of the security settings are configurable in the ASDC Manager.

Self-Updating Configuration:

If you are going to use the Enterprise Deployment scenario, please contact us and decide which IIS

server inside your network will be used for deployment and decide on the FQDN and URL path.

Firewalls:

The services run on the SSAS server itself. The clients establish secured communications authenticated by Active Directory on ports 10800 + 1 for each installed Instance. So a server with 10 installed SSAS instances will use ports 10800-10809. There is also the option to custom configure ports. This can be done by setting up the default ServerID=0 tcp channel to port=0, and then each SSAS instance’s tcp channel can be manually assigned.

If you have not obtained a license in the SSDC Manager, you will be prompted to either provide a license or obtain one over the internet (60 days full trial or purchase a license).