SSAS Musings

new SSAS blog

2011-01-11T23:54:00.000-08:00

Jeffrey Wang has started blogging - and the first entry looks very promising.

Impersonation

2010-12-31T13:17:00.000-08:00

Vincent Rainardi wrote an interesting Q&A article on impersonation.

Implicit Recursion

2010-11-19T00:20:00.000-08:00

Darren Gosbell advises not to use Implicit Recursion in MDX.

Currency Conversion

2010-10-20T11:21:00.000-07:00

Boyan Penev writes in his Blog about Currency Conversion in SSAS. He provides a lot of interesting links and includes also practical examples. His Blog is absolutly worth reading (and is now included into the link section of this page).

Cube documentation with DMV data

2010-09-29T02:22:00.000-07:00

The FrogBlog shows how to generate a SSRS documentation for a cube by using DMVs via LinkedServer access:

Linked Cubes

2010-09-08T02:41:00.000-07:00

Again only a link ...
Chris Webb wrote in his Blog about the question:

whether, if you have multiple fact tables, you should create one big cube with multiple measure groups or multiple cubes each with a single measure group.

Wenn he says that "Linked measure groups are a pain to manage" I have to agree...

Dynamic Sets with SQL Server 2008 R2 and Excel 2010

2010-06-18T03:55:00.000-07:00

Thomas Ivarsson shows in his Blog that the use of dynamic sets is much improved for the combination of SQL Server 2008 R2 and Excel 2010.

Partitioning

2010-06-02T00:53:00.000-07:00

Teo Lachev writes in his Blog about the rules for the definition of partitions. For him partitioning is mainly a feature to improve the manageability of cubes and not to improve query performance - and I guess he's right.

Process Update

2010-05-14T03:01:00.000-07:00

In his Blog Chris Webb has published a short explanation of the consequences of ProcessUpdate on a dimension for the aggregations on related partitions. To make it even shorter: It seems to be a good idea to do a ProcessIndex on those partitions after the ProcessUpdate for the dimension.

I/O Performance

2010-04-14T06:46:00.000-07:00

Theo Lachev explains in his Blog why the SQL Profiler does not tell you the truth about I/O waits in SSAS: it's "because the Started/Finished reading data from the partition events include also the time spent in aggregating data and this time may be significant". He suggests to use the Xperf tool from the Windows Performance Toolkit instead and provides links to some short introductions that show how to use the tool.

Apart from this he also suggests to use solid state disks (SSD) when the storage engines spends a lot of time reading data (but "There needs no Ghost, my lord, come from the grave/To tell us this.")

Save your day with ProcessIncremental

2010-03-18T08:51:00.000-07:00

Who claims not to have too long processing times in real-world analytic databases either is a lyer ...

... or he has the SSAS Enterprise Edition installed with the great "ProcessIncremental" processing mode. In principal, this is a clever usage of the "Partition Merge" option in that it allows to feed a tiny data set that is disjunct from the last processing slice into a temporary partition which is then merged with an existing partition that is to be extended.

In our current project, where we tried to setup a "ProcessUpdate" szenario, the complexity of dimension, partition changes and ProcessIndex calls had summed up to a period that was nearly as long as a full "Process" (~2h for 5GB/2 years of business data).

Now it seems that we will come down to around 10-20min which should give us a nice slap to our backs unless I did oversee something stupid, here.

Data Quality

2010-03-16T00:41:00.000-07:00

Hilmar Buchta mentions in his Blog an new codeplex project with a (NUnit-based) testing suite to monitor the data quality in BI solutions. Seems to be quite useful.

Setting Locale-Specific Naming Size Information

2010-03-08T05:17:00.000-08:00

In SSAS 2008, there is the possibility to translate a key-only attribute just on a locale-specific basis: The name in the default attribute properties is left empty and the individual name columns are associated on a per-attribute/locale basis in the translation tab.

When the size of those columns changes, there is now the need to adapt it also in the name mapping. The place to to this is not quite obvious since the name property of the attribute is now empty: When selecting the attribute in the translation tab, go to the properties view and unfold the "CaptionColumn" property. Here you will find the important mapping information that you need to manipulate.

Various (Non-)Ways for Hiding Trivial Members in Hierarchies

2010-03-08T03:33:00.000-08:00

This is a link-collection that Martin has been doing when investigating, how and why the auto-hide-member of trivial members (they show a single child with the same semantics and are mostly artificially introduced to render unbalanced parent-child tress into balanced md-trees) does not work with Excel 2007 and SSAS 2008.

The Microsoft Documentation for the HideMemberIf-Property mentions the MDX Compatibility-Level of connections.

Chris Webb and Mark Russo describe how Excel 2007 overwrites the property value no matter what you specified.

