Summary for "Cache–Warming Strategies for Analysis Services 2008" by Chris Webb

The following is a summary for the very good session held by Chris Webb for SQLBits and Cache–Warming Strategies for Analysis Services 2008:

• First he described how AS answers queries, and used the following chart:
  
  And it goes like the following:

1. When you run a query it goes in to the Formula Engine (FE).
2. The FE looks to the query and says what data needed to answer this query, so it slice it down to a number of requests then send them to the Storage Engine (SE).
3. All what SE does is receiving requests for data at a single granularity and it gets that data and it returns it back to the FE.
4. The SE looks to three possible was to answer a request: First of all it might have the data already in its cache (because it caches every data that already brought back). If it could not answer the request from its cache (which is the fastest way) it will look if this request matches exactly an aggregation, if not it will try to drive it from an aggregation. In the worst last way it will read the data from the raw partition of the data then aggregate it to the requested level of granularity.
   After that the request will be sent back to the FE, the FE then stitches all back together, it also does the required calculations (if there is). FE as well has a cache where it caches the result of the calculations. 
5. FE then put it all together in a cell set, and finally send it back to the user.

• SE caches data from measure groups as well as dimension data, and there is one cache per measure group and also one cache per dimension.
• FE caches only numeric values which are the results of an individual calculation… Strings can’t be cached.
• Basically when FE perform its calculations, there are two basic ways or types to do the calculations: If FE can look into your calculations before it evaluates them and know in advance what data is needed to do them, then before it does the calculations, it will bring all the needed data back all together and does the calculations, that is called the bulk mode, and it is the better way. The worse way is the cell-by-cell mode, an example of this mode happen when you have something like turning string to member names in your query.
• All SE caches have the same structure, known as the data cache registry, which hold sub-cubes and slices of data from the cube. The FE can also use that data cache structure (cache registry) if the cached data was calculated in bulk mode, if it was in that cell-by-cell mode the FE uses a different type of cache structure called Flat cache.
• SE cached data can be aggregated to answer FE requests, except when the measure data itself cannot be aggregated, for example with distinct count measures or many-to-many. And this is in fact one of the reasons why it a good idea to separate distinct count in its measure group, because as soon as you have a distinct count in a measure group the whole SE cache for that measure group will be non-aggregatable and the query performance will suffer.
• Sometimes more data is fetched into cache than is necessary for the query, this is called ‘prefetching’.
• Arbitrary shapes can only use the query cache if they are used in a subselect, in the WHERE clause, or in a calculated member. An arbitrary shape is any set that cannot be expressed as a crossjoin of members from the same level of an attribute hierarchy (means, not on a single level of granularity). For example, {(Food, USA), (Drink, Canada)} is an arbitrary set.
• To find what is going on with the cache things, run SQL Profiler and add the following events:
• Query Being/End: and these the events that fired when we start to run a query.
• Execute MDX Script Begin/End: events that fired when the MDX itself is evaluated, and this is for example when building all the calculated members and evaluating any name set..etc.
• Get Data From Aggregation: appears if an aggregation was used.
• Get Data From Cache: appears if data was fetched from cache.
• Query Cube Begin/End: fired when the query itself is evaluated.
• Query Subecube verbose: gives us a look to the actual granularity of the request made by the FE to SE.
• Progress Report Begin/End: when AS actually begins and ends getting data from the disk.
• There are three different ‘scopes’ or lifetimes of a FE cache:
• Query: for calculations designed in the WITH clause of a query, the FE values can only be cached for the lifetime of the query.
• Session: for calculations defined for a session, using the CREATE MEMBER statement executed on the client, FE values can only be cached for the lifetime of a session.
• Global: for calculations defined in the cube’s MDX Script, FE values can be cached until either any kind of cube processing takes place, a ClearCache XMLA command is executed, or Writeback is committed.
• If you want to get the best performance out of the FE cache, then you want it to cache values as long as possible, which means you should declare your calculation so they live as long as possible. In general, try to avoid declare your calculations in WITH clause of the query, instead put them in the cube, so in most cases Global scope is best from a performance point of view.
• Values stored in the SE cache can always be shared between all users.
• Values stored in the FE cache can be shared between users, except when:
• Stored in Query or Session scoped caches.
• Users belong to roles with different dimensions security permissions. Note that dynamic security always prevents cache sharing.
• Calculations evaluated in bulk mode cannot reference values stored in the FE flat cache. The same for calculations evaluated in cell-be-cell mode cannot reference values stored in the FE data cache registry.
• In certain circumstances SSAS uses query-scoped FE caches when you would expect it to use global scope, these are:
• Calculations that use the Username or LookupCube functions.
• Calculations use non-deterministic functions such as Now() or any SSAS stored procedures.
• Queries that use subselects, a client tool that always use subselect is SSRS, so try always to manually write the MDX query there.
• When any calculated member is defined in the WITH clause, whether it is referenced or not in the query.
• When cell security is used.
• We can warm the SE cache by using either:
• WITH CACHE: to warm the cache for a single query … not very useful.
• CREATE CACHE command.Remember that building aggregations is often a better alternative to warming the SE cache.
• For the FE cache, all what we can do is to run queries, there is no equivalent CREATE CACHE command. You can easily collect the queries users are running by using Profiler trace, save the trace to SQL Server or a .trc file, then rerun them, but you have to filter out those queries with a WITH clause, and watch out for parameterized queries, you have to replace the parameters with real values before rerunning them. Also watch out for queries that slice by Time, where the actual slicer used may change regularly.
• SSAS caching can use a lot of memory. The cache will keep growing until SSAS thinks it is running out of memory. When memory usage exceeds the percentage of available system memory specified in the LowMemoryLimit property, data will be dropped from cache. When it exceeds the percentage specified in the TotalMemoryLimit property, all data will be dropped from cache. We therefore don’t want to exceed the LowMemoryLimit, want to avoid paging, and we need to leave space for caching real user queries.
• The FE flat cache is limited to 10% of the TotalMemoryLimit, if it grows bigger than that it will completely be emptied.
• We can automate cache warming in different ways:
• Running SSRS reports on a data-driven subscription.
• Using the ascmd.exe utility.
• Building you own SSIS package (The best solution for overall flexibility) that either fetches queries from a SQL Server table, or from a Profiler .trc file using the Konesans Trace File Source component.

