SQL BI

Memory configuration (no pitfalls!) in SSAS

I recently had some problem with memory configuration of SQL Server Analysis Services 2005 and I'd like to share lessons I got. This post is a replacement for my previous post, that I striked to keep history of some bad assumptions I initially made.

SSAS have some setting that controls the way memory is managed by SSAS2005. Default values probably doesn't take care of concurrent applications and you could want to change them on a server running other services. Settings I'm talking about are defined as parameters of a single instance of SSAS: please note that MS suggest to not touch these settings...

Memory / LowMemoryLimit: it defines the value of physical memory that defines the point at which server is low on memory. Values greater than 100 are absolute values (scientific notation is supported in the config file, msmdsrv.ini). Values under 100 are calculated as a percentage of memory: reference value of memory (that corresponds to 100) is calculated as the minimum between the physical memory and the virtual memory available for a process. We have several cases (for semplicity I show the memory limit calculation for an hypotetic 100 value of the parameter): a 8Gb RAM 32bit Windows has a 2Gb limit (or 3Gb if you have /3Gb option enabled); a 8Gb RAM 64bit Windows has a 8Gb limit; a 1Gb RAM 32bit Windows has a 1Gb limit.

Default is 75 (75%) that is good most of the time, but if you have other processes on the same machine, you could prefer to lower this setting. If you want to know what the actual and running absolute limit is, you can get the exact running value reading the MSAS 2005 Memory / Memory Limit High KB performance counter.

Memory / TotalMemoryLimit: it's a percentage of physical memory that the server will use at maximum. Behaviour and setting considerations are analogous to LowMemoryLimi..

I had notice that in some cases PSS suggested to lower these settings to avoid an Out Of Memory error of SSAS 2005. After my previous wrong assumptions, Eric J of Microsoft helped me to understand what BOL still doesn't explain well. Eric wrote some other hints that we can share and index for future references.

Eric: You should assume they AS2000 and SSAS2005 are completely different products.  There are some similarities in settings but many differences.

Eric: Out of memory error could have been caused by many things, but basically memory could not be allocated from the OS.  Changing config values also impacts the point when the cleaner thread removes "shrinkable" memory.  Watch the perfmon counter "MSAS 2005:Memory\Cleaner Memory Shrinkable KB", and nonshrinkable.

Eric: To observe effects while you change LowMemoryLimit settings you can:

1. Open msmdsrv.ini in notepad, change value, and save file.
2. Open perfmon, report setting, view "MSAS 2005:Memory\Memory Limit High KB"
3. Observe the above listed value.

I already suggested to Eric that BOL could be improved on this topic (existing information here are not so clear).

Memory configuration pitfalls in SSAS

I recently discovered a few pitfalls on memory configuration of SQL Server Analysis Services 2005: the main problem is that the default configuration (written by setup) doesn't consider the actual physical resources (mainly RAM) of your server and this can cause some trouble. Settings I'm talking about are defined as parameters of a single instance of SSAS: please note that MS suggest to not touch these settings...

UPDATE: I completely changed this post due to incorrect information I wrote. Please read correct information here.

UPDATE: I received this comment directly from Eric J. of Microsoft. Your recent post regarding AS2005 memory usage is incorrect.  Memory values are calculated as percentage of min( virtual, physical ), or if greater than 100.0 an absolute value is used (scientific notation is supported in the config file).  The defaults have been found to work well in a variety of scenarios, but there are cases where lower or higher values would work better.  AS2005 administrators may want to monitor the perfmon counters in MSAS2005 Memory.

Excel 2003 OLAP customization with VSTO

Thanks to Romeo Pruno, I just discovered a sample of Excel 2003 customization to access OLAP data through task panes. While this article deserves to be read, I think that client customization is a sort of "last resort", to be applied only for particular customization. I prefer to use clients that are functional-complete by itself and that allows user to customize reports and queries without writing code. For this reason I think this is a good introduction for people who want to build a customized solution, but is not a way to solve the lack of functionality of Excel 2003 (like Search members), many of them will be present into Excel 12.

Report Builder trouble filtering date with UDM

Thanks to a blog reader, I just discovered that if you create a Report Builder Model from a UDM and try to create a report filtering a date field, the date you specify in the filter dialog box is inverted (months with days) when the MDX query is generated. At first glance I thought it was another locale ID compliance problem, but I just tried with en-us and it makes the same error.