There was a time when you could set the level in the registry back with SSAS 2000.
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/aeca1ce0-e6aa-4fdc-b604-cc0e27b7ab5a/
--> in 2005 + 2008 ist der Registry-Eintrag nicht vorgesehen

Greg Galloway hacks Excel to be content overwriting only a first fake property:
Extended Properties="MDX Compatibility=2";MDX Compatibility=2;

However, in our experiments, this rendered the whole subbranch invisible.

Oh no, we do not sacrifice so much for that functionality.

Collation

2010-02-19T10:24:00.000-08:00

Jamie Thomson discusses in his Blog a problem with different collations in SQL Server and SSAS that results in the following error:

Errors in the OLAP storage engine: The attribute key cannot be found when processing.

The problem is closely related to the problems we described here and here.

Demo Data

2010-02-11T07:04:00.000-08:00

Ella Maschiach provides a list of free Datasets, Data Warehouse and OLAP Databases in her Blog.

The difference between Order By Name and Order By Key in trivial attributes

2010-01-29T00:43:00.000-08:00

This is a straigthforward follow-up to the last post.

Suppose you have a trivial attribute, i.e., its key is mapped to a single column of a relational data source. Its name and value has been ommitted, i.e., corresponds to a representation of the key.

Now Order By Name (which should be the default) will lead to a lexicographic order based on the representation while only Order By Key will use the natural ordering of the underlying data type.

I´m sure you or the various books of your choices did know this already. But I didn´t.

The difference between Names and descriptive Attributes

2010-01-26T01:26:00.000-08:00

Depending on the configuration of your SSAS landscape (DB/OS/Locale of the data source, OS/Server Locale and Column Collation of the SSAS machine/cluster), you can get very strange processing errors in your dimensions especially related to character-based attributes/names.

For example, the default collation settings in SSAS (ignore whitespaces, case-insensitivity) will usually unify more (European/German-rooted) strings than a "distinct" selection in the database of your choice. NULL entries which SSAS cannot stand at all in dimension processing are also a constant cause of problems in that respect ...

If you now wonder, why this leads to processing errors in certain attributes (duplicate key error) and not in others, please remind the setup of each attribute into a key and a name column (the latter pointing to the former in the standard-case).

One of the fundamental differences between the two is that a "select distinct" of the key columns MUST NOT have two rows which will be unified by SSAS collation processing while this is not necessarily true for name columns in which case an arbitrary value is taken as the representative.

Quasi-Cursor-Reading of Large Cellsets

2010-01-26T01:14:00.000-08:00

One annoyance especially when batch-testing Cubes/Dimensions against the relational data source is that the MDX/XMLA-Architecture has no concept of continuation or open-cursor-reading.

And we all know how soon SSMS or MDX Studio present an OutOfMemoryException!

One particularly useful construct in that regard is the

SubSet(Set,Position,Length)

MDX-function which can be easily used to technically partition large result spaces into manageable junks.

Grouping in SQL and MDX

2010-01-25T06:35:00.000-08:00

In his Blog MDX equivalent of a filtered GROUP BY in SQL Darren Gosbell shows how to translate a SQL query (with a restriction in the WHERE-clause and a GROUP BY aggregating elements of a higher hierarchy level) into a corresponding MDX query. Although MDX looks like SQL it's something completly different, so every translation is beneficial.

Strange Perspectives

2010-01-22T00:45:00.000-08:00

In SSAS Persepctives are a very useful tool to control the visibility of objects (dimesions, measures, hierarchies etc.). They represent subsets of cubes and define Views with the relevant data for a specific audience. In the Perspectives tab of the cube designer in BIDS I can exclude objects, that I want to hide from my users. But obviously sometimes SSAS does not believe that I know what I do:

In the following example I defined a simple cube with only one measure group and some dimensions. In addition I created a calculation TestQuantity. In the Perspectives tab I choosed to include all the measures and the additional calculation:

In the browser we see the expected elements:

Now I exclude some of my measures from the Perspective:

And again I get the expected selection:

But when I exclude all the basic measures and want to include only my calculation, SSAS apparently starts to question my accountability:

The result again contains all the measures of the group, I wanted to exclude:

Perhaps SSAS believes that a cube (or a cube view) without measures is not very beneficial, but since the calculation TestQuantity was not excluded, I see no reason for this implicit addition of objects.

For the sake of completeness a link to William Pearson's article on Perspectives in the Database Journal and another one to the (books online) documentation.

Official release date for SQL Server 2008 R2

2010-01-20T04:31:00.000-08:00

According to the Data Platform Insider Blog ''SQL Server 2008 R2'' received an official release date and will be available by May 2010.

Dynamic Management Views (DMV) in SSAS

2010-01-10T02:04:00.000-08:00

Vincent Rainardi wrote in his Blog SSAS DMV (Dynamic Management View) on a topic we tried already to cover here some weeks ago. The article provides some interesting links to Blogs that explain who to join the results of different DMVs together.