Summary for "Design Effective Aggregations in Analysis Services 2008" by Chris Webb

The following is a summary for the very good session held by Chris Webb for SQLBits and titled Designing Effective Aggregations in Analysis Services 2008:

• Aggregations are the single most important feature in Analysis Services regarding query performance, yet it’s not always the answer to our performance problems.
• Aggregations are only useful when the Storage Engine has to fetch data from disk.
• Aggregations will not be used if the data is in the Storage Engine cache.
• Aggregations may not be useful if the cause of query performance problems lies in the Formula Engine.
• How building aggregation speed Storage Engine response to the Formula Engine thus improves performance?  When AS has to read a lot of data from disk and has to aggregate that data up from the level which stored on disk, up to the granularity that been requested by the Formula Engine. That’s where a lot aggregation at run time takes place, so if we can get the granularity requested directly from the aggregation, instead of reading it from the kind of fact table granularity and aggregate it, then yes, that part of the process is going to be fast.
• Before building aggregations, design your cube carefully, especially attributes relationships in dimensions. Also try to build natural user hierarchy.  
• How do we know if the Storage Engine is the problem? How do we know if we are going to benefit from building aggregations? How do we know if we even are using the aggregation we’ve built?
  You can use SQL Profiler to see what is happening internally when a query is running. The following events are useful:
• Query Begin/End: the start and end of the query.
• Progress Report Begin/ End: for all reads from partitions or aggregations.
• Get Data From Aggregation: appears every time an aggregation is used, and it is the most important to look for in this exercise, because if you see this event, then you query is using aggregation. 
• Query Subcube Verbose: show details of the requests made to the Storage Engine by the Formula Engine. For example in the following screenshot of the lower area of the trace, you can see one dimension name, and the list of all the attributes in that dimension, and this describes the granularity of request. If the attribute has zero next to it, then we are not requesting data at that granularity else we are. 
  
  
  So in this example we are requesting data at the month granularity, as it has asterisk next to it. And how this is useful? Look at the granularity of the subcube request and compare them with the granularities in the aggregation tab.
• Aggregation Design Wizard may not build the aggregation that you really want, because in this tool it goes and analysis your cube structure and guess what aggregation can help, it doesn’t know what queries you are running. So after running the Aggregation Design Wizard setup Usage Base Optimization.
• The last step you can do is to build manually. 

Summary for "Common SSAS Design Mistakes" By Chris Webb

The following is a summary for the very good session held by Chris Webb for SQLBits and titled Common Analysis Services design mistakes and how to avoid them:

• Don’t ignore the blue squiggly lines in BIDS, they sometimes make useful recommendations about what you should better do.
• Try your best to avoid ETL and Named Queries in your Data Source View, because it can really slow down processing.
• Use friendly names for your dimensions, attributes and hierarchy.
• When you design your dimensions get rid of the surrogate key attribute or merge it with another attribute. Try to review all the attributes and see if they deserve to live in the cube or not. Removing unnecessary attribute will improve your dimension processing, also it is easier to come up with an effective aggregation design.
• Set AttributeHierarchyEnabled to false for attributes which are simply a kind of ‘property’ (for example email address or customer mobile number), attribute we never want to be able to drive queries by (like we never want to see sales by phone number!). Doing this will still allow to show those attributes as properties for other attributes.
• Even Parent/Child hierarchies are very user friendly and flexible, there are still some drawbacks for them:
• They can lead to slow query performance.
• No aggregations can be built at levels inside the hierarchy.
• They can also be a nightmare for scoping advanced MDX calculations.
• And dimension security on parent/child hierarchy is too complex.
• If there is any alternative to using a parent/child hierarchy then use it. And if you can know or assume what will be the maximum depth of your parent/child hierarchy then try to convert it to ‘Ragged’ hierarchy with using the HideMemberIf property. BIDS Helper can do this automatically for you.
• “One cube with multiple measure groups” or “multiple cubes with one measure group” each has its own pros and cons: in one cube, it could be complicated for the users (but you can overcome usability issues using Perspectives), hard to maintain and develop, and query performance may suffer if there are few common dimensions between measure groups and many calculations. In multiple cubes, it’s hard to analyse data from many cubes. Generally speaking, one cube is better but you have to analysis the situation.
• Try to the calculations in your ETL and DWH rather than in your cube because it’s always will perform better.
• Aggregations are the most important SSAS feature for performance, but you have to make sure they are being used, and to do so, run a Profiler trace and look at “Get Data From Aggregation” event. If you see values for that event in your Profiler trace then aggregation is being used. To fix that your aggregation is not being used, go to the aggregations tab, to the advance view, there you can see the granularity of the aggregation. If no checks beside the attribute you want to be included in the aggregation, redesign your aggregation using Aggregation Design Wizard and choose deferent Aggregation Usage for that attribute, keep in mind that:
• Full: means that every aggregation design must include this attribute.
• None: means that this attribute will never be included in aggregation.
• Unrestricted: leaves it to the Wizard to decide whether to use it or not.
• Default: means the same as Unrestricted if the attribute is the key attribute or the dimension or it’s involved in natural user hierarchy.

Performance Tuning For SSAS 2008 - Summary For Chris Webb Session "Introduction to Performance Tuning Analysis Services 2008"

The following is a summary for the very good session held by Chris Webb for SQLBits and titled Introduction to Performance Tuning Analysis Services 2008:

• In general, when you design attributes relationships the kind of more of these long thin strings of attribute relationships you can find in your data the better your dimension will perform; so if you have a kind of flat pushy structure then in general the performance will be slightly worse, if you have long finger like structure is going up throw your data and performance will be better.
• You have to check if your data reflects your dimension relationship design. You can right click your dimension and choose to “Dimension Health Check” after installing the very good tool BIDS Helper.
• For any child in the attribute relationship, remember to change the key to a composite key of the attribute itself and its ancestors, otherwise you may have a many to many relationship between the parent and the child attribute.
• Parent/child hierarchies can perform badly sometimes; so if you know the maximum depth of the hierarchy try to flatten out structure and turn it to a regular dimension with a regular set of relationships, rather than having one column with a kind of recursive join on it. The “Parent/child hierarchy naturaliser” in BIDS Helper can do this automatically.
• There are two ways (other than improving hardware) to tune the Storage Engine: Partitioning and more importantly designing Aggregation.
• Partition your measure group, after that AS engine will be able to read from multiple partition simultaneously, and it will be able to aggregate up data from multiple partitions simultaneously.
• Also the benefit of partitioning is that if you sliced you measure groups into partitions which reflect the way that you’re using the query in the cube then you will effectively reduce the amount of IO because the AS engine will just go to the partition that it knows contains the data.
• Although SSAS should auto-detect the slice of a partition, it is good practice to set the Slice property manually (The Slice property is MDX expression that tells AS what data in that partition), even on MOLAP partitions, otherwise you may find AS hits partitions that you know they should not by hit for certain queries.
• Aggregations are pre-calculated sets of summary values, similar to what is returned by a GROUP BY query in tSQL.
• Building aggregations improves query performance by reducing the amount of calculation done at query time.
• Aggregations start to show an effect on regular SUM measures where partition size is greater than a few million rows.
• Designing aggregations is something that you can do when your cube design is stable.
• Use the Aggregation Design Wizard to design your aggregations and associate the aggregation design with a particular partition, but before all of that you have to set the AggregationUsage property of the attributes (in the cube editor), and what this done is that it controls how the Aggregation Design Wizard uses those attributes for designing aggregates, and there are a number of options for that property:
• Full: means that every aggregation design must include this attribute.
• None: means that this attribute will never be included in aggregation.
• Unrestricted: leaves it to the Wizard to decide whether to use it or not.
• Default: means the same as Unrestricted if the attribute is the key attribute or the             dimension or it’s involved in natural user hierarchy.
• You also can design aggregation by setting up Query Logging and run Usage-Based Optimization.
• A third option to design aggregation is manually using BIDS Helper or the advance tab aggregation Design.
• To know whether your query is Storage Engine bound or Formula Engine bound you can use Profiler or MDX Studio and find how much time it takes to read data from disk, so look at the duration associated with query sub-cube event or look at the duration associated with progress report begin and end events, and if there is a significant amount of time associated with these event, say half a second, then yes aggregations is going to be useful, otherwise the problem is in the Formula Engine.
• Other sign that may point to the Formula Engine as the problem, is when you have a slow performing query that uses one core processor; as the Formula Engine is Single-threaded while the Storage Engine is Multi-threaded.
• MDX Script Performance Analyser can help to work out which, if any, calculation on the cube is the problem.
• Caching can take place in the Storage Engine and the Formula Engine. If Storage Engine read data from disk it will store it in the cache and it will be available there until you clear the cache or you process the cube, and caches there can be shared between all users.
• There are three different ‘scopes’ or lifetimes of a Formula Engine cache:
• Query: for calculations defined in the WITH clause of query, the Formula Engine values can only be cached for the life time of the query.
• Session: for calculations defined for a session, using the CREATE MEMBER statement executed on the client, Formula Engine values can only be cached for the lifetime of a session.
• Global: for calculations defined in the cube’s MDX script, Formula Engine values can be cached usually until processing takes place. Which is a good argument to define all of your calculation in the cube rather than in your query because then after the first time you run a calculation _in general_ that calculation value will be cached on the server for next time the query runs.
• Data can be loaded into the Storage Engine cache by executing CREATE CACHE statements. SSIS packages can be used to do this.
• The Formula Engine can only be warmed by running MDX queries.
• You can also use SSRS to warm the cache.

"A duplicate attribute key has been found when processing: Table..." Error Causes & Fixes

"Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_TheTable', Column: 'TheColumn', Value: 'XYZ'. The attribute is 'The Attribute'."

There is a number of causes and ways to fix the above error:

• Could be a result of having both blanks and NULLs in the source table/view. SSAS does
  
   SELECT DISTINCT  COALESCE(attr,'') FROM SOURCE 
  which converts NULLs to blanks, resulting in duplicate value blanks in the resulting feed - hence the error.
  Solutions : Remove all nulls from the data source by either filtering out rows containing nulls, or update null values with another value before processing the cube. Another solution is to change the way SSAS process nulls, and to do so: go to the cube's Dimension Usage tab, open to edit the relation between the dimension contains the attribute with the error and the measure group, click Advance button, select the attribute in the Measure Group Bindings window, choose the appropriate "Null Processing" option, finally reprocess the cube. The following explains the options for Null Processing:
  
  • ZeroOrBlank: This tells the server to convert the NULL value to a zero (for numeric data items) or a blank string (for string data items).
  • Preserve: This tells the server to preserve the NULL value. The server has the ability to store NULL just like any other value.
  • Error: This tells the server that a NULL value is illegal in this data item. The server will generate a data integrity error and discard the record.
  • UnknownMember: This tells the server to interpret the NULL value as the unknown member. The server will also generate a data integrity error. This option is applicable only for attribute key columns.
  • Default: This is a conditional default. It implies ZeroOrBlank for dimensions and cubes, and UnknownMember for mining structures and models.
• Actually, when I was searching for the descriptions of the Null processing, I found a nice two posts by Hilmar Buchta where he list even more than the reasons I wanted to write about:
• Reason 1 to 3.
• Reason 4 to 6.