The problem shows itself only when you have a day value less or equal to 12. It appears that the conversion is made with CDate function that behaves in that way when it receives a MM-DD-YYYY date format (it inverts the format to DD-MM-YYYY when the DD value is less than or equal to 12). Many thanks to Alberto De Marco who helped me (and many other through this blog) to see this problem.

Now, if someone would have enough time to open a bug with PSS... :-)

Post SP4 hotfixes for Analysis Services 2000

I just discovered that some weeks ago Microsoft released a cumulative upgrades of hotfixes for Analysis Services 2000 released after SP4. I have to say that I noted AS2000 is less stable after SP4 intallation (I can say that after months of use) and I randomly got event ID 132 that appears to be resolved by SP4 but I've never seen before SP4 (btw, it seems that a pretty invasive backup program lock a file that should be deleted when this happens, so it should be not a SP4 responsibility). I have no time to open a call to Microsoft Support (it's a very expensive process) - I hope Microsoft will improve the way a user can submit a bug (service shutdown seems to be a good candidate) in a production system...

An equivalent post-SP4 cumulative upgrades of hotfixes is also available for the relational part of SQL Server 2000.

Alternative approach for SCD dimensions in SSIS?

Tonight I've studied how to improve performance of SCD transformation task of SSIS. I started from the consideration that in an actual SSIS package, the dimension processing is at least one magnitude slower than fact table processing; most of the time is consumed by SCD transformation.

A simple SQL Profiler session showed me how SCD works processing data: for each processed row, a sp_executesql is called with a query that have business key fields as parameter. While the query is executed very fast on the server, the throughput is not very good because we have the latency of communication between different processes (and may be different servers). Client CPU is consumed more than server CPU, probably due directly or indirectly to the sql query string construction (it's a SELECT field, field, field, ... FROM table WHERE businesskey = @P1".

Now, when you have a SCD you could also have a timestamp column on original data that helps you to extract and process only good changing candidates for dimension processing. But when this is not the case, the whole dimension is reprocessed against the last processed dimension, just to find new or changed attribute. This scenario is very common when data are provided by legacy system that are out of our control (otherwise we would have added the timestamp column months before...). It could be wonderful if SCD transformation would have an optional input flow to provide "lookup" sorted data, working in a way similar to the merge task. The wizard could use the business keys as lookup/join condition and SCD would not have to use sql query anymore just to solve the SCD condition to process. Unfortunately this is not the case, so by now the only option is to make your own SCD processing by hand using basic components. But this topic deserves attention by SSIS development team, because this kind of operations are very very common in a data warehouse environment.

CalendarTransform component for SSIS

I just downloaded and tried the CalendarTransform component for SSIS that Microsoft realesed with a shared source permissive license.

It's a useful data flow transformation component that is able to generate apparently all of the attributes you can desire to place into a date dimension. While I think it's a pretty useful component (and you have source code that can be improved!), I'd like to highlight areas of improvement:

• Editor can be more user friendly: you have to go into "Input and Output Properties" and define what output columns have to be generated.
• Add a preview of output, better if provided with sample data: a sample talks more than tousands of words, and in this case you have only one word (the enum member name...).
• It's not clear how to force locale id from SSIS editor: I correctly get italian names for calendar with my default configuration, but order of items in a complete date string is the american one, very strange... Anyway the biggest problem is that you probably want to enforce a localization identifier independently by execution condition, so for a SSIS user it could be not so intuitive as it is for a veteran .NET programmer.
• A little legenda for Format property (who remember differences between MMM, MM and M?): actually is very difficult to get appropriate documentation for date format even from .NET documentation.
• A DateGenerator data source would be very useful: I know it's very easy to write one, but for those very basic features (in a data warehouse environment) it could be better to rely on "standard" components.

This release (together with UnDoubleOut - a quote / double quote transformation helper), suggest me that it would be great to have a common repository for SSIS components, particularly for dataflow components. A simple one I'd like to have is a "dimension lookup" that internally does the work of Lookup component placing a dummy value if the searched application key is not found. I haven't tried it is possible to build it deriving the Lookup component (and if it would work, how to handle the editor user interface?), the problem is that in my understanding it isn't possible to create a dataflow component that aggregates several other dataflow components. Feedbacks on that are welcome!