Debugging Attribute Relationships in the ROLAP Basis

2010-01-06T04:32:00.000-08:00

I guess that this is quite obvious and could as well be published in our ROLAP blog, but just for the sake of completeness:

The following SQL-Statement lists all values of a source attribute

src

in your dimension source table/view

dim

which violate an attribute relationship (path) to a destination attribute

dest

select src from dim group by src having count(distinct dest)>1

Extending this a bit delivers all the bad rows:

select * from dim where src in (select src from dim group by src having count(distinct dest)>1)

Excel and MDX multiselect problems revisited

2010-01-05T23:57:00.000-08:00

Multiselect problems ins SSAS 2005 ff come in several forms. Today, I want to take a look at several forms of multiselect problems, how they interact with Excel as a MDX client and how they can be avoided. Let's first look a problem that can be easily detected.

The current coordinate is a set

In his blog article Multiselect friendly MDX for calculations looking at current coordinate
Mosha Pasumansky explains how multiselect problems that use a set in the current coordinate occur and how they can be avoided. Let's show an example for the classical Adventure Works Cube. I use SSAS 2008 and Excel 2007. Let's define the sales amount for the year before as follows:

create member [Measures].[Prior Year Sales Amount] as ([Measures].[Sales Amount],ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember));

Let's look at a simple example in Excel 2008:

So far, it works fine, now let's filter on the dates, first on calendar year 2002.

It still works fine. Now let's filter on two months, say May and June 2002. Here's the result.
It doesn't work anymore because now, the current coordinate is a set. Here's the query:

SELECT {[Measures].[Sales Amount],[Measures].[Prior Year Sales Amount]} ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})}) ON ROWS FROM (SELECT ({[Date].[Calendar].[Month].&[2002]&[5],[Date].[Calendar].[Month].&[2002]&[6]}) ON COLUMNS FROM [Adventure Works])

The set occurs in the subselect that Excel 2007 uses to implement the filter. If we do the same in Excel 2003, we get a similar result. it doesn't work but we get a least an error.

Now, the query uses a where clause and a calculated member to represent the set. Here's the query:

WITH MEMBER [Date].[Calendar].[XL_QZX] AS 'Aggregate({[Date].[Calendar].[Month].&[2002]&[6],[Date].[Calendar].[Month].&[2002]&[5]})' SELECT {[Measures].[Sales Amount],[Measures].[Prior Year Sales Amount]} ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Product].[Product Categories].[All Products]})})) ON ROWS FROM [Adventure Works] WHERE ([Date].[Calendar].[XL_QZX])

The error basically states the the CurrentMember function, which is used inside the ParallelPeriods function of our calculated measure [Prior Year Sales Amount] now contains a set.
A simple try of the same in Excel 2010 beta also shows that is currently does not work (even if you use the new SSAS 2008 R2 CTP).

Let's summarize: Calculations that use CurrentMember (or functions that use CurrentMember) are not multiselect-safe in Excel 2003, 2007 and ,at least up to now, in Excel 2010.

A possible workaround is described in Korrekte MDX-Berechnungen mit den Analysis Services trotz multiselect. The idea is to add a named calcuation in the data source view and to scope this calculation later on. Alternatively, you can just live with it, you just get no result or an error.

subselects and the current coordinate´problem

While the first problem can be easily detected Excel(you get no results or an error), the following problem is more subtle and cannot easily be detected. It occurs in Excel version 2007 (at up to now in 2010 beta). Let us again show an example in the adventure works cube. We define

create member [Measures].[Number of Products]

Count(Existing([Product].[Product].[Product].MEMBERS));

Here is a simple excel 2007 example:

Let's now filter on the products dimension.We restrict to Accessories and Bikes and also leave out the touring bikes. Here's the result:

Well nice try, but unfortunately a wrong result. The visual totals are wrong, the sum of bikes should be 103 and the total 138. Let's look at the query:

SELECT NON EMPTY
Hierarchize(DrilldownMember({{DrilldownLevel({[Product].[Product Categories].[All Products]})}}, {[Product].[Product Categories].[Category].&[1]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Product].[Product Categories].[Subcategory].[Category] ON COLUMNS
FROM
(SELECT ({[Product].[Product Categories].[Subcategory].&[2], [Product].[Product Categories].[Subcategory].&[1],
[Product].[Product Categories].[Category].&[4]}) ON COLUMNS
FROM [Adventure Works]) WHERE ([Measures].[Number of Products]
)

The filter step in Excel 2007 is implemented by a subselect. Unfortunately, the subselect does not change the current coordinate and thus the query execution context is the [All] member of the product dimension. And indead, the totals and subtotals are those for an unfiltered query.

A solution is to rely on a feature of SSAS2008, dynamic sets. The following solves the problem for SSAS 2008 and Excel 2007 and 2010.

create dynamic set Products as [Product].[Product].[Product]; create member[Measures].[Number of Products]
as count(existing(Products));
Here's the result:

Although Excel 2003 doesn't have subselect problems (it does not generate queries that use subselect), this solution does not work for Excel 2003 (the generated query does not benefit from the dynamic set, i.e., the results are still wrong.

Additionally, the problem still exists if we use different measures like Adventure Works
[Measures].[Ratio to Parent Product] because here, dynamic sets cannot help to solve the problem. Let's show this in the next example:

Now let's filter again:

Wow, amazing, all ratio to parent values are wrong, column real ratio gives you the correct values. Again, this is the effect of the subselect, that implements the filter. The current coordinate does not change, thus you see values of the form sum of filtered bikes / unfiltered parent of bikes, i.e. the 73,15% in the bikes row is equal to $80.324,94 / $109.809.274,20 (unfiltered total). The real ratios are in column "real ratio".

This also does not work in Excel 2010, but Excel 2003 computes correct values:

A solution to solve this problem in Excel 2007 and 2010 uses the same idea above, adding a hard measure (named calculation) together with scoping.

Let's summarize our results:

"The current coordinate is a set"-problem occurs in Excel versions 2003, 2007 and 2010 beta. You can either live with it (your get no result or an error) or you have to define a hard measure and scope it.

The "subselect and current coordinate"-problem occurs only in Excel versions 2007 and 2010 beta as these Excel versions rely on subselect to implement filters. Sometimes this problem can be circumvented by the use of dynamic sets (if your calculated measures relies on dynamically counting a set). In other cases, you have to define a hard measure and scope it.

Changing results with IIF hints

2009-12-21T04:11:00.000-08:00

Recently we saw some strange effects with calculations using a conditional IIF-logic: the calculation should show the difference between two measures if the first measure is greater than 0:

IIF([Measures].[FirstMeasure]=0,NULL,
        ([Measures].[FirstMeasure]-[Measures].[SecondMeasure])

A query using the calculation returned the expected results for the [All]-node of a given hierarchy but wrong results for other levels. Since the given measures are involved in some scoped assignments we assumed an error in our logic, and were quite suprised when we discovered that a CASE-block returned the correct results:

CASE [Measures].[FirstMeasure] 
         WHEN 0 THEN NULL
         ELSE [Measures].[FirstMeasure]-[Measures].[SecondMeasure]
    END

Further research showed that IIF also brought the correct results when we used the eager hint in the calculation. The hint is a new feature in SSAS 2008 and documented here. The SQLCAT team explains the hint in the blog IIF Function Query Hints in SQL Server Analysis Services 2008:

the IIF mdx function is a commonly used expression that can be very costly to evaluate. It takes 3 arguments:

iif(< condition >, < then branch >, < else branch >)

Where the condition evaluates to true, the value from the “then branch” is used otherwise the “else branch” expression is used.

Note the term “used” – one or both branches may be evaluated even if its value is not used. It may be cheaper for the engine to evaluate the expression over the entire space and use it when needed (termed an eager plan) rather than chop up the space into a potentially enormous number of fragments and evaluate only where needed (a strict plan).

Apparently the different execution pathes can bring different results - and that is something I would not expect...

Subtotals for Named Sets

2009-12-18T07:01:00.000-08:00

Although still in its infancy, named sets could really help you to satisfy your numerous customers ad-hoc aggregation requirements ...

... if they only would show sub-totals in excel and if you only could mount non-disjunct instances of those beasts one after the other.

Here is a tip that makes use of the implicit property of named sets to represent intersections when confronted with members of different levels from a common hierarchy.

In its extreme application, adding the [All] member to a set will deliver you a sub-total that is at the same time correctly named "Gesamtergebnis" or "Total" when displayed in excel.

Do not call a dimension attribute "DESCRIPTION"!

2009-12-18T06:27:00.000-08:00

At least the Office Web Components (OWC) browser integrated into BI Development Studio and Management Studio will not show the values as a member property when the attribute (Name, not key nor translation) is called "DESCRIPTION".

Federating a Secondary SQL Server Data Source into a Primary-Oracle Data View

2009-12-17T08:42:00.000-08:00

It is a common, though tricky, thing to host some, but not all of your analytical data that is to be fed into the cubeware of your trust in the nearby, type-consistent and easy-to-maintain SQL Server instance.

This methodology has also the nice side-effect that it will not give the .. say Oracle-minded ... datawarehouse people much to reason about such they will not get distracted from their ... um ... daily slee.., ah, work was the political correct term.

The idea will however bite you the first time you notice that you cannot mix dimensions and facts between two seperate Oracle/SQL Server data views. So you will read diverse blog entries trying to mix the two data sources within a single view.

Oracle will be your primary data source. "Add table" in the view allows you weel to choose a registered SQL Server as a secondary data source, but the table chooser stays empty!

If you read above-mentioned thread to its end, there will be the definitive hint, which is "Add Named Query" and .... here you are ... nearly.

Be aware that this will operate on some "Remote view" feature of the Oracle ODBC driver. For example, Oracle tends to lower-case only the key columns, which can fool you when switching an existing dimension from a seperate data view to the now merged view.

I would not recommend trying it the other way round (Oracle secondary, SQL Server primary data source) unless your primary data source will become bigger than the secondary one ... ok, we still hope that ... since it is to expect that using this kind of federation for practical volumes of fact data will not be funny.

How to translate the [All] member

2009-12-09T04:08:00.000-08:00

I was always hesitating on that issue because I did not want to affect a thousand lines of MDX-Code in which [Dimension].[Hierarchy].[All] is a common construct, e.g., to exclude unwanted detail-calculations from a calculation scope.

But per default, there is no translation row in the dimension editor that allows one to override the display name of that ubiquitous and ugly (though, comprehensible) node.

Here comes the (straightforward) step: Simly set the property "AttributeAllMemberName" of the relevant dimensions explicitely to the default value "All" and - voilà - you have the translation rows for your pleasure without having to reinterpret your scripts.

Please be aware that the member must be also (re-)translated for all the hierarchies (these rows will also automatically appear). Similarly, you can treat the "UnknownMemberName" ...

Non-Pivot-Friendly MDX through Excel 2007 [Updated]

2009-12-07T08:46:00.000-08:00

I am sooooo glad for Greg Galloway´s tiny little comment to this post.

Because we were looking for a solution to integrate MDX queries into Excel which are not very pivot-table friendly.

Why do you ever want to do something esoteric like that? Suppose you want to compare very specific data-points in your tuple-space with each other (a monthly real margin from one year in one store with a daily plan margin from a region in a completely different year). Then, your pivot table will necessarily cross-join all the individual coordinate components (year/month/day(s) of both years, affected region/store(s), plan & real measures) and come with a solution set that is orders of magnitude too large and cannot be reasonably filtered without going nuts.

That is where you should rather build up a small (remember: SMALL in the sense of aggregated fact data) pivot table, do a drill-though and then save the resulting query table as a nice template for your future experiments.

I have not been able to create that query table by any other means in Excel 2007!

Update:

There is another way for executing and displaying mdx described by Hilmar Buchta which might be more useful when you need full (VBA) control over the connection/command behaviour, e.g., when you first need to check whether the result is empty (and hence would destroy the layouted table structure).

Monitoring current SSAS acitivities

2009-12-04T05:25:00.000-08:00

A question DBA's are always interested in is: who is connected to my database and what are they doing? For SSAS 2008 there are some Dynamic Management Views (DMV) to answer this question(s) (and many others). The query:

SELECT TABLE_NAME
  FROM $system.dbschema_tables
 WHERE TABLE_SCHEMA = '$SYSTEM'
   AND LEFT(TABLE_NAME,8) = 'DISCOVER'
 ORDER BY table_name

provides the following list of tables (I added links to and descriptions from the documentation; some of the linked pages are not very extensive):

- DISCOVER_COMMAND_OBJECTS: Provides resource usage and activity information about the objects in use by the referenced command.
- DISCOVER_COMMANDS: Provides resource usage and activity information about the currently executing or last executed commands in the opened connections on the server.
- DISCOVER_CONNECTIONS: Provides resource usage and activity information about the currently opened connections on the server.
- DISCOVER_DB_CONNECTIONS: Provides resource usage and activity information about the currently opened connections from the server to a database.
- DISCOVER_DIMENSION_STAT: This schema rowset returns statistics on the specified dimension.
- DISCOVER_ENUMERATORS: Returns a list of names, data types, and enumeration values of enumerators supported by the Microsoft XML for Analysis (XMLA) provider for a specific data source.
- DISCOVER_INSTANCES: Describes the instances on the server.
- DISCOVER_JOBS: Provides information about the active jobs executing on the server.
- DISCOVER_KEYWORDS: Returns information about keywords reserved by the Microsoft XML for Analysis (XMLA) provider.
- DISCOVER_LITERALS: Returns information about literals, including data types and values, supported by the Microsoft XML for Analysis (XMLA) provider.
- DISCOVER_LOCKS: Provides information about the current standing locks on the server.
- DISCOVER_MASTER_KEY: This schema rowset returns the server's master encryption key (the key that is used by the server to encrypt sensitive data on disk).
- DISCOVER_MEMORYGRANT: This rowset returns a list of internal memory quota grants that are taken by jobs that are currently running on the server.
- DISCOVER_MEMORYUSAGE: This schema rowset returns the DISCOVER_MEMORYUSAGE statistics for various objects allocated by the server.
- DISCOVER_OBJECT_ACTIVITY: This schema rowset provides resource usage per object since the start of the service. Some further information on this view can be found in Vidas Matelis' Blog.
- DISCOVER_OBJECT_MEMORY_USAGE: Provides information about memory resources used by objects.
- DISCOVER_PARTITION_DIMENSION_STAT: This schema rowset returns statistics on the dimension associated with a partition.
- DISCOVER_PARTITION_STAT: This schema rowset returns statistics on aggregations in a particular partition.
- DISCOVER_PERFORMANCE_COUNTERS: This schema rowset returns the value of one or more specified performance counters.
- DISCOVER_PROPERTIES: Returns a list of information and values about the standard and provider-specific properties that are supported by the Microsoft XML for Analysis (XMLA) provider for the specified data source.
- DISCOVER_SCHEMA_ROWSETS: Returns the names, restrictions, description, and other information for all enumeration values and any additional provider-specific enumeration values supported by the Microsoft XML for Analysis (XMLA) provider.
- DISCOVER_SESSIONS: Provides resource usage and activity information about the currently opened sessions on the server.
- DISCOVER_TRACE_COLUMNS: This schema rowset contains the DISCOVER_TRACE_COLUMNS schema rowset.
- DISCOVER_TRACE_DEFINITION_PROVIDERINFO: This schema rowset returns basic information about the trace provider, such as its name and description.
- DISCOVER_TRACE_EVENT_CATEGORIES: This schema rowset contains the DISCOVER_TRACE_EVENT_CATEGORIES schema rowset.
- DISCOVER_TRACES: This schema rowset contains the DISCOVER_TRACES schema rowset.
- DISCOVER_TRANSACTIONS: This schema rowset returns the current set of pending transactions on the system.

Some of these views have to be queried with the function SYSTEMRESTRICTSCHEMA to query restricted schema rowsets - you find some examples for the use of this function in Vidas Matelis' Blog.

To answer the initial question - who is on my db? - you could use the (simple) query

SELECT *
  FROM $system.DISCOVER_SESSIONS

Another option to get these information is to use the small GUI-application Activity Viewer (download from codeplex).

Editions

2009-12-01T01:28:00.000-08:00

The msdn site Features Supported by the Editions of SQL Server 2008 shows the differences between the SQL Server editions. Regarding SSAS the Standard Edition (SE) misses the following features compared to the Enterprise Edition (EE):

Data Warehouse Scale and Performance
- Auto parallel partition processing
- Partitioned cubes
- Distributed partitioned cubes

Multi-Dimensional Analytics
- Financial aggregations
- Custom rollups
- Semi-additive measures
- Writeback dimensions
- Linked measures and dimensions
- Binary and compressed XML transport
- Account intelligence
- Perspectives
- Analysis Services shared, scalable databases

Data Mining (in this category SE is quite weak)
- Cross validation
- Models on filtered subsets of mining structure data
- Time series: custom blending between ARTXP and ARIMA models
- Time series: prediction with new data
- Unlimited concurrent data mining queries
- Advanced configuration and tuning for algorithms
- Algorithm plug-in API
- Parallel model processing
- Time series: cross-series prediction
- Unlimited attributes for association rules
- Sequence prediction
- Multiple prediction targets for naïve Bayes, neural network, and logistic regression

Of course you can build cubes without these features but they are very helpful in many situations. On the other side there's of course a difference in the licence fees... - here are some additional links to licence and pricing information:

- SQL Server 2008 Pricing
- SQL Server 2008 Licensing FAQ

duplicate key error

2009-11-30T05:02:00.000-08:00

Hilmar Buchta explains in his Blog A duplicate attribute key has been found when processing… some common reasons for duplicate key errors in processing error messages. The most likely reasons are:

NULL values in the attribute key column.
data that violates the attribute relationships defined in the dimension.
A third reason is that there are separate key and name source fields for an attribute, so there is more than one name corresponding to a key.
A related issue results from collation effects: distinct string values from an oracle source are considered as duplicates by SSAS because they only differ in capitalization (upper/lower case), trimming or some strange characters (as the german "ß" that will be replaced by "ss" or "ä", "ü", "ö" etc.).

MDX Query Performance in SSAS

2009-11-20T05:04:00.001-08:00

there are a few documents you should know if you want to optimize the performance of the MDX queries on your SSAS cubes:

The document SQL Server 2008: Analysis Services Performance Guide (download here) provides information on the tuning of server resources and shows strategies to enhance the performance of cube processing and query access.

The paper Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services (download here) discusses the troubleshooting tools integrated into Microsoft Windows and SQL Server and shows how to use these tools to identify and resolve MDX query performance bottlenecks with individual MDX queries. Most of the given advices are still true for SSAS 2008 (as far as I can see...)

Some interesting papers and articles can be found at the web site of the Microsoft SQL Server Development Customer Advisory Team.

Unfortunatly there are many advices which were of interest in SSAS 2005 but are no longer relevant in SSAS 2008 - so you have to be very careful when adopting the strategies shown in various blogs and newsgroups in the Net.

Centrally Managing Formatting Properties in MDX Scripts

2009-11-19T07:56:00.000-08:00

This is a quite current goody already sketched by Marc Russo:

Formatting is indeed in the twilight zone between server/analytic logic and client display issues. Originally you would want your cube to be agnostic of all that representational stuff which may change with the eye of the beholder.

On the other hand, it is unbearable to walk through the conditinal formatting result of ~100 affected measures * 30 affected reports once a formatting rule changes.

A good compromise between the "this-is-client-stuff" and the "incorporate-it-in-each-and-every-measure' is using the power of MDX scripting. The scoping approach that Marc Russo describes (and from which I learned the invaluable [Measure].CurrentMember trick) will not work, as soon as
you need to address targetted measures (such as "all currencies" or "all indexes") across calculated and real measures (an explicit measure set in a scope may only contain non-calculated measures).

So here is how it works in these cases (and similar for FORE_COLOR, BACK_COLOR, etc):

FORMAT_STRING({
 [Measures].[REAL_MEASURE_1]
 ,[Measures].[CALCULATED_MEASURE_2]
 ,...
 })=// YOUR MDX CALCULATION using [Measures].CurrentMember and returning a format string
 ;

When the Scoping gets tough ...

2009-11-19T01:51:00.000-08:00

... the tough gets scoped.

There is a hairy observation to make that I would usually classify under the catgory "bug".

In arrogant ignorance of Microsofts issue tracking systems and even more, in absence of enough terminological background to even state a reasonable issue search query, we currently file it under "phenomenon with a workaround".

Imagine that you have developed an elaborate date hierarchy with two "branching" hierarchies, one for calendar weeks and one for months, quarters, years andsoforth. Branching means that from the point of view of attribute relationships, there are two outgoing relation branches, one in the attributes of the week hierarchy and one into the attributes of the month hierarchy.

From our experiments, calculations specified "purely" for the month scope will nevertheless also hit the week level of the parallel hierarchy just like if SSAS would rather parse the statement as "operate on (all) successor members of date".

SCOPE([DIM_DATE].[HIER_YQM].[LVL_MONTH].Members);
 // Do the usual nasty overrides and calculations
END SCOPE;

To get the behaviour right, we are now used to rather write such temporal scopes as

SCOPE([DIM_DATE].[HIER_YQM].[LVL_MONTH].Members,
      [DIM_DATE].[HIER_WEEK].[LVL_WEEK].[All],
      [DIM_DATE].[HIER_WEEK].[LVL_WEEK_YEAR].[All]);
 // Do the usual nasty overrides and calculations
END SCOPE;

where it is interesting that leaving the second all restriction away would reinforce the overrides at the calender year level nevertheless.

Still the behaviour (under the many strange behaviours of SSAS) leaves us puzzled. But I´m sure that there is a tough explanation for this in some other universe.

BI Methodology

2009-11-18T07:14:00.000-08:00

Marco Russo and Alberto Ferrari have written an interesting paper presenting their Methodology for Microsoft BI (September 2008) which can be downloaded from here. In the paper they discuss their approach in the context of the methodologies of Bill Inmon and Ralf Kimball and draw a picture of the interaction of OLTP database, Data Warehouse and OLAP Cube.

MDX Studio

2009-11-18T01:45:00.000-08:00

As Mosha Pasumansky states MDX Studio is a

tool which helps users of Microsoft Analysis Services to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution.

It's indeed an extremly useful tool: it shows valuable information that is not included in profiler traces such as the number of cache hits, calculated cells, memory usage etc. Another important feature is the ability to show a hierarchical tree of execution steps (which Mosha describes here). Very helpful is also the ANALYZE option that (- it's not a surprise -) analyzes a given query or mdx script, shows possible issues, and provides links to wider explanations (mainly from Moshas Blog).

The current version of the tool (a work in progress) can be downloaded from here.

EXISTING keyword

2009-11-17T23:52:00.000-08:00

As the documentation states the EXISTING keyword forces a specified set to be evaluated within the current context instead of being evaluated within the context of the cube that contains the members of the set. Mosha Pasumasky declares "that Exists function and EXISTING operator are generalization of hierarchy related functions such as Descendants. Indeed, the process of building an hierarchy is nothing more then applying Exists between attributes which build individual levels of that hierarchy." So EXISTING shows only elements that are actually relevant in the given context.

If we are looking for the number of cities in each country in the Adventure Works Cube and use the following query:


WITH
  MEMBER [CITY_COUNT] AS COUNT([GEOGRAPHY].[CITY].[CITY].MEMBERS)
SELECT {[CITY_COUNT]} ON 0
       ,[GEOGRAPHY].[GEOGRAPHY].[COUNTRY].MEMBERS ON 1
  FROM [ADVENTURE WORKS]

we do not get the intended result:


                CITY_COUNT
Australia              587
Canada                 587
France                 587
Germany                587
United Kingdom         587
United States          587

That's the total count of cities for every country - apparently the evaluation took place in the context of the cube and not in the current context. With EXISTING we get what we want:


WITH
  MEMBER [CITY_COUNT] AS COUNT(EXISTING([GEOGRAPHY].[CITY].[CITY].MEMBERS))
SELECT {[CITY_COUNT]} ON 0
       ,[GEOGRAPHY].[GEOGRAPHY].[COUNTRY].MEMBERS ON 1
  FROM [ADVENTURE WORKS]

provides the result:


                CITY_COUNT
Australia               40
Canada                  50
France                  36
Germany                 51
United Kingdom          35
United States          375

As already mentioned here EXISTING can also be used to solve some problems with multiselects.

In his Blog Chris Webb wrote about the performance of EXISTING in combination with NONEMPTY and stated "if you are using NonEmpty and Existing together, do the NonEmpty first and then the Existing" - but admitted that he has no sound explanation for the observed effect.

Multiselect

2009-11-17T07:43:00.000-08:00

Multiselects are a source of constant discomfort in SSAS 2005 ff. When there are multiple members in the slice of a MDX query the currentmember function no longer defines a single element and that's the reason for many problems with calculated members. Some of these problems can be solved by the use of special syntax elements (most notably the EXISTING function) but client tools like Excel 2007 generate their own queries so the developer can not control the design (and Excel makes things even worse by using subselects which bring even more annoyance than simple where-clause multiselects).

At least there are some solutions to avoid the most unpleasent consequences - and most of these solutions come from Mosha Pasumansky:

In his blog articles Writing multiselect friendly MDX calculations and Multiselect friendly MDX for calculations looking at current coordinate Mosha Pasumansky shows a way to avoid the multiselect problems by using the function EXISTING and sum up the results. Another solution (using a stored procedure) is shown in How to detect subselect inside MDX calculations (aka Multiselect in Excel 2007) - but Mosha warns "that the solution is neither elegant nor efficient, and probably is mostly of the theoretical interest." In his article I need your help or how to make multiselect work seamlessly in MDX Mosha suggests a solution for all multiselect problems - but it seems he kept that secret...

The blog Korrekte MDX-Berechnungen mit den Analysis Services trotz Multiselect makes the suggestion to define "empty" measures (initially filled with NULL values) and then to overwrite them with scoped assignments on every relevant level - so correct values can be aggregated for higher levels.

All these solutions have an impact on performance, so Mosha writes: "Therefore, it is certainly a choice of cube designer - keep the simpler and more performance friendly expression, but accept that multiselect on the Time dimension will cause errors, or allow more functionality and enable multiselects on Time, but be prepared for slower performance in all cases."

Nested Scopes and Attribute Relationships

2009-11-17T03:54:00.000-08:00

Nested Scopes are a convenient syntax to structure subcube/crossjoin calculations in your MDX scripts. Most often, you would want to use more than one attribute from the same dimension in such a context and, in addition, navigate the associated hierarchies in the scoped calculations:

SCOPE({[DIMENSION].[HIERARCHY_1].[LEVEL_OR_ATTRIBUTE_1].&[KEY_1]},...);
   SCOPE({[DIMENSION].[HIERARCHY_2].[LEVEL_OR_ATTRIBUTE_2].&[KEY_2]},...);
        // Your calculation involving [DIMENSION].[HIERARCHY_1].CurrentMember
        // will result in navigating [DIMENSION].[ATTRIBUTE_1].[All]!
   END SCOPE;
END SCOPE;

However, with nested scopes it seems that similar overriding rules than with concatenating ordinary tuples apply: Suppose the attribute ATTRIBUTE_2 associated with the LEVEL_OR_ATTRIBUTE_2 level is related to the attribute ATTRIBUTE_1 (i.e., KEY_2 is unique for KEY_1, in contrast to the related from relationship where KEY_1 is unique for KEY_2), the example calculation context above would result to the All member of ATTRIBUTE_1. Duh.

Writing the nesting the other way round (which would seem semantically equivalent from only superficially reading the specs, as we usually do) the delivers the expected result

SCOPE({[DIMENSION].[HIERARCHY_2].[LEVEL_OR_ATTRIBUTE_2].&[KEY_2]},...);
    SCOPE({[DIMENSION].[HIERARCHY_1].[LEVEL_OR_ATTRIBUTE_1].&[KEY_1]},...);
        // Your calculation involving [DIMENSION].[HIERARCHY_1].CurrentMember
        // will finally navigate [DIMENSION].[ATTRIBUTE_1].&[KEY_1]!
    END SCOPE;
END SCOPE;