Richard Lees on BI

Analysis Services Configuration Options

2012-01-29T07:49:00.000+11:00

To see the SSAS configuration options (properties), right click on the service from SQL Server Management Studio and select properties. You will only see the Basic options, by default. To see all options, check the "Show Advanced (All) Properties".

Most of these options, you really shouldn't alter, but there are a few that you should know about.
Here are a few SSAS properties that I think you should be aware of, and might want to change.

DataDir
This is where SSAS will, by default, put the SSAS database files. By default, this will be a sub directory of the "program files", which is not an ideal location. The SSAS databases should be on high performance storage. IO is very often a critical factor in large SSAS cubes.

ExternalCommandTimeout
As I understand it, this property is the number of seconds that SSAS will wait for the first row to return from an SQL query during processing. The default is 3600 seconds (1 hour) which is insufficient for many environments.   I often change this to 36000 (10 hours) in larger environments, or environments with Oracle as an rdbms (just kidding).

ForceCommitTimeout
This is the amount of time (milliseconds) that SSAS will wait on queries while performing process updates. A process update cannot commit while a query is active. The default is 30 seconds, which I think is quite reasonable, but you might have a need to change this value.

QueryLogSampling
The default value is 10, so that 1 in 10 queries are recorded. In practice, I either need all queries or none. So what I do is change this value to 0 to minimise overhead.   When I am running aperformance tuning exercise, I will temporarily change this to 1.

LowMemoryLimit
If this number is between 1 and 100, it is the percentage of memory that SSAS can use, without concern. By default it is 65, which tends to be an appropriate value. You might wonder why your 100GB server never sees 65GB memory for SSAS, even though your cubes are much larger and the IO demands are huge. This is due to SSAS caching logic. Just because you have the memory and the cube is large doesn't mean that SSAS will hold the cube in memory. see my earlier blog http://richardlees.blogspot.com/2011/12/why-doesnt-ssas-cache-entire-cube.html

TotalMemoryLimit
You might see the LowMemoryLimit as a very soft limit, which can be exceeded when processing demands would benefit. The TotalMemoryLimit is a harder limit, but it can also be exceeded when necessary.   One way that I look at these two options is the LowMemoryLimit is the memory limit during non processing times, and the TotalMemoryLimit is the limit when SSAS processing jobs are active. However, as I mentioned above, the more common concern is that SSAS isn't using the available memory, rather than it is using too much.

DrillThroughMaxRows
I haven't ever changed this property. Since 2005, SSAS hasn't offered a real drill-through. This is really a drill down option. If you want to support drill through see my blog http://richardlees.blogspot.com/2009/01/using-drill-through-in-analysis.html

Port
If you want to change the port that SSAS listens on (or you are running multiple instances) this is where you specify the port number. By default this value is 0, which denotes 2383.

ServerTimeout
This is the limit for SSAS queries in seconds. After this time (default 3600 or 1 hour) mdx queries will timeout. You may have reason to increase or decrease this value.

Remember What's Important

2011-12-28T09:19:00.001+11:00

Happy New Year to everybody.

Perhaps, like me, you enjoy your work in the Microsoft software space. That's great, you are very lucky if you enjoy your work. But remember what's really important in this life. Your family and friends.

So, don't work too hard. Eat well and keep exercising. It helps if you find a sport that you really enjoy. At the moment, that is unicycling for me.

See you all in the New Year.

My SSAS databases are corrupted

2011-12-11T13:39:00.001+11:00

Did you happen to have a unplanned shutdown? This is quite likely to be the cause of the failure. This is unlike SQL Server, which has a transaction log and will always recover databases on restart.
As I have mentioned earlier, SQL Server Analysis Services exploits the Windows File Cache to help minimise IO, which might lead you to suspect the Windows cache. However SSAS uses the FlushFileBuffers API to minimise the possibility of having dirty pages in the Windows File Cache. However, this does not protect SSAS from corruption on an unplanned shutdown.
So, if you really want to avoid corrupted SSAS databases on power failure, you should ensure your SSAS servers are supported by UPS.
Most production SSAS servers have UPS. But, if you don't have UPS, like my demonstration site (http://RichardLees.com.au/Sites/Demonstrations) and, like my site, the cubes are continually updating, when the power fails, SSAS databases have a good chance of being corrupted. The only solution to this, that I know of, is to restore the affected (typically all) SSAS databases. I find the quickest way to do this is to stop SSAS, empty the SSAS data directory, start SSAS and recover all the databases. One thing you will also need to do is add back any userids that had SSAS administrator privileges as they were held in a file on the SSAS data directory.

Windows Server Performance Tuning

2011-12-06T12:05:00.001+11:00

I just came across this very good whitepaper on Windows 2008 Server performance tuning. There is will be a lot of information that you already know, but you will probably learn some new things from this document. There is quite a lot of information on storage, networking, processor utilisation and virtualisation. The virtualisation section was most informative for me.

The document is in Word format http://msdn.microsoft.com/en-us/windows/hardware/gg463394

Why doesn't SSAS cache the entire cube?

2011-12-05T17:09:00.001+11:00

Would you expect a 36GB server dedicated to SSAS to eventually cache an entire 11GB cube in memory? If so, you would be wrong. SSAS does not necessarily keep aggregations in memory, even though memory used is less than Memory\LowMemoryLimit.

I have seen this effect on many production servers but never quite believed it. There is much more RAM than cube size, yet SSAS is continually losing data from cache and asking for physical IO. Actually, SSAS relies on the Windows File Cache to reduce much of its IO. However, the Windows File Cache does not cache the entire cube either.

To demonstrate this effect, I have set up a 36GB server, dedicated to SSAS, and set up an 11GB MOLAP cube. Then, over a period of 19 hours, I have client tasks querying the cube in a semi random fashion. The MOLAP cube was not updated over the period. Over the 19 hours, if SSAS or Windows File Cache were effective, there wouldn't be much more than 11GB of read IO on the SSAS data drive over the 19 hours. However, the system reached an equilibrium, where it was requesting over 20MB of read IO/second. If you add this up, the system read 1.3 terabytes of data from the 11GB cube (20MB*60*60*19). Obviously, the system was not effectively caching the cube. This is very different from SQL Server, which would quite happily keep an 11GB database in memory, effectively eliminating further read IO.

My server was running Windows 2008, SQL Server 2008 R2 with default configuration settings. The SSAS database was located on the d: drive. Nothing else is located on the d: drive
Here is a Perfmon chart with some of the key counters during my test.
SSAS (msmdsrv.exe) did not use more than 12GB of memory during the test, which is well below the LowMemoryLimit 65% (which is about 23GB).

During the 19 hours, SSAS requested, on average, 102MB/second of read IO. The Windows File Cache was able to satisfy about 80% of these read requests, so the physical disk bytes/second was only 20MB/second.

The Perfmon chart above shows some of the key counters. Notice

The Process (msmdsrv) Bytes/second y axis scale is in MB, often going off scale at 100MB/sec. These are not necessarily physical IO, as the Windows cache will satisfy many.
The PhysicalDisk Bytes/second is averaging 20.1MB/second for the 19 hours
The Windows Cache Bytes is sitting between 0.6GB and 1.1 GB.
The red line (Cache Copy Reads/sec) is the IO requested of the cache. These are IO that the Windows cache has managed to satisfy by finding the page in its cache.
See how the Cache Copy Reads/sec is satisfying IO requested of the msmdsrv process, reducing the PhysicalDisk Bytes/sec. Not so easy to see, as the reads are reads/sec, while the PhysicalDisk and Process are in bytes/sec, but you can see it.

Now that I understand this effect, I am much more appreciative of the Windows Cache. This is very different from a SQL Server system, where the Windows Cache has little to do as SQL disables the Windows File Cache for its database files.

However, you can clearly see that SSAS (or the system) is performing much more IO than would be necessary if the entire 11GB cube was cached in memory.
There are a couple of takeaways from this exercise

Be thankful that we have a Windows Cache, and consider configuring it for SSAS.
Try and put SSAS databases on very fast (ideally solid state disks) storage devices. If you can't avoid the IO, at least make it fast.
Partition large cubes. This helps reduce logical IO requests and to the extent it bunches the popular data together it will probably help the Windows Cache hold the hot data more effectively.
Hopefully, in a future version, either SSAS or the Windows File Cache will cache more of the cube.

By the way, this effect is not always noticeable. If your cube is less than 2-3GB, then it is likely to be well cached between SSAS and Windows File Cache. Also, if you cube is much larger than the memory available to SSAS, then you would expect to see continual IO, and it is likely to be quite well optimised. However, when you have a 64 bit server with a cube that is larger than 3GB but is comfortably less than the server memory, you might be surprised to see the volume of continual IO.

Causing a Deadlock in SQL Server

2011-11-27T20:37:00.001+11:00

SQL Server has a background process that is continually looking for deadlock chains. If it finds a deadlock chain, it will rollback the transaction that has done the least amount of work, which should be the fastest to rollback. That's unless one of the transactions has volunteered as a deadlock victim by setting their deadlock_priority to low.
If you want to cause a deadlock, it is quite easy. Most DBAs will be able to do it, and it can be useful in determining what profiler and logging information is available in the event of an unplanned deadlock.

Here are some instructions to cause a deadlock between 3 transactions. Most deadlocks are between 2 transactions, but a deadlock chain can have any number of transactions. By the way, there are many ways to set up a deadlock, the only common characteristic is that there are a chain of processes that are waiting on each other for locks. So in a 2 process chain, Process A is waiting on a resource held by Process B and Process B is waiting on a resource held by Process A. As you can imagine, the only solution is for one of the transactions to be rolled back. They can't both go forward.

In my experience, a common class of deadlocks is the one caused by update (update, insert or delete) transactions that update so many rows that the lock must be escalated. For example, escalate several page locks to a table lock. When there are two transactions like this starting about the same time, they both start updating, and taking locks, until one of them escalates to a table lock. At this moment that transaction waits on the other. But if the other is also destined to escalate to a table lock on the same table, there will be a deadlock.

Now, to cause a deadlock between 3 tasks follow these instructions.
You might like to start SQL Profiler and enable a trace for Lock:Deadlock graph, Lock:Deadlock Chain and Lock:Deadlock, which will give you considerable information on the deadlock chain participants and resource locks.
Create 3 tables

create table t1 (c1 int, c2 varchar(50))
create table t2 (c1 int, c2 varchar(50))
create table t3 (c1 int, c2 varchar(50))

Create 3 query windows in SQL Server Enterprise Manager.
In the first query window execute
begin tran
insert into t1 select 1, 'abc'

In the second query window execute
begin tran
insert into t2 select 2, 'xyz'
Select * from t1

The second query window will be waiting on the first query.
In the third query window execute
begin tran
insert into t3 select 3, 'mno'
Select * from t2

The third query window will be waiting on the second query. At this point in time there is no deadlock. We just have a locking chain, with query 1 at the head. You can see the locking chain if you execute.
sp_who2

In the first query window execute
--begin tran
--insert into t1 select 1, 'abc'
Select * from t3
Now, query 1 will be waiting on query 3, which is waiting on query 2, which is waiting on query 1. We have a deadlock. You should notice that within a couple of seconds, one of the queries is cancelled and the transaction rolled back.

Msg 1205, Level 13, State 45, Line 4
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now, have a look at the Profiler output, and you should see your deadlock chart with the three processes and the locked resources. The transaction that was selected as the victim, and rolled back, has a cross through it. If you hover over the transactions, you will see the last batch to be submitted. Note, the last batch is not necessarily the entire transaction. There may have been an explicit BEGIN TRAN and many statements within the transaction that aren't included in the chart. If you manage to capture a blocking transaction in action, you can see the locks it has (and ones its waiting on) with the sp_lock command.

Remember to COMMIT or ROLLBACK the two queries that weren't rolled back. There is nothing worse that a query holding locks while the user has gone off to do something else.

Fix Query Performance by Creating Statistics

2011-11-22T06:40:00.001+11:00

SQL Server's optimiser will automatically create statistics on individual columns that are used in predicates. See picture of database properties to the right. However, it will not create statistics on column combinations unless they are composite columns in an index. This is where you might be able to help the optimiser by creating statistics on column combinations. This technique is most appropriate where the columns in your predicates have a relationship. For example, my InternetLogtable (1.2 Million rows) contains a row for each internet request. It has columns ClientAgentId and ClientHostId, where ClientAgent is the name of the browser, and ClientHost is the IP address of the client machine. As you can imagine, most requests from one ClientHost will tend to have the same ClientAgent. However, SQL Server's optimiser does not know this. It has statistics on ClientAgentId and ClientHostID so if when it determines the cost of the following queries it can quite accurately determine how many rows will be returned. For the first query it estimates 332 rows will be returned. For the second query it estimates 110 rows will be returned. In both cases, the estimate is very accurate. (There do happen to be indexes on each of the individual predicate columns, although even if there weren't indexes, it would automatically create statistics and get similar estimates.)

select * from internetlogtable with (nolock)

where ClientAgentId=72383

select * from internetlogtable with (nolock)

where ClientHostID=417408

However if we combine both predicates, as in the following query

select * from internetlogtable with (nolock)

where ClientAgentId=72383

and ClientHostID=417408

The optimiser assumes that ClientAgentId and ClientHostId are independent and thus there will be very few, if any, records satisfying both predicates. Essentially it is guessing that the number of rows returned will be 332/1200000 * 110/1200000. It rounds the number up to 1. Here is the query plan without statistics. Notice how the Estimated Number of Rows is 1. Actually, when it says 1, it typically is estimating <1 records, but it always rounds up to 1.

Now, what would happen if we create statistics on the two predicate columns?
create statistics StatsAgentIdHostId
on internetlogtable (ClientHostID,ClientAgentID)

SQL Server's optimiser now has data on the combinations of ClientHostId and ClientAgentId and finds that this particular combination will return about 110 rows. Here is the query plan. Notice that the Estimated Number of Rows is now 110.96! This is much more accurate.

In the query example I have used here, the query plans are different, although not dramatically different. However, if this table was joined with a few others, some of which very large, the query plan can change dramatically and make the difference between a sub second query and a several minute query.

Understanding these subtleties of the SQL Server query optimiser will help you tune queries in an efficient manner. If you were not aware of this technique to create bespoke statistics, you might solve this issue with a new index on the two columns, which would solve this query performance but at a great cost to overall performance.

By the way, here is an illustration of what statistics SQL Server keeps. There is a lot of information. For example, there are even as many key ranges as can fit in an 8KB page with the number of distinct keys, equal keys and rows in the range. Notice the All density column in the second set of statistics. It is telling us that a single value for ClientHostId, will on average return 0.0001507841 of the total rows. It also tells us that ClientHostId and ClientAgentId will, on average return 0.0001226091 of the rows. Not much less! Ie, if you have the ClientHostId, the ClientAgentId does not filter the rows much more.

I encourage you to get to know the SQL optimiser better. The more you know about the optimiser, the more you will be able to tune your queries. And the way to get to know the optimiser is through the query show plan.

Parallelised Queries are Selfish Queries

2011-09-17T16:59:00.001+10:00

SQL Server Parallel Query Processing and MAXDOP

I am prompted into writing this post after reading the msdn article Understanding and Controlling Parallel Query Processing in SQL Server. It is a very informative article. I cannot disagree with anything in the article, but there is an elephant in the room. The article dissregards IO performance bottlenecks. It actually states that it will disregard IO performance. "We assume that your I/O system is configured properly ..... so that the I/O system is not the primary performance limiter." I am going to discuss IO performance with MAXDOP, because I believe it is often the main performance limiter.

As a bit of background, SQL Server has a parallel query processing feature that is enabled by default. This permits SQL Optimiser to choose a plan that involves breaking one query into sub tasks that can run in parallel. This can involve executing, essentially, the same plan, but in parallel subtasks, or it can involve a completely different plan and again breaking the query into subtasks that can run in parallel. Really quite an amazing piece of technology. When it is working well for a query, it is interesting to see figures like cpu=10 seconds, elapsed=1 second!

In many of the very large database environments where I work, IO is the most critical resource. Perhaps this is my misfortune, but I suggest that IO performance is often a performance limiter in large database systems, and probably will be so until SSD (solid state disks) are more commonly employed. The problem with parallel query plans is that one query can simultaneously request multiple table scans, selfishly requesting more resources in parallel in the hope of getting a faster response. Just one table scan from one query will tend to have parallel IO requests, as multiple SQL Server read engines are given multiple page read ahead requests. So, even with a single non parallelised SQL query, there will already be some parallel IO requests. Run several non parallelised queries at the same time and there are many more parallel IO requests than there are queries. That is all before Parallel Query Processing kicks in!

Let's take a very simple example, where the SQL query has a predicate on a non indexed column of a very large table (the table is larger than SQL cache), therefore requiring a sequential scan of the table. Without Parallel Query Processing, SQL Server will sequentially scan the very large table, using several read engines working in parallel to get multiple page ranges. Note, there is already parallel IO requests with a single non parallelised SQL query. Now if we enable Parallel Query, SQL can break the scan into several sub tasks, each of which will ask for range scans of the table. Each of these scans can busy up multiple read engines, which can lead to IO throughput saturation. That is the parallelised query will result in scanning the same table, but it will flood the IO system with many more parallel requests that can end up in a long queue. A good way to think about it is to imagine an old fashioned bank branch with 30 tellers and everyone queues up for the next available teller. That would be non parallelised. Now imagine that someone comes in with bags of money that requires lots of counting and when he/she gets to the front of the queue, all the tellers attend to counting his/her money together (in parallel). This might be good for the selfish individual, they get served faster, but it is not good for the performance of the branch, as now all customers will be waiting for the selfish customer to finish. For this reason, I like to think of parallelised queries as selfish, or unsocial. They want to take all the resources, even though this is not an efficient use of the overall resources and others are negatively impacted. This is fine in a system with very few (perhaps one) customer. Note, there is always an overhead in parallelising a query, even if it is using essentially the same plan as a non parallelised query. This is because there is overhead in distributing and regathering the sub tasks.

There is an argument that data warehouses have very few users so that Parallel Query is an ideal performance option. However, I see modern data warehouses with large numbers of users simultaneously submitting queries (dashboards, web pages, reports etc), and you typically don't want one user to selfishly hog critical resources such as IO.

I might sound like I am against Parallel Query. That isn't true. It is a fantastic feature. However, I would encourage DBAs to consider setting Max Degree of Parallelism to 1. Note; that is not disabling Parallel Query, it is just the default. So, by default, queries will not be selfish. If you have a particular ETL task or report query that you want to parallelise, you can simple add OPTION (MAXDOP n) to the query, where n is the number of potential sub tasks running in parallel. So you are not turning it off, you are just ensuring that queries aren't selfish by default. Also, just to be clear, with Max Degree of Parallelism set to 1, your system can still have many SQL queries running in parallel (with each other) it is only that individual queries will not be broken into parallel running subtasks.

The reason I configured Max Degree of Parallelism to 1 the first few times was because I was working in environments with a wayward parallelised queries. SQL Optimiser chose a very very poor plan thinking that parallelisation would make this plan fast. The same queries with Max Degree of Parallelism =1 (or MAXDOP 1) the optimiser chose a very different, and efficient plan. When I reduced the "Max Degree of Parallelism" to 1, these queries, and the whole system ran more efficiently.

An annoying limitation of MAXDOP is that you are unable to set MAXDOP for queries that SQL Server Analysis Services uses when processing fact tables. You can only provide SSAS with a table or view name, and it constructs the query. There is no way to inject a MAXDOP option. This is another reason I like to set Max Degree of Parallelism = 1, so that the default setting is non parallelised.

One word of warning. Changing Max Degree of Parallelism to 1, can (in fact by design it should) make some queries run slower. It can also change dramatically the query plan (as I discussed above) and while this will usually result in more efficient queries, it can also involve a poorer performing plan. In my experience the reverse is more likely (when there are considerably different plans between parallelised and non parallelised, the non parallelised plan is more often the more efficient. Consequently, it is less risky to configure Max Degree of Parallelism =1 while developing a solution (and using MAXDOP n for individual queries where appropriate) than to change a production environment.

The effect of Max Degree of Parallelism is not just on the individual query. If you want to know the impact of Max Degree of Parallelism, you should monitor vital statistics (cpu, memory, IO), get average query response times (for common queries) and compare them with before and after configuration changes. Another, albeit small, cost of Max Degree of Parallelism<>1 is that each parallelised query will now have two plans (it always keeps a non parallel version for when cpu is high) so that more pressure is put on the plan cache (it is bigger, or it holds fewer plans). Note, SQL Server is great in that you can change the Max Degree of Parallelism option dynamically. No SQL Server restart required. You might even consider setting Max Degree of Parallelism =1 for the online day and resetting Max Degree of Parallelism =0 for the overnight ETL.

A closely related issue is Resource Governor, which also has a MAXDOP option that looks like it can override the MAXDOP option in the query. However, as I understand it, the Resource Governor does not work like MAXDOP in a query or sp_configure. If you set MAXDOP to 1 in the Resource Governor, the underlying queries can still chose parallel plans (no change there). What the Resource Governor does is to restrict the query to only one thread! This is almost the worst of both worlds, as the optimiser can choose the selfish (potentially faster) parallel plan, but the Resource Governor will ensure that the parallel tasks are serialised with just one thread.

Why are there less data in the cube than the fact table?

2011-09-11T11:22:00.000+10:00

Sereral customer have asked me why they have less data in their cube than they have in the fact table. Essentially, when they view the cube Measures.NetSales does not equal Sum(NetSales) from the fact table. So here are the some possible causes, that I would investigate in such circumstances.

The measure datatype cannot hold the aggregated number. For example, the fact table might have an integer datatype for the SalesQuantity column, which might be fine for the SQL table. However, by default, SSAS will give the SalesQuantity measure the same datatype, but now it must be able to contain the sum(SalesQuantity). If sum(SalesQuantity) is greater than 2^31 SSAS will not display any errors, and the Measures.SalesQuantity will show numbers that appear to wrap around. So the numbers are much smaller than expected (almost random) at an aggregated level. The solution is simple, just change the datatype in the cube to a much larger number, for example bigint, which can be up to 2^63. One of the good things about using bigint over int, is that the cube is almost exactly the same size, as SSAS will compress the leading zeroes in binary numbers very effectively. If you need numbers larger than 2^63, you might need to use a double datatype, which, although potentially much larger is an imprecise number. Also, a double datatype measure, will typically make the cube significantly larger than a binary measure containing the same information.
There are missing members on one or more dimensions and missing members are not enabled. The missing member facts will not be included in the cube. I personally believe the cube should contain all the facts in the fact able. Even if there is a missing member, the data should still be available. For example, if there are sales for a ProductId that does not exist, typically, you will still want to know about the sales even though you cannot say which product it was for. I prefer to enable missing members, and just hide them if you must. It is also a good practice to regularly check for missing members, either in the DW or in the cube (through missing members).
The third situation where the cube numbers are light is an unusual one. It is where the fact table (actually a view) returns an arithmetic error such as divide by zero. Most times SSAS will sympathetically return the SQL error, and you will know there is a problem during cube processing. However, sometimes SSAS does not sympathetically return the SQL error, rather, it returns a successful cube process with the rows up until the divide by zero. There are two ways of locating this error. Firstly, you could simply execute the same SQL query in SQLEM window and wait for the full resultset or the arithmetic error to return. This is not practical in many circumstances due to the number of rows in the resultset. I guess you could get SSIS to help you, but an easier way is to use SQL Profiler and trace Error events.
You have a referenced dimension and the INNER JOIN clause that SSAS is generating for SQL Server is removing fact records that don't join with the intermediate dimension. To identify this issue, you need to execute the SQL generated by SSAS, and compare its counts with the raw fact table.
Is your cube (measure group) processed incrementally? If so, there might be a logic issue with the way you are incrementally processing. To verify, you could reprocess the entire measure group and see if the numbers now equal the fact table.
Is the data in your fact table changing? Of course, if the fact table is changing, the MOLAP cube won't know about it. This can be related to incremental processing, where the incremental processing logic is good, but historic data records are changing. Strictly speaking you cannot incrementally process updates, only new records. If the data has changed, the cube (partition) will require processing.
Have you a default member that is not the [All] mmeber, or do you have a dimension without an [All] member. In these cases, you would need to take this into account when comparing Measures.NetSales with sum(NetSales).

I hope this help you identify reconciliation issues between the cube and fact table. A couple of suggestions when reconciling such issues

Use physical measures in the cube. Even if they are hidden, you can still query the physical measures.
Ideally, start off by reconciling record counts. If you can, put a record count in the cube.
When you are running SQL queries against the fact table, use the SQL statement that is generated by SSAS, since it might be referencing a different table/view than you expected. Also, as in point 4 above, it might be performing an INNER JOIN with an intermediate dimension that has missing members.

Improving MDX join performance

2011-08-10T07:41:00.009+10:00

Analysis Services will tend to instantiate sets created from joins. Some sets can be extremely large and will actually fail with insufficient resources.
Server: The operation has been cancelled.

The solution is to write the MDX query in such a way that the intermediate sets are smaller. Here is an example of a problematic query that is asking for the top 10 hour/City/Date combinations. The output is simply 10 rows, but AS must create a set with hundreds of Millions of members to get the top 10.

select
[Measures].[Bytes Total] on Columns,
topcount(
[Client Host].[Client Geography].[City]
*[HoursOfDay].[HoursOfDay].[Hour Of Day]
*[Date].[Year Month Day].[Day]
,10,[Measures].[Bytes Total])
on Rows
from EasternMining

Here is a way to improve performance using the generate() function, another one of my favourite MDX functions.

select
[Measures].[Bytes Total] on Columns,
topcount(
generate([Client Host].[Client Geography].[City]
,{[Client Host].[Client Geography].currentmember}
*[HoursOfDay].[HoursOfDay].[Hour Of Day]
*[Date].[Year Month Day].[Day])
,10,[Measures].[Bytes Total])
on Rows
from EasternMining

The generate statement is breaking the query into a couple of steps, so that the largest set will probably be the Days*Hours, from which it is only keeping the top 10 for each city. The outside set, for every city, will only have 10 rows per city. The query is logically equivalent to the first query, meaning it will return the same results.

We could take this logic further with a second generate statement so the inside query only has 1o rows per city day combination.

select
[Measures].[Bytes Total] on Columns,
topcount(
generate([Client Host].[Client Geography].[City]
,topcount(
generate([HoursOfDay].[HoursOfDay].[Hour Of Day]
,topcount({[HoursOfDay].[HoursOfDay].currentmember}
*{[Client Host].[Client Geography].
currentmember}
*[Date].[Year Month Day].[Day]
,10,[Measures].[Bytes Total]))
,10,[Measures].[Bytes Total]))
,10,[Measures].[Bytes Total])
on Rows
from EasternMining

We can improve this query further by requesting nonempty() sets

select
[Measures].[Bytes Total] on Columns,
topcount(
generate(nonempty([Client Host].[Client Geography].[City],[Measures].[Bytes Total])
,topcount(
generate(nonempty([HoursOfDay].[HoursOfDay].[Hour Of Day],[Measures].[Bytes Total])
,topcount({[HoursOfDay].[HoursOfDay].currentmember}
*{[Client Host].[Client Geography].currentmember}
*nonempty([Date].[Year Month Day].[Day],[Measures].[Bytes Total])
,10,[Measures].[Bytes Total]))
,10,[Measures].[Bytes Total]))
,10,[Measures].[Bytes Total])
on Rows
from EasternMining

The NonEmpty() function is a very simple way to reduce the set size, and of course it can be done without using the generate() function. So going back to the original query, we might have found that the following change was sufficient to avoid resource errors.

select
[Measures].[Bytes Total] on Columns,
topcount(
nonempty(
[Client Host].[Client Geography].[City]
*[HoursOfDay].[HoursOfDay].[Hour Of Day]
*[Date].[Year Month Day].[Day]
,[Measures].[Bytes Total])
,10,[Measures].[Bytes Total])
on Rows
from EasternMining

However, I wanted to show the generate() function first, as it can improve the query in a way that the nonempty() cannot.

I should add that generate() and nonempty() functions are not panaceas for MDX query performance. The more you know MDX and the way SSAS executes the query, the more you will have in your toolkit to improve query performance.

MDX Root() function doesn't support multiselect

2011-07-16T17:10:00.008+10:00

One of my other favourite MDX functions is Root(). It's a really easy way to get all the filters off a dimension. I often use it in calculations defined in the cube.

However the Root() function does not support multi-select and you might get this error message.

#Error Query (6, 30) The MDX function ROOT failed because the coordinate for the 'Region' attribute contains a set.
Fortunately, there is a workaround. Unfortunately, it means not using the Root() function and going back to the .[All] member of all the attributes (or all the ones being used in the query) of the dimension.
For example, the following query will hit the error.
WITH
MEMBER [Client Host].Region.[AfricaAsia] AS
AGGREGATE({[Client Host].Region.&[Africa]
,[Client Host].Region.&[Asia]})
MEMBER Measures.[HitsPercent] as
[Measures].[Request Count]/(ROOT([Client Host]), [Measures].[Request Count])
SELECT
{Measures.[HitsPercent]}
ON COLUMNS
,[Date].[Month].[Month]
ON ROWS
FROM [EasternMining]
WHERE
([Client Host].Region.[AfricaAsia])

The fix is simply to change Root([Client Host]) to [Client Host].Region.[All]. ie.
WITH
MEMBER [Client Host].Region.[AfricaAsia] AS
AGGREGATE({[Client Host].Region.&[Africa]
,[Client Host].Region.&[Asia]})
MEMBER Measures.[HitsPercent] as
// [Measures].[Request Count]/(ROOT([Client Host]), [Measures].[Request Count])
[Measures].[Request Count]/([Client Host].Region.[All], [Measures].[Request Count])
,format_string="#,#0.00%"
,non_empty_behavior="measures.[Request Count]"
SELECT
{Measures.[HitsPercent]}
ON COLUMNS
,[Date].[Month].[Month]
ON ROWS
FROM [EasternMining]
WHERE
([Client Host].Region.[AfricaAsia])

Note, I have only put the .[All] member in for the Region attribute, whereas you really need to add in a .[All] member for all the dimension attributes to be equivalent to the Root() function.

By the way, I don't know why the Root() function doesn't support multi-select. One would imagine that it should.

Because of this limitation, I might be using the Root() function less liberally in calculations stored in the cube.

Single Click SQL Management Studio

2011-06-25T13:26:00.004+10:00

Here is a simple way to avoid clicking on the inevitable SQL prompt when starting SQL Server Management studio.
On the shortcut simple add -S -d -E

For eample, on my PC, the shortcut is
"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -S Manly -d master -E

Now when you click on SQL Server Management studio, it will connect automatically to the server/dbname. No more prompts to the server/dbname that you connect to every day.

linkmember, my favourite MDX function

2011-06-10T06:30:00.005+10:00

Linkmember is one of my favourite MDX functions. It tends to be useful whenever you have role playing dimensions. For example, when you have multiple (role playing date dimensions or role playing geography dimensions) such as OrderDate and DespatchDate, or CustomerStore and PurchaseStore.

You might have a report that displays the order date on rows, with sales on columns. You would also like add a column to show sales, but sliced by delivery date. So what you want to do, is put the same, sales, measure on columns, but have the "delivery date" dimension on rows.

This is exactly what the linkmember does. Simply create a calc member that uses linkmember to switch the OrderDate on rows to DeliveryDate. Here is a simple example.

with member
Measures.AmountDelivered as
(Measures.Amount,
linkmember(OrderDate.CalendarYWD,DeliveryDate.CalendarYWD),
root(OrderDate))
select
{Measures.Amount,
Measures.AmountDelivered}
on Columns,
non empty
OrderDate.CalendarYWD.Day on Rows
from MyCube

One thing that you need to remember is that the linkmember will effectively get the member with the same key in another dimension. However, it will not change the original member. So you probably want to also override the original member with a root() or .[All].

Another way of achieving the same functionality as LinkMember is to extract the key and use StrToMember() to construct the appropriate member of the other dimension. However, LinkMember is much more efficient, and is the preferred tool.

Tech Ed 2011 Sessions available

2011-06-05T10:18:00.005+10:00

If you didn't afford the time or money to go to Tech Ed, don't worry, you can still view the sessions online, and in your own time.

TechEd North America 2011

How many cubes

2011-05-04T20:20:00.007+10:00

I am often surprised how many cubes some BI developers create. It would appear that each time they have a requirement for a piece of information, they will create a new cube. Sometimes this is amplified because they are using denormalised summary tables, and creating new ones when they need a bit more dimensionality or a new measure, leading to a plethora of overlapping data.

Since SQL Server 2005, it is possible to put dozens of fact tables (cubes in the old days) into one cube with separate measure groups for each fact table. This is a great feature. For example, Sales and Budget have slightly different dimensionality as Sales is by cashier and hour of day, whereas, budget does not have cashier and time granularity is only available down to week. Having these in the same cube is great, since it makes querying of sales against budget (by all the common dimensions) very easy. Also, if a user is only interested in one measure group, say sales, they can ask the browser that measure group, and they will on see the associated measures and dimensions. In the same way they might browse a separate cube.

You might ask, are there any performance implications of having one super cube?

In terms of cube size and processing overhead, the design alternatives are comparable.
MDX queries can be broken down to FE (formula engine) and SE (storage engine) components. The SE component of a query isn't materially affected by unrelated dimensions, since the storage is comparable. However, the FE component of a query is affected by the unrelated dimensions as it is works within the entire cube space (larger with more dimensions).

There is a LookupCube function in MDX that allows you to get the value from another cube. This might be useful in a design that exploits multiple cubes. But, don't see this as a panacea for multiple cubes. It has its own performance overhead and is not as functional as having the measures in one cube.

There might also be operational considerations. The design alternative I have discussed is having multiple cubes in the same database, but if the cubes really are unrelated, you might consider having separate SSAS databases for them. This would lead to more operational options including, parallel processing, and even hosting databases on separate servers.

So, as a cube designer, you must weigh up the performance, functional and maintenance costs. As an over simplified summary, on the side of mini cubes is the minimisation of FE performance costs, while on side of super cubes are increased user functionality.

For another discussion on this topic see Chris Webb's blog

MDX for Top n by group

2011-05-04T20:18:00.003+10:00

Here is a little MDX that will get the top n list for each of another set of members.

For example, for Each region, I might want to get a list of the top images in that region.

The query is very simple, just a matter of employing the generate function.

select [Measures].[Hits] on 0,
non empty
generate([Client Host].[Client Geography].[Region],
([Client Host].[Client Geography],
topcount(order([Target].[Resource Hierarchy].[Resource Type].&[.jpg].children,[Measures].[Hits],bdesc),10)))
on 1
from EasternMining

You can plug in the grouping, ordering that you want. Even nested top n lists.

(re) Clustering a Large Online Table

2011-03-13T08:16:00.010+11:00

One of the most important performance controls you have on a relational database is the clustering sequence of your large tables. Unfortunately, it is often not considered until there is a performance issue. Clustered indexes are important on their own, but they are especially significant when you are joining multiple large tables. There are often orders of magnitude performance gains by clustering the large tables in the same sequence. However if you have a large table that needs to be online 24 hours a day, seven days a week, it can be difficult to make such a big change.

I have achieved this a few times now, on tables that are only inserted into (ie transaction tables). The only requirements are that the table is inserted into only (no updates) and that the table has a column that is inserted into in ascending sequence only (eg transactionid, datetime etc). Essentially, this is done by creating a new table, which has all the live table's records inserted into it using bulk load (SSIS package task). This makes it very efficient, but it will take a long time on a large table, so the new table won't be up to date. The trick is to keep low and high water marks, then you can open a transaction to copy in the last few records and rename the tables within the same transaction. In this way, at the point of committing, both tables will have the same content, and any active transactions will, at worst, wait a couple of seconds for the last insert and renaming to occur.

Here are the steps involved in the SSIS package

Create the new table with the clustered (and non clustered) indexes you want.
Create a control table to maintain high-low watermarks. Set the high water mark to the maximum ascending key value in the live table.
Perform a bulk load (SSIS Data Flow task) for all the live table rows up to and including the high water mark. This task might take several hours.
Set the low water mark to the high water mark, then re set the high water mark to the new maximum ascending key value in the live table.
Perform the bulk load again. This time it will just be the rows that have been inserted during our earlier bulk load. After this task, the new table will be very close, but not quite, up to date to the live table.
Within the scope of a transaction lock the live table, insert records above the high water mark, rename the tables (and PKs) and commit the transaction. The new table is now live.

A few notes on the process

You will need to have sufficient space in your database to accommodate two copies of the table.
You will need to have sufficient space in your database logs to hold log records from the start of the Main Bulk Load to the end of the Main Bulk Load. So, if the bulk load takes 4 hours, their will be a transaction open for 4 hours and a log truncation won't truncate anything within the 4 hours.
You will need to have sufficient space in tempdb to perform the sorts for the clustered index and nonclustered indexes.
If you have sufficient space in the database files and logs, you should set the maximum commit size on the destination to 0 (zero). Having other values will tend to slow down the load process (although less disk space is required) since the indexes will be maintained at each commit.
It is quite likely that your large tables are partitioned and compressed. This doesn't need to make any difference to the reclustering process above. However, if the table is already partitioned, you could improve performance by looping through each partition. If your table isn't already partitioned, this process could be the opportunity to partition your table.
Don't be tempted to use NOLOCK when reading the live table.
Test your package before you run it in production.

There is no special code in the package I have created. The most significant task is the very last one, which is performed within the scope of a transaction. To ensure there is no opportunity of a deadlock, the task first asks for an X lock on the live table. Here is some sample SQL from each of the tasks.

Create Control table

create table ReclusteringControl( ReclusteringControlID int default 1, LowWater bigint not null default 0, HighWater bigint not null default 0, CONSTRAINT [PK_ReclusteringControl] PRIMARY KEY NONCLUSTERED (ReclusteringControlID ASC))

Insert into ReclusteringControl values(1,0,0)

update ReclusteringControl set HighWater=(select max(rid) from MyLargeTable)

Input for Main Bulk Load

select * from MyLargeTablewhere rid<=(select HighWater from ReclusteringControl)

Set New HighWatermark

update ReclusteringControl set LowWater=HighWater
update ReclusteringControl set HighWater=(select max(rid) from MyLargeTable)

Second Bulk Load (input statement)

select * from MyLargeTablewhere rid<=(select HighWater from ReclusteringControl) and rid>(select LowWater from ReclusteringControl)

Final INSERT and Rename

--set identity_insert MyLargeTable_new on

--This is required if there is an identity column on the table

begin transaction

update ReclusteringControl

set LowWater=HighWater

select top 0 * from MyLargeTable with (tablockx)

update ReclusteringControl set HighWater=(select max(rid) from MyLargeTable)

insert into MyLargeTable_new --columns need to be explicitly declared if identity cols are inserted

select * from MyLargeTable where rid<=(select HighWater from ReclusteringControl) and rid>(select LowWater from ReclusteringControl);

exec sp_rename MyLargeTable,XXXX_MyLargeTable;--should delete this table

exec sp_rename MyLargeTable_new,MyLargeTable;

exec sp_rename PK_MyLargeTable, PK_MyLargeTable_XXXX

exec sp_rename PK_MyLargeTable_new, PK_MyLargeTable

commit transaction

--set identity_insert MyLargeTable off

Anything is possible with MDX

2011-02-14T20:05:00.005+11:00

I was with one of my favourite customers last week and had an impromptu challenge put before me. We had a cube containing the company's sales data and the challenge was to reproduce a report they had created in their legacy system. (I found out later that this report had taken a couple of months to produce.) The requirement was to create a report that would display the last 7 days of a particular outlet and for each day show the sales and transactions for the top hour of that day.

I have often been quoted as saying that if the data is in the cube, one MDX query can answer any question that you might have. So the pressure was on to create the report. Fortunately, they were using ReportPortal software, which is a highly functional thin client tool, and it has good charting capability. The web browser tool allows you to create your own sets and calculated members. So all I really needed to do was create a set for the last 7 days. I will reproduce a similar query using my weblog database, which has comparable dimensions.

Here is a set declaration for the last 7 business days.

tail(nonempty([Date].[Year Month Day].[Day],[Measures].[Bytes Total]),7)

The calculated members for the top hour etc are simply a matter of taking the top hour and getting the attribute/tuple require. Here is the full query on my weblog database. Note, the good thing about a tool like ReportPortal is that you can declare sets and calculated members and it will still honour filters and hierarchies that you have dragged and dropped on rows/columns. So the only MDX you need to write is for the set and calculated member, the rest of the report is created by dragging and dropping from the palette.

with
set Last7Days as tail(nonempty([Date].[Year Month Day].[Day],[Measures].[Bytes Total]),7)
member Measures.TopHourName as order([HoursOfDay].[Hour Of Day],[Measures].[Hits],BDESC).item(0).member_caption
member Measures.TopHourSessions as (order([HoursOfDay].[Hour Of Day],[Measures].[Hits],BDESC).item(0),[Measures].[Sessions])
member Measures.TopHourHits as (order([HoursOfDay].[Hour Of Day],[Measures].[Hits],BDESC).item(0),[Measures].[Hits])
select
{Measures.TopHourName,Measures.TopHourSessions,Measures.TopHourHits} on columns,
[Date].[Month].[Month]*Last7Days on rows
from EasternMining
where ([Client Host].[Client Geography].[Region].&[Americas].[UNITED STATES])

Like all good cube browers, ReportPortal will graph your results. In this case, the most appropriate chart was a hybrid column/line chart, with sales as columns on the left hand y axis and transactions as a line on the right hand y axis.

The takeaway from this is that, so long as the cube has the necessary data, a single MDX query can get the answer to any question.

PowerPivot versus Analysis Services

2011-01-16T09:51:00.008+11:00

Chris Webb has done more to demystify Microsoft's PowerPivot story than any other person. Recently, he has put up a "quiz" to help people decide between PowerPivot and SSAS. The quiz is presented in a light hearted fashion, but it contains a lot of Chris' analysis on the strengths and weaknesses of each technology. You can see Chris' quiz on http://cwebbbi.wordpress.com/2010/11/19/powerpivot-vs-ssas-quiz/

If you are really interested in hearing Chris speak on this topic, there is a recording of him talking in Belgium on this topic here http://sqlserverday.be/video/view/?id=2

I found the quiz very interesting. Not the quiz final recommendation (PP vs AS), but the quiz questions and how the answers were interpreted, which exposes Chris' analysis. Here is a copy of Chris' quiz with the questions shaded in green and red interpreting Chris' analysis of the question answer here https://spreadsheets0.google.com/ccc?key=tcyHIY2MtHCkj-wTJUAb3Tg&hl=en_GB#

You can use this copy of the quiz to quickly get an understanding of Chris' analysis of the strengths and weaknesses of PowerPivot.

Warming the OLAP cache

2010-12-12T08:26:00.010+11:00

Analysis Services has a data (and aggregation cache) not dissimilar to a relational database cache. For really good cube performance it is desirable to have most of your cube resident in the cache. Querying the cube will help bring data into the cache, while cube processing will tend to clear the cache. More accurately, partition processing (incremental or full) will clear that entire partition from the cache.

Analysis Services, by default, will freely use 65% of a computer's memory, so data should not be pushed out of the cache unless AS is using 65% of memory. With commodity 64 bit servers, the memory available to Analysis Services should be, at least, in the tens of GBytes.

Here is an example of a query that has been run on a cold cache. Notice how the first Query Subcube has an EventClass of "non-cache data". This means that the storage engine is going to disk since the data/aggregations are not in cache.

You can help warm the cache by executing MDX queries, or you can execute the CREATE CACHE statement. The CREATE CACHE statement looks similar to a regular MDX query, the main difference being a resultset is not returned. For example

CREATE CACHE FOR [EasternMining] AS
[Port].[Ports].&[80]
*{[Measures].Members}
*{[Date].[Year Month Day].[Year].&[2010].[January].[1]
:[Date].[Year Month Day].[Year].&[2010].[December].[3]}
go
CREATE CACHE FOR [EasternMining] AS
[Port].[Ports].&[80]
*{[Measures].Members }
*[HoursOfDay].[Hour Of Day]
*{[Date].[Year Month Day].[Year].&[2010].[November].[1]
:[Date].[Year Month Day].[Year].&[2010].[December].[3]}
go

Now let's run the query again and check the Profiler trace. Here is the output

Notice now, all the Query subcubes have an EventClass of "Cache data". This means that AS has found the data for this query in the cache. Also note, the duration for the first subcube is 0ms. This is a great improvement over the original cold cache subcube of 47ms.

Generally, it is a good practice to warm the cache after cube processing. This will help the performance of the first few queries that hit the cube after processing.

Also, as a cube designer, you should be aware of the cache warming effect and design partitions so that you isolate the volatile data as much as possible, so that you minimise cache clearing when processing.

If you want to experiment with cache warming, you will probably want the ability to clear the cache quickly. Here is an example of a cache clearing statement for a cube

IISLog
EasternMining

Here is the MDX query I used in the demonstration above

WITH
member measures.MyCalc as
avg(tail(nonempty([Date].[Year Month Day].[Year].&[2010].[November].[1]
:[Date].[Year Month Day].[Year].&[2010].[December].[3].lag(1)
,[Measures].[Bytes Total]),6)
,[Measures].[Bytes Total])
SELECT {measures.MyCalc} ON COLUMNS ,
{[HoursOfDay].[All HoursOfDay].[00:00-01:59].[00:00-00:59],[HoursOfDay].[All HoursOfDay].[00:00-01:59].[01:00-01:59],
[HoursOfDay].[All HoursOfDay].[02:00-03:59].[02:00-02:59],[HoursOfDay].[All HoursOfDay].[02:00-03:59].[03:00-03:59],
[HoursOfDay].[All HoursOfDay].[04:00-05:59].[04:00-04:59],[HoursOfDay].[All HoursOfDay].[04:00-05:59].[05:00-05:59],
[HoursOfDay].[All HoursOfDay].[06:00-07:59].[06:00-06:59] ,[HoursOfDay].[All HoursOfDay].[06:00-07:59].[07:00-07:59],
[HoursOfDay].[All HoursOfDay].[08:00-09:59].[08:00-08:59],[HoursOfDay].[All HoursOfDay].[08:00-09:59].[09:00-09:59] }
ON ROWS
FROM EasternMining
WHERE ([Port].[Ports].&[80] )

What's happening on my site?

2010-10-16T18:45:00.007+11:00

My internet site has a series of close to real-time dashboards showing, among other things, internet activity on my site. It is primarily meant as a demonstration tool, but it has prooved useful to me in quickly seeing what's happing on my site (and my servers, since there is also a Perfmon dashboard).

Interesting, over the last couple of weeks, I have seen some suspicious looking referrers from Russia. You can probably still see them on the default dashboard on http://RichardLees.com.au/Sites/Demonstrations. See the Top Referrers table on the bottom. At the moment the top referrer includes "buy ultram.narod.ru" at the top, and there are a few more suspicious ones lower down. I am used to seeing Google, Bing or my blogspot compete for the top spot.

It's great with Microsoft PerformancePoint dashboards that you can dynamically drill down the table as an ad hoc cube browsing tool. I have drilled down to see the pages from the referrer, the http command (HEAD), the ip address, http return code etc. See picture. You can do this too, simply right click on the chart and take the direction you want. A tip; click the triangle at the top right of the chart and open in a new window to see the chart/grid on a new page.

Although this activity is suspicious, and might represent a potentially malicious attack, it doesn't appear to have had much success. Or perhaps all it wanted to do was populate my logs.

In any case, I feel that the dashboard has shown the value of processing logs and providing an easy and intuitive dashboard to this information. Everyone who has an internet site should have access to information on the site's activity. Also, many of the off-the-shelf web analytic tools only show you a subset of what is really happening. For example, I have Google Analytics, which are quite useful, although they only show me a subset of the activity, which doesn't include these referrers.

Wouldn't it be nice if Microsoft Windows included an install option that went something like

Would you like incoming http activity processed and kept in an OLAP cube for ad hoc browsing?

For more examples of online, close to real-time OLAP cubes and data mining models see http://RichardLees.com.au/sites/demonstrations

SSAS Dynamic Security

2010-10-10T08:41:00.011+11:00

Imagine the scenario where there are hundreds or thousands of cube users, most of which are only permitted to see "their" data. And there is a dimension with an attribute containing their Windows userid. It would be very tedious to set up a security role for every individual. Not to mention the maintenance in adding and deleting roles. Although this could be somewhat automated with AMO scripting.

A technique I like to employ is to set up just one SSAS role, which essentially contains everyone that might have some access to the cube (integrated security only). So, in the Dimension Data security tab, I restrict the "Allowed member set" to just those members where the member name is equal to the username(). Username() is an mdx function that returns the Windows userid.

The Allowed member set might look something like

{StrToMember("Branch.BranchManager.&[" + UserName() + "]")}

The default member could be

StrToMember("Branch.BranchManager.&[" + UserName() + "]")

You can add other roles, for example, one with read access to the entire cube. So the users that can only see their Branch, would be in the first role (they would only be able to see their own branch) and head office users in the second role would be able to see the entire cube.

This is a very elegant solution. As you can see, it requires no maintenance, as new/old users will automatically be able to see their own data. If the branch manager changes, the old user will have no read access and the new user will have read access to that branch.

There are many ways to extend this security. I have combined it with application tables that list users' access. This can be a many-to-one or many-to many relationship. No problem, just create a dimension (possibly many-to-many) , and grant read access to the dimension in the same way. Also, it might be that you only want part of the username, or that you want/don't want the domain. Again, that is no problem, you can use VBA functions in the MDX expression to get the substring that you want. You could also use this technique to manage a black list (Denied member set) instead of a white list (Allowed member set).

A classic use of this technique would be to support sales persons to see information relating to their customers. This technique might be combined with other restrictions. For example the dynamic security role might let browsers see a subset of their data, such as quantities, but not dollar values. And only for their customers.

Note, there is an overhead in Dimension Data security, but in my experience it is almost undetectable. Of more concern, if you needed to use it, would be the many-to-many dimension. Be wary of performance if your m2m dimension and/or m2m fact table is large.

Also note, you probably want to check the "Enable Visual Totals" check box, so that users can only see the total for members they are allowed to see.

The use of this dynamic security technique can be used in conjunction with conventional roles in the same cube.

Write Back cubes are easy with Excel 2010

2010-08-18T06:42:00.013+10:00

SQL Server has supported write enabled cubes for over 10 years, but things have just got a whole lot better with Excel 2010 (and SQL Server 2008). You can now browse and update a write enabled cube in Excel without any add-ins and without writing any code.

It's quite easy. Just right click on the partition (in the Partitions tab of the cube editor) and select Writeback settings. You can just take the default settings. Interestingly, the storage mode does not need to be ROLAP, so we get good performance without operational complexity.

Now, simply open Excel 2010, connect to the cube, and enable "What-If Analysis". This is a button in the Tools section (next to PivotChart and OLAP Tools). Now you can write over any of the cells in the cube. The numbers you have changed will have a little purple triangle in the bottom right corner. When you want to write these back simply click the "Publish Changes" button under the "What-If Analysis" button. Excel will "distribute" your updates according to the settings you have asked for.

Excel has a Settings button under "What-If Analysis", which allows you to control how values are spread, when you enter at a higher level than the cube granularity. For example, you could enter a number for the whole of 2010 and it will spread the amount across all periods based on whether you want an even spread and whether you want the updates to be incremented based on the old numbers.

Excel does not natively support the insertion of new dimension members, which would have been a nice feature for some applications.

Operationally, I don't think it is so important to migrate the writeback data to the main partition, since the writeback partition is typically MOLAP. However, it would not be difficult to move the data over in an SSIS package on a scheduled basis.

Of course, you can restrict read and write access rights based on roles, so, for example, the Australian users can only update the Australian numbers.

It now really is easy to set up an updateable cube and make available to users with Excel 2010.

Getting the last non empty value

2010-07-26T05:44:00.003+10:00

LastNonEmpty is an aggregation function available in the Enterprise version of SQL Server. However, you can create your own with a little bit of recursive MDX. Essentially, you simply create a calculated member that returns the non empty value, or if empty, it looks in the previous member. It really is very simple, and is an elegant way of writing a last non empty query. To create a LastNonEmpty calculate measure, simple use MDX such as the following

With Member Measures.LastHits as
iif(isempty(Measures.Hits),
([Date].[Year Month Day].prevmember,
Measures.LastHits
),Measures.Hits)

Notice how the calculated member is called LastHits, and the calculation actually refers to LastHits. This is the recursion. It keeps referring back to itself until it gets a day with a non empty value. If there are no non empty values, the recursion will automatically stop at the first day in your dimension and return null.

Naturally, you can add other calculations on top of this recursive calculation. For example, you might want to sum up the month to date figures using this non empty calculation. No problem, here is a MTD calculation using the last non empty calculation above.

With Member Measures.LastHits as
iif(isempty(Measures.Hits),
([Date].[Year Month Day].prevmember,
Measures.LastHits
),Measures.Hits)
Member Measures.LastHitsMTD as
sum(PeriodsToDate([Date].[Year Month Day].[Month],[Date].[Year Month Day]),Measures.LastHits)
select
{Measures.Hits,Measures.LastHits,Measures.LastHitsMTD}
on Columns,
tail([Date].[Year Month Day].[Day],31)
on rows
from
EasternMining

For examples of real-time OLAP queries on continually updating databases see http://RichardLees.com.au/Sites/Demonstrations

VARCHAR(MAX) Performance in SQL Server 2008 R2

2010-07-19T07:07:00.005+10:00

There has been some discussion around the relative performance of VARCHAR(MAX) versus VARCHAR(n) columns. I thought it would be useful to benchmark the performance of INSERT, UPDATE and BULK INSERT of these datatypes.

Essentially, I found that there is a performance cost to using VARCHAR(MAX) over VARCHAR(8000). This would suggest that you should only use VARCHAR(MAX) when you need to. (Ie you have data that can be longer than 8000 bytes.) I would add to this by saying that data types should always be as tight as practical. It would not be a good practice to make all varchar data types MAX. For example, if you application has a screen that only permits 30 characters, it would make sense to make the data type VARCHAR(30). Although, I could make exceptions if I thought this might be increased in future.

Notice; in the results of my test that INSERT and BULK INSERT are only penalised by cpu. UPDATE statements appear are penalised by reads and writes too. Consequently, inserts were only slower by a factor of 2, while updates were slower by a factor of 7. All tests were inserting or updating 110,879 rows taking 42MB. Compression was not used.

Querying Analysis Services For Cube Metadata

2010-07-15T20:05:00.007+10:00

SSAS DMVs can be very useful in providing metadata information. Ie.

$SYSTEM.MDSCHEMA_CUBES
$SYSTEM.MDSCHEMA_DIMENSIONS
$SYSTEM.MDSCHEMA_FUNCTIONS
$SYSTEM.MDSCHEMA_HIERARCHIES
$SYSTEM.MDSCHEMA_INPUT_DATASOURCES
$SYSTEM.MDSCHEMA_KPIS
$SYSTEM.MDSCHEMA_LEVELS
$SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
$SYSTEM.MDSCHEMA_MEASUREGROUPS
$SYSTEM.MDSCHEMA_MEASURES
$SYSTEM.MDSCHEMA_MEMBERS
$SYSTEM.MDSCHEMA_PROPERTIES
$SYSTEM.MDSCHEMA_SETS

SQL Server Enterprise Manager's Query editor supports "Select * From " these view. Interestingly, a lot of this information is also available through MDX. For example, here is an easy way of getting a list of all attributes (strictly speaking, hierarchies) and their level names.

WITH
member measures.HierarchyCount as dimensions.count
set HierarchySet as tail([Date].[Year Month Day].[Day],measures.HierarchyCount)
member measures.MyHierarchyNo as rank([Date].[Year Month Day],HierarchySet)-1
member measures.DimensionName as
left(dimensions(measures.MyHierarchyNo).dimension.unique_name,
instr(dimensions(measures.MyHierarchyNo).dimension.unique_name,".")-1)
member measures.MyHierarchy as dimensions(measures.MyHierarchyNo).name
member measures.HierarchyLevels as dimensions(measures.MyHierarchyNo).levels.count
member Measures.Level1Name as
case when Measures.HierarchyLevels>1 then dimensions(measures.MyHierarchyNo).levels(1).name else null end
member Measures.Level2Name as
case when Measures.HierarchyLevels>2 then dimensions(measures.MyHierarchyNo).levels(2).name else null end
member Measures.Level3Name as
case when Measures.HierarchyLevels>3 then dimensions(measures.MyHierarchyNo).levels(3).name else null end
member Measures.Level4Name as
case when Measures.HierarchyLevels>4 then dimensions(measures.MyHierarchyNo).levels(4).name else null end
member Measures.Level5Name as
case when Measures.HierarchyLevels>5 then dimensions(measures.MyHierarchyNo).levels(5).name else null end
member Measures.Level6Name as
case when Measures.HierarchyLevels>6 then dimensions(measures.MyHierarchyNo).levels(6).name else null end
member Measures.Level7Name as
case when Measures.HierarchyLevels>7 then dimensions(measures.MyHierarchyNo).levels(7).name else null end
member Measures.Level8Name as
case when Measures.HierarchyLevels>8 then dimensions(measures.MyHierarchyNo).levels(8).name else null end
SELECT NON EMPTY
{measures.DimensionName
,Measures.MyHierarchy
,Measures.HierarchyLevels
,Measures.Level1Name
,Measures.Level2Name
,Measures.Level3Name
,Measures.Level4Name
,Measures.Level5Name
,Measures.Level6Name
,Measures.Level7Name
,Measures.Level8Name
} ON Columns,
HierarchySet on Rows
FROM EasternMining

Just replace the Date hierarchy name with a hierarchy from your own cube. This hierarchy is used to list out the attributes, so just ensure there are at least a few hundred members. These members are used to drive the query.

Not sure when this would be useful. Perhaps if you only had browser access to a cube, it did not support DMVs, and you needed to see hidden attributes.

Easy Tuning Options for SQL Server OLTP

2010-06-28T07:27:00.008+10:00

Even though I have specialised in business intelligence applications, I still help customers with OLTP performance issues. Here are a couple of very simple option that can make quite a difference without changing any code.

Turn on asynchronous statistics collection. This can be a great help where there are locking (and/or deadlocking) because you want to reduce the duration of transactions. The probability of deadlock tends to be exponentially proportional to the transaction duration. Asynchronous statistics collection will enable your transactions to continue to execute without having to wait for the statistics gathering. By the way, if you really want to manage statistics in a critically high tp system, you might want to disable autostats and run them on a schedule off peak. This option is set for the server on the advanced properties tab.

Disable query decomposition. In an OLTP system, you really don't want one query to break up into many parallel tasks consuming more resources. The property is "Max Degree of Parallelism" and is set by database.

Of course there are a million things that you can do differently in your application to make it perform better, but I would need to write a book to cover the essentials. Also, hardware is always an option to improve performance, but it will only improve performance if it is hardware that is a critical resource.

For real time BI and data mining demonstrations see http://RichardLees.com.au/Sites/Demonstrations

Solid State Disks and IO Performance

2010-05-06T19:12:00.004+10:00

I just read a non bulls**t whitepaper from EMC's site about disk performance and solid state disks for the enterprise. They are calling the disks EFD (Enterprise Flash Drives). I have tired of reading storage vendors's whitepapers that dodge real IO bottleneck performance issues. This paper is very informative and discusses IO throughput, which, in my experience, tends to be one of the most limiting factors in hardware performance.

Download from emc.com

Or download my copy from here.

I have always known that solid state disks have faster IO than magnetic disks, but was not sure about their IO throughput. This paper has a performance test (conducted by Microsoft's SQLCAT team) and they found over 1000% throughput improvement by moving to solid state disks. One solid state disk could sustain over 2500 IOs per second! This is great news for any IO bound database server.

The whitepaper also discusses SQL Server's system checkpoint the implication on the log, if you didn't know already.

I suggest the whitepaper is a must read for any DBA interested in system performance.

Improving Excel's Cube Performance

2010-04-16T11:14:00.016+10:00

I am often asked to help with Excel's query performance. The cube is fast and efficient, but some Excel queries are slow due to the MDX that Excel is generating.

This is unfortunate, but there a couple of things you can do about it. Let me describe this particular issue. Essentially, you have a dimension with a large number of attributes. In Excel, you have crossjoined many of these attributes on rows, which you feel is reasonable since you have also added a filter so that the query will only return a small number of rows. For example, here is a query of the FoodmartFull database, where I have filtered on just one customer surname (Peacock) and asked for many customer attributes on rows. This query takes 9 seconds for SQL Server 2008 to execute. The reason it takes so long is that the MDX is asking for a crossjoin of all the attributes, at all levels. Note, this issue can be so bad that the results are not returned before timeout.

I have traced the MDX using SQL Profiler. See the MDX in the picture to the right. (I have formatted the MDX. Wouldn't it be a nice thing if SQL EM would format SQL/MDX for us? That's a topic for another blog.) If you execute this MDX, you will receive 3840 rows! That is a huge number, when you consider there is only one customer and Excel is only displaying 1 row. (I asked for a tabular format. But even if I ask for a compact format, I only get 14 rows.) Understandably, this MDX takes 14 seconds to execute.

The reason I chose this particular query is that all of the attributes you see in the Excel report are from the same dimension. Having all these attributes from the same dimension should be very easy for Analysis Services to return quickly, since it can determine, from the dimension, exactly which Customer cells/aggregations it needs.

I can edit the MDX to simply ask for the crossjoin of the leaf members, which will execute in just 1 second. This is much easier for Analysis Services since we are only asking for asingle row rather than 3840. See the second query.

Of course it is not possible to edit Excel's MDX to make the query faster, but we do have other tricks we can employ. Since the problem is that Excel is inefficiently crossjoin-ing all of the attribute hierarchy sets, we can help Excel by creating a "whopper" user hierarchy that includes all (or many) the attributes we want in our report. If we had a Customer hierarchy with these attributes, Excel wouldn't need to ask for any (or so many) crossjoins and the query will be sub second. Of course, I am not suggesting that you create "whopper" hierarchies all over the place, but it is a consideration. Also note, you do not need to create one "whopper" hierarchy, just a couple of multi-attribute hierarchies might do the trick. This is because the cost of adding (or reducing) the number of hierarchies has an exponential effect on query performance. Each attribute you add will crossjoin the new set (it is not a single member set) with all other rows.

Another, much cleaner, option, if possible, is to simply show properties of existing members, rather than ask for a crossjoin. I say, if possible, as this is only possible if the parent attribute is in the report. Naturally, this is much more efficient, as the generated MDX is simply asking for the member properties of existing members (crossjoin not required).

Whenever there is an Excel performance issue, I would encourage you to trace the MDX using Profiler. Even though you can't directly change Excel's MDX, it will help you understand the problem and give you some ideas to work around it.

For a list of Excel PivotTable tips see http://richardlees.blogspot.com/2009/10/excel-2007-olap-pivottable-tips.html

MDX - Group by measure range dynamically

2010-03-29T19:48:00.011+11:00

Here is a similar MDX puzzle. The aim is to aggregate members by the range their measure falls into. For example, aggregate cities into ranges (multiples of 1000) of the number of hits.
Of course, you can hand craft the MDX to define each range, but that's not elegant, scalable or flexible. How about some MDX that dynamically creates the ranges?

Here is a solution from my Weblogs database. The query is aggregating the number of hits for all cities with hits in the 0 - 999 range, 1000 - 1999 range etc. See how it uses recursive MDX, very similar to the preceding blog, which aggregates by distinct member_caption. Recursive MDX really is very useful.

Here is what the output looks like. Note; ignore the city name, that is just the last city in the range. The first measure column has the range limit, the second column has the number of cities and the third measure has the total hits for that range.

To view the cube I used, see http://RichardLees.com.au/Sites/Demonstrations

with
member measures.MyRange as
cstr(round(Measures.Hits/1000,0)*1000)+"-"+cstr(round(Measures.Hits/1000+1,0)*1000-1)
set MyCities as
Order(nonempty([Client Host].[Client Geography].[City],Measures.Hits)
,Measures.MyRange,BASC)
member measures.r as rank([Client Host].[Client Geography],MyCities)
member measures.RangeTot as iif((MyCities.item(Measures.r-2),Measures.MyRange)=Measures.MyRange,
Measures.Hits+(Measures.RangeTot,MyCities.item(Measures.r-2)),
Measures.Hits)
member Measures.RangeTotal as
iif((MyCities.item(Measures.r),Measures.MyRange)=Measures.MyRange,
null,Measures.RangeTot)
select {Measures.MyRange,Measures.RangeTotal} on 0,
nonempty(MyCities,Measures.RangeTotal) on 1
from EasternMining

MDX - Aggregating by member_caption

2010-03-29T19:26:00.006+11:00

Here is an interesting MDX puzzle. Try aggregating a dimension hierarchy by member captions, where the members have unique keys, either by different hierarchies or just different keys. When you try to sum() a set, you have a tendancy of losing the position of the member on the axis.

Here is a solution to the puzzle using recursive MDX. If the preceding member has the same name, it moves to the preceding member and adds the measure. I have used the client geography dimension in my Weblogs cube. There are many city names that exist in multiple states and/or multiple countries. See 'Springfield' appears in 7 states. The challenge is to get the sum of all distinct city names. I know this is not a likely query, but if it was a customer name that was in many states, it might be a real query.

with
member measures.mc as [Client Host].[Client Geography].member_caption
set MyCities as Order(nonempty([Client Host].[Client Geography].City,Measures.Hits)
,[Client Host].[Client Geography].member_caption,BASC)
member measures.r as rank([Client Host].[Client Geography],MyCities)
member measures.CityTot as iif((MyCities.item(Measures.r-2),Measures.mc)=[Client Host].[Client Geography].member_caption,
Measures.Hits+(Measures.CityTot,MyCities.item(Measures.r-2)),
Measures.Hits)
member Measures.CityTotal as
iif((MyCities.item(Measures.r),Measures.mc)=[Client Host].[Client Geography].member_caption,
null,Measures.CityTot)
select {Measures.CityTotal} on 0,
non empty
MyCities on 1
from EasternMining

You can slice and dice the real data on http://RichardLees.com.au/Sites/Demonstrations

You Too Can Learn About R2

2010-03-03T18:07:00.003+11:00

Microsoft have just released an updated kit for developers learning about R2. If you want to know more about it and/or download a trial version goto http://go.microsoft.com/?linkid=9710868

There are lots of pptx, demos, videos and labs.

Expect more resources shortly, but this is a really good way to get a head start with R2.

Using Profiler to tune a SQL Server data warehouse

2010-02-21T10:22:00.028+11:00

The last blog was on tuning TP applications. How about tuning a DW application? Surely it is quite similar. Well yes, the skills for tuning data warehouses and TP systems overlap. In this blog I will provide some hints for tracing/tuning the data warehouse SQL Server database. This includes any SQL statements (and utilities) on the warehouse database.

A key difference is that we want to trace SQL statements, rather than individual procedure statements. The number of statements tends to be an order of magnitude less, so we can (and should) trace for a longer period, 24 hours, if possible. But at least spanning your main load process. It is still important to include a row limit, as some performance issues can manifest as iterative SQL calls. Such as INSERT statements when you are expecting BULK INSERT, or iterative UPDATE statements for the SCD SSIS task.

There is a complication. That is, some statements might be dynamically compiled and executed, in which case the trace will group by the meaningless name of the procedure and parameters. To ensure that we have the prepare statements (that's where the SQL query is) we will clear the plan cache at the start of the Profiler trace. Then we will be able to find the prepare statement for the procedure by searching earlier records on the same SPID.

To trace all SQL requests

Start Profiler, as in the previous blog, clear all events, and only include RPC:Completed from Stored Procedures and SQL:BatchCompleted from TSQL. This will capture all SQL requests to SQL Server.
Select the same columns as the previous blog.
Limit the number of records to 100,000. This should be plenty, since a data warehouse tends to have a small number of large requests.
Execute DBCC FREEPROCCACHE to clear the procedure cache.
Now leave the Profiler trace to go over a long period, perhaps 24 hours. This way you will cover an entire load and process schedule.
Check that the number of trace records is less than your limit. If you hit your limit, find out which query is iteratively called. Ask yourself the question, can this iterative statement be executed more efficiently in batch? If so, change it, if not, filter it out of the trace and start the trace again.
It is worthwhile altering the trace table. Right click on the table, select Design and change ntext to varchar(max), nvarchar() to varchar() and remove any unnecessary columns. You might need to change your designer options on SQL 2008 to allow a table change that involves a drop table. see Can't change SQL table
Some of the SQL requests won't have the actual SQL statement, since it has been prepared (optimised) into a temporary procedure for fast subsequent executions. To help query the results, we will to update the trace table to include a pointer to the prepare statement. To do this, add a column PrepareRowNumber INT to the trace table and execute the following update statement to populate it.

update t
set PrepareRowNumber=(select top 1 RowNumber from t tt where tt.starttime<=t.StartTime and tt.spid=t.spid and tt.TextData like '%sp_prepare%' order by starttime desc) where TextData like '%sp_execute %' update t set PrepareRowNumber= (select top 1 RowNumber from t tt where tt.starttime<=t.StartTime and tt.spid=t.spid and tt.TextData like '%sp_cursorprep%' order by starttime desc) where TextData like '%sp_cursor %'
Now you can execute a query to get the top 50 SQL batches by total resource.
select top 50 *,Duration/Executions Duration_avg, CPU/Executions CPU_avg, Reads/Executions Reads_avg, Writes/Executions Writes_avg, RowCounts/Executions RowCounts_avg from (select t.DatabaseName, t.ApplicationName, tt.TextData PrepStmt, convert(char(100),t.TextData) TextData50, sum(t.Duration)/1000.0 Duration, sum(t.CPU)/1000.0 CPU, sum(t.Reads) Reads, sum(t.Writes) Writes, sum(t.RowCounts) RowCounts, count(*) Executions from t with (nolock) left outer join t tt on tt.RowNumber=t.PrepareRowNumber where t.ObjectName is not null group by t.DatabaseName, t.ApplicationName, convert(char(100),t.TextData),tt.textdata) t order by t.Duration desc
Notice how there is PrepStmt column as well as the textdata. If the textdata contains a sp_execute (or sp_cursor) statement, the PrepStmt column should contain the prepare statement. That was why we cleared the proc cache at the start of the Profiler trace.
Now the fun starts. You can review the SQL Statements and see if there is a way of tuning them.

Note

The above update queries don't use the variable number, which they should do to ensure the right prepare statement is fetched. The top 50 list displays both the execute statement and the prepare statement, so you can eyeball that the correct statement has been selected. If you find that the incorrect statements are being selected, you might want to amend the update statement. An SQL function to parse the text for the parameter number would work a treat.
Sometimes I will include a CASE clause around the textdata, so that I can manually group together statements, that I know about. You should see the above top 50 SQL query as a template, or starting point, from which you can taylor for your Profiler data.
The above steps will also work for a TP application that calls with SQL statements (ie, without stored procedures). However a TP system is likely to have many more overlapping dynamic procedures, so it is more difficult (and more important) to locate the right prepare statement.
You could filter the Profiler trace to only record statements that consume a minimum resource level. However, be careful that you don't ignore repeated statements that individually consume very little resource, but collectively take a long time. A couple of examples of this would be the UPDATE statement on the SSIS SCD task, or a load process that is firing INSERTs.
This profiler trace is simply on the SQL Server workload. Your data warehouse is likely to have Analysis Services processing work and cache warming, which you should also be interested in. Treat this as a separate tuning exercise.

For a real-time data warehouse demonstration see http://RichardLees.com.au/Sites/Demonstrations This site inserts about 300 facts/second into SQL Server and incrementally processes a few OLAP cubes and data mining models. The above trace output was from a 24 hours trace of the SQL Server database server.

Using Profiler to tune a high volume transactional application (written with stored procedures)

2010-02-18T06:09:00.016+11:00

I am a great fan of SQL Server Profiler. I often use it to diagnose performance and functional issues with SQL Server and Analysis Services. There is a classic Profiler task (performance tuning transactional systems) that I would like to share with you. The situation is that you have a high volume transactional SQL Server application written with stored procedures. You want to identify the statements that are consuming the most resources, as tuning these statements will have the biggest impact on the system.

You are not looking for the slowest statement, since it is not necessarily noticeable from a system point of view. From a system point of view, a statement executing 1000 times per minute taking 200ms is more interesting than a statement executing 1 time per minute taking 10 seconds.

The Profiler trace for this task is quite simple.

Start SQL Server Profiler
Start a new trace with your SQL Server instance as the source
Check the Save to table option and provide a location for the trace table. I have called the trace table t, but you can use any name.
Check the Set maximum rows and enter a number <=100000
In the Events Selection tab, uncheck all events. This window is a bit annoying, since you can't resize it.
Check the SP:Statement Completed event under Stored Procedures. This is the only event you will need. To see it, you might need to check the Show all events box.
Check the Show all columns box, and select the columns you want. At this point you only really need databasename, objectname, textdata, duration, cpu, reads, writes, linenumber, rowcounts and SPID. However, applicationname, NTUsername and endtime can be useful too.
Click Run
You will be able to see how many records are being collected, and can stop the trace manually at any time.
Once the trace has stopped (or you have stopped it) run the following query to get the top 50 statements by their combined duration.

select top 50 *,Duration/Executions Duration_avg, CPU/Executions CPU_avg, Reads/Executions Reads_avg, Writes/Executions Writes_avg, RowCounts/Executions RowCounts_avg from (select DatabaseName, objectname, convert(char(100),TextData) TextData50, sum(Duration)/1000.0 Duration,sum(CPU)/1000.0 CPU,sum(Reads) Reads, sum(Writes) Writes, sum(RowCounts) RowCounts, count(*) Executions from t with (nolock) where ObjectName is not null group by DatabaseName, ObjectName, convert(char(100),TextData) ) t order by Duration desc

Now comes the fun task of tuning the individual queries. Starting with the top query, open up the stored procedure, and look at the particular statement in the context of the procedure. Is there a way to improve this particular statement, or can the structure of the procedure be improved? The answer is invariably Yes, although you want to find improvements that can be introduced with minimal disruption.

A couple of notes

Try to avoid running Profiler on the server itself. That will add more work to the server.
I like to alter the trace table, removing unnecessary columns, changing nvarchar to varchar, ntext to varchar(max) to reduce the size of the table, which will make subsequent queries faster.
You can ask Profiler to group the data, but since you will be aggregating the data in SQL there is no need.
Run the profiler trace during a busy, but not critical, time.
Earlier versions of SQL profiler could make SQL Server unstable, if you collected too many records. This isn't such an issue now, but I encourage you to always limit the number of event records so that you minimise the database server impact.
The overhead on the server is mostly the cost of the Profiler application (that's why you should run the app on a machine that isn't the database server) and inserting the event records into SQL Server (which is why you should target a non production server for the trace records)
On a very busy system, where you do not want to trace every statement, try a filter on reads. For example, you might only record events with more than 10 reads (these aren't physical reads). The logic being that a statement looking at less than n pages, is unlikely to be a big resource consumer.
Start the exercise with a small record limit, and look at the results. You might want to add some additional filters to exclude/include certain application components.
Keep a summary of the trace output. After you have tuned the top queries, you will be able to measure the improvements. I don't think you need to keep the atomic trace records.
Profiler will show duration in milliseconds, althought it is saving the data in microseconds. The trace table will have duration in microseconds. Don't be confused.
I will often not ask Profiler to save to a trace table while tracing. Once I have the records I want, I then ask Profiler to save to a trace table.
If you are going to repeat this exercise, you should save the trace template. That will help you avoid defining the events, columns and filters again.
The query above orders by duration. You might also like to order by reads, cpu or writes. This would be particularly appropriate where your system has parallelism enabled. With parallelism a query with a 500ms duration might be consuming 4 seconds cpu. Having said that, in a high volume transaction system, it is generally not appropriate to have parallelism enabled. In a tp system, I would tend to disable parallelism by default, and turn it on for individual queries that need it, but that's another blog.
This exercise is just looking at stored procedure statements. This trace exercise will not include statements that are not part of a procedure.

Also note, the Profiler can trace so much information. The more you know about Profiler, the more it will help you diagnosing performance and functional issues. This exercise is just one very narrow performance task Profiler can help you with.

SSIS Slowly Changing Dimension SCD Wizard is Too Slow

2010-02-15T19:43:00.004+11:00

Pre SQL Server 2005, we had to write our own scripts for managing slowly changing dimensions (aka Type 2 dimensions). Since SQL Server 2005 we now have an easy to use SSIS wizard to maintain SCD. Unfortunately, the SSIS wizard generates an OLEDB update statement that is executed once for every record. Naturally the iterative statement is very slow with large volumes.

Hence if you have large volumes of data and need to have high performing SCD updates, you will need to use a batch update statement. This is not all that difficult. I would even suggest that if you can't write the SQL, you might not the be the best person to design a DW.

Essentially, you need to "close off" the records that are getting replaced, and "insert" all new records. The "closing" of records can be done in a single UPDATE statement as shown in the query below. Note, the exact script you need will depend on your SCD and non-SCD attributes and the format of your source data. But essentially, you are updating all records in the DW where there exists a newer record. If you have some Type 1 attributes, will need another update statement for them.

The second task is simply to insert the new (and updated) records. You should do this in an SSIS data flow task. The select statement in the image below, is an example of what the query will look like to feed that load. Note, your query will be a little different depending on how you identify changes, but the query below will give you a starting template.

By the way, if you really don't want to write the above queries (or prefer the elegance of a component) there are 3rd party SSIS components that manage SCD with set based operations.

Of course, I am sure a future version of SQL Server will have an SCD wizard that works with batch update commands. Just not sure which one.

I like PerformancePoint Dashboards

2010-02-14T19:56:00.006+11:00

PerformancePoint (PPS) dashboards really are easy to create once you have an OLAP cube with useful information. One of my cubes, Weblogs, is continually updating with all the web activity on my site RichardLees.com.au. In a couple of days, two and a half years ago, I built some PPS dashboards as a public demonstration. I still find them very interesting and a useful guide for activity and performance on my site.

You can see the same information I can. The picture on the right, is a dashboard pointing at the weblogs cube. To get to it, just click on the Web Analytics dashboard on the left hand menu of http://RichardLees.com.au/sites/demonstrations. Then click on the Web Performance tab on the top of the dashboard. The scorecard, is asking for the last 6 days on columns and the top 20 http resource types on rows. It will continually adapt to the date and web activity. It doesn't end there, you can click on any resource type (in the picture, I have clicked on .aspx) so that the 3 right hand charts will dynamically filter to this resource type. At a glance I can see which particular resources are most active and their response time trends. From the bottom chart, it would appear that most of the .aspx resources have been progressively slowing down over the last 12 months. This might be due to the increased activity, database size growing, or perhaps my web site is competing for internet bandwidth with my boys web downloads. I'll investigate further.

The point I would like to make, is that once you have useful information in a cube, it is really quite a simple job to create some dynamic and interesting dashboards. If you are heading towards dashboards, I would encourage you to start by very loosely defining what you want on the dashboard. You only need to determine what information your cube requires. Then once, you have the cube, you can be creative with the dashboards to see how to maximise the information you have available. In my experience, what tends to happen is that you will start with a generic dashboard, then, with feedback, make more dynamic functionality so that users are able to easily drill down to the interesting aspects.

There is really only one gotcha that stands out for me and that is the client browser. Internet Explorer 7 and 8, obviously, are very good clients. Other browsers, don't support all PPS dashboard functionality. Of course, if you have a particular browser in question, you could browse to my demonstration and see how well it does. Feel free to post comments on this blog with your results.

By the way, I have similar beliefs about SQL Server Reporting Services. Once you have a cube with all the information, dynamic reporting is quite simple. Also, Reporting Services and PerformancePoint should be seen as complementary, rather than alternatives.

Aggregations Won't Fix Cube Performance

2010-01-30T06:29:00.013+11:00

There appears to be a temptation for SQL sales people to demonstrate the aggregation wizard with the impression that it is the tool to solve all performance issues. Unfortunately, it isn't, and I need to dispel that myth.

The aggregation design wizard is not a performance panacea, but it can help. It's like tire pressure in a racing car. The optimal pressure will help performance, but it won't turn a slow car into a fast car.

Let's think about what the aggregation wizard does. The base cube cells only contain values at the intersection of all dimension keys. When a query asks for data at a higher level, AS must derive this data from the base cells by aggregating them. The aggregation wizard determines which of these aggregations to calculate at cube processing time and store on disk. So designing aggregations will impact your cube processing time. The more aggregations you have the longer the cube processing time, and the larger the cube on disk. At query time, if AS does not have the pre-aggregated cell on disk, it will get the lower level cells (these might be aggregations too) and calculate the aggregated cell. This new aggregation will reside in AS memory like any other aggregation or base cell. So there's the rub, even if you don't have any aggregations, AS will create them at query time (or cache warming) and try and keep them in memory for subsequent queries. So, if AS is not memory constrained, aggregations will only help the queries while the cache is cold. In essence, a cube warming query (CREATE CACHE) will have a similar effect as aggregation designs for a non-memory constrained cube.

Cubes that benefit the most from aggregation design are ones that are updated frequently (for example, every minute or so during busy times) and are larger than the memory available to the AS data cache. Note, when a cube partition is processed, all the cells and aggregations in memory are dropped.

Another important note; by default, the aggregation wizard will only create aggregations on key attributes and attributes in natural hierarchies. For example, if you have a Date dimension with date as the key, the aggregation wizard by default, will only build aggregations on "date" and natural hierarchies. If these other attributes are commonly used and you would like the aggregation wizard to consider them, you can ask for it in the Aggregation Usage window (or properties in the cube design). The "Default" usage for a key is "Unrestricted", while the "Default" usage for a non-key property is "None". However, don't see this as a silver bullet, by enabling all properties as "Unrestricted"! That will only result in very thin aggregation design. Also, aggregations on keys, tend to be the most important ones, so the default options suit most cubes.

When using "Distinct Count" measure in a cube you need to be even more careful with aggregations, since each aggregation (and base cell) will include every distinct value of the Distinct Count. This can make aggregations very large and costly to build/query.

For very small non volatile cubes such as the New Zealand census cube http://EasternMining.com.au/sites/Demonstrations/Pages/NZCensus_Excel.aspx there is no benefit from aggregations, which is why that cube has no aggregations.

For really good OLAP cube performance you should ensure that the design is optimal, and use aggregation design (or usage based aggregation design) as a final operational tuning exercise.

Are Temp Tables Bad?

2010-01-15T20:36:00.009+11:00

I have often said that temp tables are the hallmark of sloppy SQL programmers. Why can't these developers write correlated subqueries with outer joins without writing out intermediate tables? I even had a little wager for a bottle of whisky with Ron Soukup about the merits of temp tables just after I joined Microsoft in 1995. That was before I knew who Ron was. I was to show that a query could be faster without temp tables, but the old SQL Server 6.5 optimiser was didn't make it easy for me. Essentially, if you are using a temp table, you are saying that you can do it better than the database optimiser. Or in other words, the optimiser has room for improvement.

However, a colleague has brought to my attention a query that reliably runs faster with a temp table, and there isn't much I can do about it.

It would appear that SQL Server's optimiser uses the estimated rowcount when setting up/executing a sort. So that if the estimated rowcount is much higher than the actual, the sort becomes slightly more expensive than the same sort with an accurate estimation. This is quite intuitive to me, since a very large sort might use a different algorithm than a very small sort. (In the old mainframe days we always had to tell the sort program how many records it was going to sort.)

In my query without a temp table, SQL Server estimates 26,000 rows will be sorted, while the actual number of rows to be sorted is 36. Of course the sort after the temp table quite accurately estimates 36 rows. So, by inserting the 36 rows into a temp table before sorting, the optimiser will know quite accurately how many records it will sort. To further support this hypothesis, I included a "top 36" in the query without a temp table, and low and behold, it estimated 36 rows in the sort and ran faster (in the same time as the temp table query).

I feel that this performance effect is more of a curiosity than a valid justification to use temp tables in queries with Order By clauses. Also, it is very likely that in a future version of SQL Server the Optimiser team will enhance the product to dynamically determine what sort will be employed during query execution. Then the temp table query will be slower.

The original query was from a customer database, but here is an equivalent query that I have written on my home Perfmon database. If you want to reproduce the effect, the key is to have a query that estimates a very large number of rows in the sort, while the actual number is very small.

The picture shows the execution plan for the two queries. The first query (no temp table) took 53% of the total resources, while the second and third queries (with temp table) only used 47% of the total resources. This was further evidenced from the temp table queries using 10% less cpu than the original query. 328 ms of cpu versus 297 ms for the temp table queries.

DECLARE @date DATETIME
DECLARE @value real
select @date = '2010-01-15 15:50:00', @value = 700
--select * from (
select --top 53
MAX(CounterDateTime) CounterDateTime,
SUM(CounterID) CounterID,
CounterValue CounterValue
FROM CounterDatafacts with (nolock)
WHERE CounterValue between @value and @value+2
and CounterDateTime between @date and DATEADD(hh,1,@date)
Group By CounterValue --) as x
ORDER BY CounterDateTime
--Same query query using temp table
select MAX(CounterDateTime) CounterDateTime,
SUM(CounterID) CounterID,
CounterValue CounterValue
INTO #TEMP
FROM CounterDatafacts with (nolock)
WHERE CounterValue between @value and @value+2
and CounterDateTime between @date and DATEADD(hh,1,@date)
Group By CounterValue
SELECT * FROM #TEMP ORDER BY CounterDateTime
DROP TABLE #Temp

The Transaction Manager is not available

2010-01-15T05:57:00.006+11:00

A few things you need to know about SSIS TransactionOption "Required".

By default, SSIS packages do not require transactions, and will not coordinate transactions. That is because the default TransactionOption is "Supported". Supported means that if this package (or subordinate object) is called from another object that has TransactionOption "Required" then it will be part of the transaction. However, if you select "Required" as the package TransactionOption (or task that consumes multiple connections, such as a sequence container) then SSIS will use DTC to manage the transactions across multiple connections. This may not be immediately intuitive to you since you might only connect to a single SQL Server. However, with multiple connections, the only way SSIS can ensure they are all committed, or none are committed, is to use DTC (Distributed Transaction Coordinator).

If the SSIS package is running on the same server as the only SQL Server database, then it is quite simple, you only need to have DTC service running. However, if the SSIS package is running on a different machine to SQL Server (this includes running SSIS in dev mode locally and attaching to another SQL machine) then you will need to ensure that DTC can communicate across machines. Otherwise, you might get a message like these

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

These error messages are, in essence, telling you that DTC cannot communicate with SQL Server. Here's what you need to check
Ensure both computers can ping each other using netbios names. Netbios names must be used for DTC transactions.
Both computers must have MSDTC running
If there are firewalls on either machine, they must have exceptions for Port 135 or for the program msdtc.exe.

The dtcPing tool can be helpful in diagnosing DTC issues. For more information see http://support.microsoft.com/kb/918331

Now, whether or not you want to use large scope transactions is another question, and not a simple one to answer. A couple of points to think about when you are considering using transactions are

Any update, insert etc to a rdmbs is either completed successfully or rolled back. Using TransactionOption "Required" as I discuss above is about combining many update/insert/delete statements so that they all succeed or none of them do.
Be mindful of how long the transaction runs for. This is the time that another transaction might have to wait if there is a locking issue.
There might be other ways to ensure your data integrity. For example, if you are thinking about transactions to enable simple re-running of the package in the event of an error, you might be able to structure the package so that it can re-run even after successful completion of some, or all, of the tasks.

Another point worth noting with SSIS transactions is that you can set TransactionOption of "Required" on the package, and have a task (such as Execute SQL) which has a TransactionOption of NotSupported. In this way, so long as the tasks executes, it will not be rolled back with the rest of the package in the event of a failure.

For a real-time demonstration of data that has SSIS packages loading/processing about 300 new records/second (all day, every day) see http://RichardLees.com.au/sites/Demonstrations

Circular Relationships

2010-01-11T19:42:00.005+11:00

I am occasionally reviewing database designs and one of the things I like to check is normalisation. One indication that a table structure isn't in third normal form is the existence of circular relationships. That is where you can navigate from one entity back to itself via circular relationships. I should stress that the existance of a circular relationship does not necessarily mean that we aren't in 3rd normal form, but it is a good clue.

Here is an example in the picture of a circular relationship. From the Products table you can get to ProductType directly or via ProductGroup.

To search for circular relationships that are declared by referential constraints, you can use the following SQL query. (Note, it is only looking for a three level relationship, and it could be enhanced to find deeper redundant relationships.)

select
OBJECT_NAME(fk.parent_object_id) Child
,OBJECT_NAME(fk.referenced_object_id) Parent
,OBJECT_NAME(fk1.referenced_object_id) Intermedidate
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.parent_object_id and c.column_id=fkc.parent_column_id
where fkc.constraint_object_id=fk.object_id) FK_FirstColumn_FirstRelationship
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.referenced_object_id and c.column_id=fkc.referenced_column_id
where fkc.constraint_object_id=fk.object_id) PK_FirstColumn_FirstRelationship
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.parent_object_id and c.column_id=fkc.parent_column_id
where fkc.constraint_object_id=fk1.object_id) FK_FirstColumn_IntermediateRelationship1
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.referenced_object_id and c.column_id=fkc.referenced_column_id
where fkc.constraint_object_id=fk1.object_id) PK_FirstColumn_IntermediateRelationship1
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.parent_object_id and c.column_id=fkc.parent_column_id
where fkc.constraint_object_id=fk2.object_id) FK_FirstColumn_IntermediateRelationship2
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.referenced_object_id and c.column_id=fkc.referenced_column_id
where fkc.constraint_object_id=fk2.object_id) PK_FirstColumn_IntermediateRelationship2
from sys.foreign_keys fk
inner join sys.foreign_keys fk1 on fk1.parent_object_id=fk.parent_object_id
inner join sys.foreign_keys fk2 on fk2.parent_object_id=fk1.referenced_object_id
and fk2.referenced_object_id=fk.referenced_object_id

Remember, there is a lot of information in the relational catalog. We should use when we want need information about our data structures.

Get Active and Eat Well

2010-01-04T08:03:00.006+11:00

It's summer holiday season in Australia and I have been spending more time with the family, beach and unicyle. That's why I haven't blogged for 3 weeks.

My first blog for 2010 is to remind everyone that we need to keep active and eat well. I have had too many friends that have become software developers at the expense of social interaction, exercise and good eating. That was me for the first 5 years I worked at Microsoft. Long work hours combined with junk food and little activity. I have since learnt that you are a much more inovative developer if you keep a balance between work, family, exercise and social interaction. The good news is that you can recover, I did.

Yesterday, my second son, Sven, helped me create a video of my typical unicycle jaunt around Sydney's North Head. Take a peak at http://www.youtube.com/watch?v=FpNslE5J3tI

Stay active and eat well.

When to use clustered indexes

2009-11-26T19:25:00.003+11:00

I often found experienced database designers, new to SQL Server, will come with preconceived ideas about the optimiser and storage engine. One of these ideas is that clustered indexes should be used sparingly, since they come at a cost with limited benefit. With SQL Server, this is by and large untrue, and as a general rule, most tables will be better off with one of the indexes being clustered.

SQL Server (and Sybase) have historically had a structurally different way of maintaining clustered indexes. They actually use the index leaf pages to hold the data columns, which enables them to save space, reduce the number of pages touched in an index path, and use them to very efficiently sequence scan.

There is a useful performance analysis of clustered versus non-clustered PK index on http://technet.microsoft.com/en-us/library/cc917672.aspx This paper looks primarily at the cost and throughput of inserts, updates and deletes. By and large, the clustered indexes performed better. There is one significant exception, where there are high volume concurrent inserts, with keys in close proximity. In this situation, concurrent can compete for locks and latches with other active transactions with similar keys. By the way, this does not necessarily mean that you shouldn't use a clustered index for the table, rather, it might mean your clustered index should be on a key that won't create hot spots. For example tbTransaction might have contention with TransactionID clustered, but a clustered index on AccountID would be less contentious and might even be better serving queries.

One area that this paper doesn't look at, and in my view, is one of the most important reasons for clustering, is queries that join very large tables. If you are joining two or more very large tables, the optimiser will like to have the opportunity to Merge Join the larger tables. For example, a query that was to join tbTransaction with tbTransactionCharges and many other smaller tables is likely to benefit from having both large tables clustered in the same sequence. This could be TransactionId, or, perhaps more likely, AccountID. If queries on these two tables were often filtered by particular accounts (or tbAccount columns) then having all 3 tables clustered by AccountID (and also TransactionID or TransactionDate for the transaction tables) is likely to provide very good join performance.

We are very fortunate with relational databases, in that we can change the index structure without having to rewrite any application queries. However, I would encourage database designers to analyse in some detail the activity on the biggest tables, and design appropriate clustered indexes early on. Clustereed indexes are much more difficult to change at a later date than non clustered indexes. Also, for those smaller tables, make one of the indexes (typically the natural primary key) clustered.

For a real-time demonstration of a database application continuously processing about 300 records/sec see http://RichardLees.com.au/Sites/Demonstrations The Perfmon application is close to real-time and is processing about 300 perfmon records all day, every day, on commodity 32 bit (yes I really should upgrade) hardware.

How to Avoid SQL Reserved Words

2009-11-24T16:47:00.007+11:00

Have you ever been querying a database and received a syntax error because one of your column, table or some other object name is clashing with an SQL reserved word? If so, here is an easy way to avoid reserved words during your development cycle. In simple terms it joins a list of reserved words with your catalog columns.

Simply download the ReservedWords.csv from http://richardlees.com.au:8080/dropzone/SQLReservedWords.csv Or if you prefer, copy the words from SQL Server books online and load them into a table.
Load the words into this table
create table ReservedWords (ReservedWord varchar(128), ReserveWordList varchar(128))
Run the following query

select o.name [Object Name],c.name [Column Name], w.ReserveWordList
from sys.columns c
inner join coreddsdevtemp..ReservedWords w on w.ReservedWord=c.[name]
inner join sys.objects o on c.object_id=o.object_id and o.type NOT IN ('S','IT')
order by 1,2

You will get a list of all the objects that have column names on the SQL 2008, ODBC, and SQL future reserved lists. By the way, you might want to filter out the ODBC words if they are not appropriate for you.

I should add that it isn't fatal to have reserved words in your columns. It just means that you might have to use delimiters around your column names.

MDX is a redundant language

2009-11-21T08:02:00.009+11:00

It is often said that SQL is a redundant language, since all but the most simple queries can be written many different ways. Well, I find MDX much more redundant than SQL. There really are thousands of ways to express the same logical query. I like this, since it means that there are lots of opportunities to tune the query by writing it in a different way. Just like SQL. As an MDX writer, I encourage you to strengthen your ability to write queries in different (redundant) ways. This will make you more powerful in query writing and give you more performance opportunities.

Here is a simple example, which involves one of the most common "brick walls" hit by new MDX writers. You want to write a query that would be the logical equivalent of

columnx IN ('Australia','New Zealand'). Here is one way to write this in MDX

with member [Client Host].[Client Geography].ANZ as
sum({[Client Host].[Client Geography].[Region].&[Asia].[AUSTRALIA],
[Client Host].[Client Geography].[Region].&[Asia].[NEW ZEALAND]})
select
{measures.hits,measures.sessions,Measures.[bytes total]} on 0,
tail([Date].[Year Month Day].[Month],12) on 1
from [EasternMining]
where [Client Host].[Client Geography].ANZ

Here is another way to write exactly the same query

select
{measures.hits,measures.sessions,Measures.[bytes total]} on 0,
tail([Date].[Year Month Day].[Month],12) on 1
from
(select {[Client Host].[Client Geography].[Region].&[Asia].[AUSTRALIA],
[Client Host].[Client Geography].[Region].&[Asia].[NEW ZEALAND]} on 0
from [EasternMining])

Of course there are many other ways of expressing this same query. I would not suggest that you should use one technique over the other. It really depends on which one is easier to read, easier to maintain, and runs faster.

For online real-time OLAP and data mining demonstrations goto http://RichardLees.com.au/Sites/Demonstrations

Optimizing Basket Analysis

2009-11-07T16:09:00.011+11:00

Basket Analysis is the analysis of sales by basket (or transaction). It can be useful in understanding questions such as

What is the total value of sales for transactions that include products x (and y, and z...)?
What is the average transaction value for transactions that include products x (and y, and z...)?
What proportion of transactions include products x (or y or z)? And what proportion of transactions that include products a (and b, and c...) also include products x (and y, and z...)?
How did the attach rate of product x change during the sales promotion series? And was there a lasting effect.
Any analysis that requires mix of products within transactions.

Traditionally basket analysis has been achieved by running SQL queries on very large, highly scalable, databases that dynamically summarize by transaction id. You can imagine that these queries involve huge numbers of transaction and item records and really enjoy massively parallel computers. However, it is now possible to run quite effective basket analysis systems using low cost commodity hardware and Microsoft OLAP cubes. Many people do this by creating a cube with a dimension on the transaction id. If you are considering creating such a solution, I have two major suggestions. This has helped me create basket analysis solutions that consume hundreds of millions of transactions in cubes that support relatively fast ad hoc queries (sub 30 seconds).

Rather than creating a dimension on transaction id, create a dimension on basket id. A basket is defined by the unique combination of items. So all the transactions that include items m, n, o and p, would be considered the same basket. Essentially, the basket id can be used as a dimension like the transaction id, but the basket it dimension is a fraction of the size. This makes the solution much more scalable and faster. A basket id dimension table can be created quite simply by creating a bit string with one bit representing each item. For example an 8000 byte string will support 64000 items. There is a little bit of math to create the bit string. but essentially each ItemID can create an exponential of 2 to get its position in the string. ie
POWER(convert(bigint,2),ItemID). Of course, a bigint can only support 2 to the power of 64, so you will need to do this by a series of 8 byte strings. This would be very very tedious SQL to write, but I found that it is quite easy to write a little SQL to generate the full SQL. Also note, there is no need to keep the full 8000 bytes, it can be a varbinary to the right most byte with an item. See below.

The second major suggestion I have, is to create a dimension using an identity column from the basket id table (there is no need to have the varbinary(8000) column in the cube, and write calculated measures for the basket analysis. ie. I would resist using the distinct count physical measures in the cube. You really need a Basket dimension, so that you can identify specific combinations of items.

The result is a relatively small cube, with a relatively large basket id dimension. The cube will have two measure groups, one for transactions with transaction count and sale value, while the item measure group will have item sales value and item count measures. Here is the short SQL query that will generate the full query for the basket ids. It is only a few lines of SQL, but it will generate over 1100 lines of SQL that will form the query for calculating the unique basket id. You will want to run this query incrementally, so just put a where clause at the bottom to ensure it is over the high water mark. I would also encourage you to put the query in an SSIS package with an OLEDB call to a procedure to look up the identity column on the basket table, so that can be stored with the transaction. The rest, including building the cube, should be easy.

A complementary application to this basket analysis cube is a data mining model targeted at the relationships between sales items and useful for predicting sales items.

If you are having performance issues with your basket analysis solutions or want help with your application, don't hesitate to ask me to help. This is the type of BI activity I really enjoy.

For real-time demonstrations of OLAP, data mining and related BI technologies see http://EasternMining.com.au/Sites/Demonstrations

Excel 2007 OLAP PivotTable Tips

2009-10-17T13:58:00.016+11:00

I am a great fan of Excel as an ad hoc cube browser, even though it lacks a few features I would like to see. Many people don't appreciate some of the functionality available within Excel. Here are a few of my favourite features.

Right click somewhere in the pivottable and select PivotTable Options. Get familiar with all the options. My favorites are Refresh data when opening the file and disabling row/column totals.

Use conditional formatting within the cube. Excel, will honor these cell formatting options by default.

Use Excel's conditional formatting on top of number formatting. Excel will hold the conditional formatting until you change what's on rows.

When you have a cube with multiple measure groups (don't they all now?) use the Show fields related to: to filter measures and dimensions pertinent to your query.

Use the Design tab to remove subtotals by row or columns. Removing sub totals here, will be preserved when you continue pivoting, whereas removing sub totals from rows/columns will not persist.

Also use the Design tab to insert banded rows (or columns)

Right click on the Row or Column header to request sort by measure columns (ascending or descending) or even some other column value.

Right click on the Row or Column header to request Top 10 list. Note, this is really a Top n list, as you can determine how many rows/columns to display. Particularly useful when you have too many columns (rows) and you just want to show the top n.

Right click on the Row or Column header and use the Show/Hide Fields option to remove hierarchy levels from the display. For example, you may want to show cities, without the higher levels of state, country and region.

Right click on the Row or Column header to display member properties. Particularly useful for properties such as phone number

If there are additional actions set up in the cube (a real drill through, for example) right click on the cell (or member) to trigger an Additional action. I find this particularly useful when you want to drill through to a report that shows all the underlying facts, with all their associated details. It can also be useful to take you to an independent application that takes some information from where you are in the cube. For example, Google search (or Bing.com) for a member name.

Right click on a bunch of rows (or columns) to Group them together, as one member.

Get your cube designer to create useful sets within appropriate dimensions. You can drag a set onto rows/columns. For example, Last 10 weeks, or New Customers etc.

If you have a large cube (or poorly designed one) that is slow to query, then check the Defer Layout Update box on the bottom of the field list. In this way, you can design your report without waiting for the data to display. Just click on the Update button when you are ready.

If your query is taking too long, just click on your Esc button, and the query will be cancelled immediately.

In the Options tab, select Offline OLAP under OLAP Tools, to save a copy (or subset) of your cube locally. Note, this will only be available if you have been granted local copy permission. Can be really useful, if you want to take your laptop away and analyse the data.

For the very advanced user, in the Options tab, select Convert to formulas from the OLAP tools button. This will convert your entire pivottable to individual cell formulas of CUBEMEMBER and CUBEVALUE. From this point you will be able to create a report with full control on what is in every cell of the report. It really is quite powerful, although you lose the flexibility of a pivottable. A hybrid option I like is to keep the filters from the pivottable and have a report using the CUBEMEMBER and CUBEVALUE formulas, which gives you a bit of the best from each.
To remove a row or column member, right click and select Filter, then Hide Selected Items (or Keep Selected Items). This is much easier than navigating through a selection list and checking the items you want (don't want).

Of course, there is a lot more functionality within Excel OLAP pivottable than I have highlighted above, but you can see that there is quite a lot of power hidden within the tool.

For online real-time OLAP, data mining, Excel Services, PerformancePoint demonstrations goto http://RichardLees.com.au/Sites/Demonstrations

What's preventing Excel from being the ubiquitous cube browser

2009-10-11T11:38:00.002+11:00

I have said earlier that Microsoft’s Excel 2007 is not the ubiquitous cube browser that it should be. What’s wrong with it? Well nothing is really wrong with it, but there are several features, that I would expect from a product that is 10 years old. With OLAP services first release 1998, there was an update for Excel PivotTable. As I understand it that first version was largely the result of one great developer (TC). However, since that release, the upgrades have been unimpressive. Excel 2007 is a great improvement on Excel 2003, but it still has a long way to catch up to the sophistication that is available in Analysis Services. Here are a few of the things I would like to see in Excel

Full support for calculated members. They are partially supported, calculated measures are OK, but other calculated members are treated as second class citizens.

Ability to create calculated members that are stored within Excel, and available like any other calculated member for that user.

Support for 3 dimensional queries. Currently, a user can only ask for a two dimensional report. Excel, is inherently a 3 dimensional space when you include worksheets. So I would like to be able to create a PivotTable with a dimension placed on the worksheet axis.

Support for naive users who open up a dimension member list with a million members. For example, ask them if they really want the one million rows (columns) and perhaps defaulting to a top 100 list.

More optimised MDX. Sometimes I despair with the performance of pivottable queries, and when I look at the MDX I see that it could run quickly with more efficient MDX.

Ability to see properties of ancestors

Better integration into Excel spreadsheets. For example, ability to format measures and PivotTable to remember these, even after drill down, nesting etc.

Ability to define sets.

Eposure to some set functionality in a gui fashion. For example, it might allow the user to select a crossjoin on rows with an exception function to exclude some members based on a filter criteria.

Ability to right click on members to “select only” or “deselect”

More control over charts. For example mixing the dimensionality of measures so that one measure is cumulative on series and the other is unrelated to the series.

Don’t get me wrong, even with this wish-list, I am a great fan of Excel PivotTable. As a rich client cube browser, it is still my favourite. It’s just that it isn’t keeping up to Analysis Services, which is truly a market leader in the OLAP field. In a later blog, I will write about my favourite features of Excel pivottable, many of which are not immediately obvious.

For real-time OLAP, Data Mining, Excel Services and related demonstrations goto http://RichardLees.com.au/Sites/Demonstrations

My Calculated Member is slow

2009-09-29T05:11:00.005+10:00

Have you written a calculated member that is performing very slowly, while the underlying physical measures are fast? This could be a common cube query performance issue that has a very quick remedy.

Essentially, AS want to know when to treat this as an empty cell. If you don't specify the non_empty_behaviour, AS will need to physically calculate all potential cells to determine which ones are actually emtpy. With non_empty_behaviour specified, AS can avoid many calculations, since many blocks of cells are known to be empty. The non_empty_behaviour attribute is appropriate for both SQL Server 2005 and SQL Server 2008.

To remedy, all you need to do is specify the non_emtpy_behaviour for the calculated member.

Note, there are logic implications with non_empty_behaviour. With the example in the picture (hits per web session) it is logical that I would want to return null when the sessions measure is null. This would not be the case if I were calculating a YTD measure, since the measure might be null on the current day, but the calculation needs to add many other days that might not be null.

For live OLAP and data mining demonstrations with real data see http://RichardLees.com.au/Sites/Demonstrations

Which cube browser for Microsoft OLAP

2009-09-25T15:01:00.020+10:00

I am often asked which cube browser I recommend for Analysis Services cubes. The answer is typically a few minutes long, since there isn't one ubiquitous cube browser that satisfies everyone. It's a bit like asking someone which vehicle you recommend. It really depends on what the purpose is and what you value. A high performance sports car might be appropriate for some people, but would be inappropriate as a family car or to tow the boat to the coast. For me, the best vehicle is a sports bike, but this would be inappropriate for most road users. The same applies to cube browsers, some users need something very simple and easy, while others need sophistication and flexibility. Having said that if Microsoft Excel had a few more features, it could quite easily be the ubiquitous browser.

Two cube vehicles that complement each other well, unsurprisingly, are Microsoft Excel and SQL Server Reporting Services (RS). Excel is very flexible, while RS can deliver tailored reports, with flexible parameters and easy links to drill down/across to other reports.
In this way, RS is perfect for naive users that need easy to consume information with click-through for more detailed and associated information. Note; RS does not support ad hoc cube browsing. Microsoft also have PerformancePoint, which is a great tool for delivering visual data to a thin client. It has functionality somewhere in-between Excel and RS and many features not available in either. PerformancePoint supports (very rudimentary) ad hoc cube browsing, but it is very powerful at delivering visual and interactive data. So PerformancePoint is ideal for presenting visual data to naive users, and for data savvy users there can be a great deal of “data flexibility”. PerformancePoint can deliver this in a semi structured way, so that users don't drive a cube browser, rather, they drive a dashboard of their data. There is a good example of rich PerformancePoint dashboard on SQLS Live Dashboard. Notice how you can click on scorecard items, and they act as filters on the charts.

Another important cube browser (although not yet truly ad hoc) is Excel Services. Excel Services is essentially an Excel spreadsheet accessed through a thin client, Internet Explorer. Any spreadsheet can be published, even one that is attached to an OLAP cube, so it can be a useful vehicle to deliver live cube data. There are a couple of good Excel Services examples on NZ Census. Notice how you can interact with the spreadsheet by drilling down hierarchies and applying filters. Note too; how the data is live (you might want to switch to the weblogs spreadsheet). However, in the current release of Excel Services you cannot change what is on rows, columns and filters. I have many customers that love Excel Services, since it permits their Finance dept to design and publish reports entirely within Excel, and their report users always see live data.

There are hundreds, if not thousands, of third party cube browsers. They really aren't that hard to develop.
I wrote the first thin client cube browser, ThinSlicer, which is still used by many organisations. When I wrote the first version of ThinSlicer, in 1998, I thought that its shelf life would be short; surely Microsoft will offer a highly functional, low cost, thin client cube browser? How wrong could I be? It is now 10 years on, and the only ad hoc thin client cube browser we have from Microsoft is PerformancePoint, which while it has an ad hoc cube browser, it is not the reason anyone would buy it. An example of a more functional thin client cube browser would be Report Portal. This product supports quite strong ad hoc cube browsing and charting. I must add that there are many other commercial cube browsers, and I really don’t want to list them, since the list and relative strengths are continually changing.

If you are embarking on a data warehouse or business intelligence project, I would encourage you to leave the cube browser choice until quite late in the process. (Just like you shouldn't purchase your production hardware until you are almost ready to deploy a solution.) This is particularly true if you are not familiar with cubes and are not sure what you and your users will need. Microsoft’s SQL Server is probably the best foundation for a data warehouse/business intelligence solution. Particularly its OLAP cube and related technologies such as data mining. I would encourage you to use Excel 2007 and Reporting Services (which is free with any version of SQL Server) as the initial cube delivery vehicles, and trial other products when, and if, you find the need. If you are looking for better thin client visualisation, do try PerformancePoint and Excel Services before you engage with third party vendors. These products are very useful, and they are likely to be greatly improved in future versions. I should add that PerformancePoint dashboards will be incorporated into SharePoint MOSS in future, which makes a lot of sense to me. One other consideration, if you are delivering to users outside of your Intranet/internal network is licensing. SQL Server RS and Excel are quite easy to licence for use over the Internet. Unfortunately, it’s not so easy with PerformancePoint and Excel Services. If that is what you would like to do, I suggest you talk to your Microsoft reseller early on.

Big Queries

2009-09-08T10:00:00.003+10:00

Have you ever wanted to know which queries are consuming the most resource on your server? There is a quick and easy way to do this without starting up SQL Profiler. Just execute the following SQL query.
select top 50
execution_count,
total_physical_reads,
total_logical_reads,
total_elapsed_time/1000 total_elapsed_time,
total_elapsed_time/(1000*execution_count) Avg_Execution_Time,
text
--,*
From Sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)
order by total_elapsed_time desc

This query will list out the top 50 queries from you plan cache and show how much resource they have consumed since they have been in the cache. Note, this is not an accurate way of measuring total resource consumption, since it depends on what and how long plans have been in the cache. However, for a first cut tuning exercise, it is very helpful. If you are looking at overall SQL Server (or machine) activity, they it doesn't matter if a small query is running inefficiently, if it executes infrequently. The query above, provides total_elapsed_time, which is a good indication of which queries (in total) are consuming the most resource.

What I would do in the first instance is copy the text from the query and run an explain, possibly with an execution, and look for tuning opportunities. One of my mottos is that anything can have its performance improved, it's only a matter of how much effort is required.

SQL Server Analysis Services Partitions

2009-08-25T17:36:00.005+10:00

Like relational partitions, SSAS partitions can be very useful in easing operations and, sometimes, improving performance. Like any performance tuning, you really need to know the ramifications of the feature to get the best out of it.

Firstly, partitioning is an Enterprise version, so you will need to have SQL Server EE to make use of it. Secondly, performance doesn't just improve by virtue of having partitions. It depends on what is in the partitions, how many there are and how they are processed/queried. SSAS knows the low and high dimension key attributes for each partition. So if you have partitioned on date, and your query is looking for a particular date value (or range) SSAS will only query the partition (or partitions) relevant for your query. Will your query run faster with partitioning in this instance? Maybe, but not necessarily. I would generally not anticipate an improvement in this scenario, unless there is some caching effect. For example, if the current month (partition) is the "hottest" it will tend to stay in the data cache at the expense of the less queried months. Note, this tends to happen in a large cube without partitions.

On the other hand, if your query does not include a filter that aligns to the partitioning strategy, SSAS will break your query into multiple sub queries, with at least one sub-query in each partition. This can allow the query to be executed in a parallel fashion, which might, but, again, not necessarily, improve performance. If the parallel queries are essentially breaking the workload (as opposed to all performing the same workload for different slices) then there is likely to be a significant performance gain.

Just like a relational database, if there is only one query active at a time, parallelisation is more likely to achieve a performance gain than if there are many concurrent queries. Many concurrent parallised queries can easily saturate a server and degrade overall performance.

One of my favourite strategies for partitioning is not the classic one (which is to partition by date, say one partition per month/year) and it comes with a very high probability of performing well. It is to partition into static and active partitions, with a large static partition(s) and a very small, but volatile, active partition. If you want to keep your cube very close to real-time, evey time you incrementally update a partition the partition is washed out of the data cache. It has to be washed out, since it is now out of date. Unfortunately, if your cube is very large, the next few queries (including cache warmer) will be very slow, while they read the cube (and aggregations) off disk and into memory. So a good potential strategy to to have all of the historical data in one partition and a second, very small, partition, with current data that is getting incrementally updated every minute or so. That way, each minute, the large static partition will remain in memory. Only the small dynamic partition will get washed out. This is good, we are only washing out the small amount of data that we are changing. This strategy requires an addition operational process, where the dynamic data is merged into the static partition. There are a number of ways to do this, including merging partitions, or reprocessing the partitions during the quiet part of the day. Also, this strategy does not negate the possibility of having a classic partitioning strategy also. I.e your large partition may actually be a number of medium sized partitions, but the volatile data is isolated into one small partition.

My second favourite reason for partitioning is, like relational partitions, it makes some operational procedures easier. For example, with a classic partitioning strategy, to drop a month or year's worth of data, simply involves dropping one or more partitions. No reprocessing require.

My suggestion to you, if you are considering partitioning (and you should for large cubes) is to create a few test cubes and benchmark the performance with various partitioning strategies. Monitor your IO, memory and cpu utilisation. Ensure that your tests involve the caching (or washing out of cache) effect, and ensure that you are using data volumes (and hardware) that reflect what will be used in production.

Dynamic OLAP Reports

2009-08-24T06:34:00.007+10:00

The specification from a customer's BI solution, that I just completed, included 3 reports. (The project was really about designing a high performing cube with a hundreds of millions of facts, over 20 real dimensions and updates every 10 minutes.) I love those sort of challenges. Anyway, the 3 reports were essentially the same Profit and Loss Statement at different levels of the business hierarchy. The customer expected 3 reports, and the early BI developer created 3 reports in the PoC, however I created one report that could navigate up and down the business hierarchy with hyperlinks. Needless to say, the customer was delighted to have one report and the flexibility to click up and down the business. This is similar to a blog I made some time ago. I encourage BI developers to use SQL Server Reporting Services Reports (SSRS) on OLAP cubes to create fast and linked reports so that the reports appear like an application to the end user.

Adding hyperlinks to another report in SSRS is really simple, you just right click on the text box, asking for text box properties and clicking on Action Go to Report. The parameters need to be configured. In my case, the parameter for the business unit is created using the current member's unique name and its parent's unique name. So a tiny bit of MDX is required. That brings me onto another of my soapbox themes. BI developers should know MDX. If your BI developer doesn't know MDX, he/she is unlikely to be able to design high performing cubes and will not know how to exploit all the available functionality. it's a bit like asking someone to design a relational data warehouse for you, who doesn't know SQL very well. Good MDX skills are a predicate for good cube designs.

I can't provide the example of my customer's report, but you can see examples of dynamic and linked reports on http://RichardLees.com.au/Sites/Demonstrations

Who's Using Resources on My Server?

2009-07-23T12:15:00.007+10:00

One of my favourite analytical pages is Perfmon by Process. This page shows me which Windows processes are using my critical resources, such as Processor, IO, page file and virtual bytes. When you click on this link, it will show you the critical resources and for each of the last three hours, it shows you which process has been the biggest consumer. The two charts on the right show you the top 10 consumers for the critical resource you have selected on the left (scorecard). By default, the selection is %Processor Time, but you can click on others. My favourite is IO Data Bytes/sec, since this tends to be the most critical resource in a large database system. Try clicking on IO Data Bytes/sec and other counters. See how the grraphs on the right change dynamically, and will show you real time analysis for the last 60 minutes and last 60 hours.

This is just a demonstration. If you have a large production system, do you have such analytical tools available to you? If you don't, I would suggest that you are operating blind, and that you should get some. The toolset I have created uses nothing but SQL Server 2005/2008 and PerformancePoint (for visualisation). In fact, it's so simple, I would like Microsoft to have something like it available as an optional install from SQL Server.

There are lots more real-time demonstrations on http://RichardLees.com.au

Instant File Initialization for SQL Server 2008 (and 2005)

2009-07-22T19:06:00.006+10:00

I will do anything to reduce the IO load on large databases, which is why I am a great fan of compression in SQL Server 2008. However, another way of reducing IO is to ask SQL Server to format new data extents without writing zeroes out to all pages. If your database takes a new extent (by default it is 10% of the file size, which typically isn't good) SQL Server will want to write binary zeroes to all the pages to ensure that some old data isn't hidden in your database. SQL Server does not need the binary zeroes, it is done to ensure data security. Someone else may have deleted a file and perhaps they don’t not want you seeing the data.

Very often, the data security is not an issue and you would like to avoid the writing of binary zeroes. This can be achieved (on NTFS drives) by ensuring that the account running SQL Server has SE_MANAGE_VOLUME_NAME privilege. You can grant this privilege in User Rights Assignment on Windows 2003, XP or above. To do this, grant the SQL Service account Perform volume maintenance tasks local security rights. Alternatively, if the SQL service account has administrator privileges, it will automatically have this privilege. That's all you need to do to avoid having SQL Server write all those binary zeros. By the way, SQL Server always needs to write binary zeroes to the log files, we are only avoiding binary zeroes on the data files.

If you don't want to grant this privilege to the SQL service account, and you want to avoid the IO load during peak times, I suggest that you extend (manually or automatically) your datasets during an off peak times (i.e. before SQL does it for you).

One of the most noticeable tasks affected by binary zeros is database restores. If you watch the restore progress, it will typically not move off 0% until it has written out the entire data files with binary zeroes. After writing every data page, it will then write the backup files over the data pages, during which time, you will see the progress percent increase. So writing binary zeroes can approximately double full database restore times.

Hope that helps you reduce IO load and increase SQL Server performance.

For real-time SQL demonstrations, including Perfmon, which is writing over 300 records every second, see http://RichardLees.com.au/Sites/Demonstrations

Spotted on SproutDaily.com

2009-07-22T18:59:00.004+10:00

Last week I was spotted by SproutDaily.com Some of the best photographers in Sydney take pics around Manly Beach and post them out to everyone registered at SproutDaily.com.

Just can't do anything in Manly without everyone knowing.

Restartable or Rerunnable SSIS Packages

2009-07-01T06:20:00.010+10:00

BI solutions need to be continually importing/updating data from external source systems. I believe that a good BI solution will have automatically correcting load and process jobs. What I mean by that is if a package fails (such as a power or system failure) the application will automatically correct itself next time it runs. In the old days, we used to create "restartable" jobs, where, after a failure, the operator would manually intervene to restart the job with specific parameters. I think there is little excuse nowadays to have applications that require manual intervention after a failure. They should be designed in such a way that they “know” where they are up to and continue, or reprocess.

For example, my Permon application (Perfmon Dashboard) has a job that runs every 180 seconds to transform and load the new records, and incrementally process the OLAP cube. Every now and again the machine is restarted for some reason and the Perfmon update process could be at any point when it is stopped. However, the job will automatically run from the right place, next time it runs. It will know whether there is 3 minutes or 10 minutes of data to process, by using high water marks in the database. In this way, I have no operational overhead in running this application. It just looks after itself. It is the same for the weblogs BI solution on Weblogs Dashboard. If your BI solution requires manual intervention after a failure or an error, get someone else in, like myself, to fix it up to run automatically without manual intervention.

Now having said that, it is often useful to be able to pass parameters to an SSIS package. (I just needed to mention automatically correcting jobs above, as some developers make packages restartable,to help with manual correction, when it should be automatic.) For example, you might want to have the ability to ask the SSIS package to conditionally run one task. This can be achieved quite easily by assigning the Disable property on the task (container etc) using Expressions on the task to set it to a parameter. To pass the parameter it is simply a matter of adding /SET "\Package.Variables[DisableBackup]";False to the cmd for the job in SQL Agent.

Reporting Services Tips for OLAP Cubes

2009-06-26T08:24:00.013+10:00

Naturally I have an inclination to use Reporting Services on OLAP cubes. My feeling is that reports should by and large be run from OLAP cube where they can be dynamic and fast. SQL based reports are more ideally suited for "atomic data" reports (eg transaction lists) and some special reports requiring real time data.

There are a few funnies when you get into OLAP reports with SSRS. The SSRS wizard does a great job of starting the report and building the parameters you need. However, before long you will need to edit the MDX that the wizard creates, to do things like taylor the parameter queries for cascading filters. Here are a couple of tricks that I commonly use.

In SSRS 2008, when you edit any MDX query, the designer will automatically rebuild (in other words wash out any MDX changes you have made) input parameter queries. The simple work around for this frustrating behaviour is to remove the Dimension and Hierarchy values from the query's parameter list.

You might want to use the SSRS calendar control for easy date picking, which is used to filter an OLAP date dimension. This isn't handled automatically, but can be achieved with the following. Change the parameter property to be Date/Time datatype. That will ensure that SSRS uses the date picker. For most reports you will want to dynamically specify the default. This can be simply achieved by a single row OLAP query (for example the last day with data) or from today's date using the now() function. If you use the now() function you can strip off the time with =cdate(format(now(),"yyyy-MM-dd")). The trick now is to use the datetime parameter in your MDX query. What you need to do is construct a full member name using the known dimension-hierarchy names and contruct the member key using the date. The construction of the key will depend on what you have defined as the key of the date hierarchy. If the key is a date, then the fully qualified member name will look something like this. [Date].[Calendar].[TheDate].&[2009-06-26T00:00:00]. So to include the date parameter in your query you can include something like the following in your query strtomember("[Date].[Calendar].[TheDate].&["+format(now(),"yyyy-MM-dd")+"T00:00:00]") Just replace [Date] [Calendar] and [TheDate] with the dimension, hierarchy and level name from your dimension. The MDX query tool in SQL Enterprise Manager will help by providing a sample member. The date parameter might simply be used in the WHERE clause to filter the query or it might be part of a range specification etc. It's a member specification and as such could be used anywhere in your query.

Not so much of a tip, but more of an apology. The MDX query editor in SSRS is a very poor text editor. When I am editing an MDX query, I invariably cut and paste the query into and out of SQL Enterprise Manager Query tool.

Here is an easy way to get a report line number. =RunningValue(1, SUM, "Invoice_Data") The line number can also be used for alternate line shading.

Check the performance of all the MDX queries. If any of them are not running fast enough, see if there is anything you can do to speed them up. One classic way to improve performance and improve report usability is to filter out empty members.

Another classic way to improve performance is to change the use of any single select parameters from StrToSet() to StrToMember() and placed on the WHERE clause.

Just like SQL reports. Or perhaps more so, since OLAP reports are more likely to be run dynamically, link your reports together so that users can easily navigate down and across reports. Essentially, making it easier for users to drill through to focus the salient data. With SSRS reports on OLAP this can be achieved in such a way that users do not need to know anything about databases or even that a cube is behind the report. They simply click on headings or numbers to drill into and across the data that interests them. For example, click on the "Top Memory Consumers" picture above for an OLAP report. The report is dynamic and close to real time. Notice how you can click on any of the processes and get an analysis of that process. Likewise, you can click on any of the counters in that report for a breakdown of the processes using that resource. A good set of dynamic SSRS reports will appear more like an application to end users than a traditional list of static reports.

One last tip is to verify that all the queries in your report definition are actually used. Sometimes SSRS will create new queries for you when you edit parameters. Or you might modify a report and remove a parameter. Do remember to take out the unused queries. All queries appear to fire even if they aren't used.

For examples of real time OLAP reports see http://RichardLees.com.au/Sites/Demonstrations

Bing.com - Microsoft's new search engine

2009-06-22T07:29:00.010+10:00

Have you checked out Microsoft's beta search engine, Bing.com? It has some nice features and now look much more like Google.com.

I have noticed that it is rising on my Top Referrers List. Currently it is in the top 20 and rising. For an up to date list see http://RichardLees.com.au/Sites/Demonstrations

By the way, I count 1 referrer as one person clicking on a link from Bing.com to somewhere on http://RichardLees.com.au So far this month (June) Bing.com has made 45 referrals compared to Google, several hundred, or blogspot.com with 1679.

For real time web analytics see RichardLees.com.au

Microsoft Making PerformancePoint Planning Source Code Available

2009-06-18T19:52:00.005+10:00

I have just heard that Microsoft will be making PerformancePoint Planning software available to partners (http://www.microsoft.com/bi/partners/default.aspx) who were actively involved in evaluating/using the product, at no cost.

This is great news. PerformancePoint Planning was a great V1 product and I think it would have had a great future had Microsoft continued to invest in it. It's a loss to Microsoft, MS partners and customers that the product was discontinued. Let's face it every organisation does planning, and if the organisation is large, you do need software to manage the process. I did blog at the time of the discontinuance announcement http://richardlees.blogspot.com/2009/02/microsoft-is-discontinuing.html

I don't know if there are any restrictions on what the partners can do with the code, but I imagine/hope some company will turn it into a V2 commercial product.

I really think the corporate planning market has room for maturation with none of the incumbent vendors (Hyperion, Peoplesoft, Cognos etc.) providing a ubiquitous product.

Learn SQL - SELECT Product(column)

2009-06-15T07:54:00.011+10:00

I have on occasion been quoted as suggesting that SQL code with temp tables can indicate the author lacks strong SQL skills. Consequently, I have been challenged to write single SQL statements to replace multiple statements stuck together with temp tables. One of these occasions was with Ron Soukup over a bottle of whisky, but that story deserves its own blog.

On this occasion (sometime in 1999) I was challenged to write a query to get the product of a set of numbers. On another bottle of whiskey, as it happens. My friend wanted something like the SUM() function, but he wanted the product of a set of numbers rather than the sum. The business application was quite simple. His client, a financial organisation, needed to calculate compound interes for money on overnight deposit. Each day will have a different market interest rate.

This problem had an interesting solution using a couple of maths functions - Sum(), Exp() and Log(). Here is something like the SQL that I wrote for them.

select exp(sum(LOG(OvernightInterestRate+1))) as CompoundRate
from OvernightDeposits
where TheDate Between '1999-07-23' and '2009-06-18'

Now that's really quite easy, and is much more efficient than putting the data into a temp table and reprocessing it. Having said that, it would be quite useful (and I thought quite easy) to include a Product() function in SQL Server.

For real-time SQL, OLAP and Data Mining demonstrations, goto http://RichardLees.com.au/Sites/Demonstrations

Know SQL - Predicates on the WHERE or OUTER JOIN Clauses

2009-06-14T14:13:00.013+10:00

Writing good SQL is fundamental in a high performing application free of bugs. SQL has been around as the standard database query language since it left QBE by the roadside in the 1980's, and the core syntax hasn't changed much. Database developers (of any rdbms) should fully understand all ANSI aspects of the language.

I was with some developers recently who hadn't appreciated the difference between a filter predicate on a WHERE and a LEFT OUTER JOIN clause. Essentially the difference is whether the predicate is working on the join or on the resultset. If a predicate is on the right side table of a LEFT OUTER JOIN clause, then it will work on the right hand records only. If the same predicate is on the WHERE clause, the entire record (left and right side) will be filtered out. This can affect the number of records returned.

Here is an example of the difference. The first query has a filter on the WHERE clause, so that any objects joined with columns that have a scale<>0 will be entirely filtered out. That is, not all objects with a name like '%sys%' will be returned.

Select o.* from sys.objects o
left outer join sys.columns c on o.object_id=c.object_id
where o.name like '%sys%'
and c.scale <> 0

This second query has had the columns predicate moved to the LEFT OUTER JOIN clause. This is logically very different since the predicate scale<>0 is working on the join. In this query all objects with a name like '%sys%' will be returned. It’s just a matter of whether they are joined with columns.
Select o.* from sys.objects o
left outer join sys.columns c on o.object_id=c.object_id
and c.scale <> 0
where o.name like '%sys%'

I have used sys.objects and sys.columns, since they are tables (well views actually, but that doesn't matter) in the master database (of either SQL Server 2005 or SQL Server 2008) so you can cut and paste the queries into your query editor and visualise the difference.

This is just one subtlety of the SQL language. If it is news to you and you are a database developer, I suggest that you spend some time learning the SQL language more thoroughly. The good news is that any time you invest in the SQL language will be useful to you for a long time, since it does not appear to threatened by a new language anytime soon. (As much as I love MDX, I can't see it replacing SQL in the rdbms.)

Leaving Footprints All Over the Internet

2009-06-13T13:36:00.005+10:00

Someone recently asked me if my web analytics dashboard (http://RichardLees.com.au/Sites/Demonstrations) records information on where they had been on my site. The answer is an emphatic Yes. Not that my site does anything special in the logging department. I am just using Windows IIS and by default it will log every http request. The only extra thing I do is that I put this data in a cube so that it is easy to query and visualise. You should remember that everytime you browse to a page on the internet, there is probably a log on that server recording your IP address, what you requested, date, time etc. And there will often be additional information such as your browser configuration, even the operating system and version number. If you are accessing the internet via a proxy server, then your IP address is shared amongst other proxy server users so you are somewhat camouflaged. But if you are using a proxy server, then you should consider the proxy server is logging every request you make over the internet, and possibly all the intranet browsing too.

So, as I was saying, my site puts all the web logs into a cube so that you can visualise all the activity on my site, even what I do. I built it quite simply as a demonstration of OLAP technology.

You can ad hoc query the activity on my site using ThinSlicer or PerformancePoint. Both of these tools are thin client, only requiring IE6 (or above) and there are no client controls. So if you have been to my site (anything on http://richardlees.com.au/) then you will be able to browse the cube and see where you have been and what resources you used on my server at the time. Simply go to the Interactive Chart Grid on http://RichardLees.com.au/Sites/Demonstrations and navigate to your IP address and display the requests you have made. This is using Microsoft's PerformancePoint dashboard technology, which takes a little getting used to, but it is a fully functional cube browser. For example, the picture above is a query I made, drilling into IP address 84.151.222.71, which happens to be in Munich, Germany. I then drilled into .jpg files to see what pictures had been downloaded. If you don't know your IP address, you can drill down to your geographic location and select the IP address active at the time you were on the site. You could even browse to my site requesting a resource that does not exist, such as HelenClark.jpg, since you will be the only person requesting this resource, you will be able to drill into this resource and list out your own IP address etc. You could then pivot on the IP address and see what else you requested. The cube is available for continuous querying, even though it is being updated with new logs every 15 minutes or so.

Please note, I do not use these web logs for anything other than providing a demonstration of OLAP and data mining technologies. There is no reason to feel paranoid about what I store on my server, but it might make you think about what information other web sites have about your activity.

Altavista is Back in Action

2009-06-08T11:27:00.008+10:00

Anyone who was using the internet in the 1990s, like me, will fondly remember Altavista.com, the best search engine of that era.

I have just noticed that it is back in action and referring people to my site! It seems to have replaced referrals that have come from Yahoo. I guess that shouldn't be so surprising since Yahoo acquired Altavista in a roundabout way via Overture Services.

What is interesting to me is that altavista.com has come back and that almost all of the Altavista referrals (on my site) have come from Chicargo, Illinois. Anyone know why might be?

I have attached a couple of reports, one is simply the Top Referrers Report from http://RichardLees.com.au/Sites/Demonstrations which shows altavista suddenly appearing in Maya 2009. The other I have created using ThinSlicer showing the geographic location of the altavista users. You can do the same remotely on my site. Or, if you prefer, the default dashboard on http://RichardLees.com.au/Sites/Demonstrations has a top referrers grid at the bottom right of the dashboard, which is interactive, so you can drill down on Altavista or drill across to any other dimension.

I find it interesting participating and watching the competition between the big search engines. From the stats on my site, Google.com is by far the largest, with live.com enjoying a steady workload. By the way, the home page on the dashboard above shows the top referrers, but only for the last 5 months. If you want to see a longer range, simply drill up on the time dimension to All, then drill back down to month and you will see all months.

Resizing .jpg files for SharePoint Server 2007

2009-06-06T16:46:00.007+10:00

My internet site (http://richardlees.com.au/) is hosted on Sharepoint Server 2007. There are many photos on my site, particularly, but not exclusively, family snaps. In SharePoint I have resized the photos for easier display. I knew that SharePoint did not physically resize the images, but didn't appreciate the full impact it had on people browsing my site over a low speed link. Since SharePoint did not physically resize the photo (.jpg file) a 2MB file might be downloaded to show a little photo whose image only needs to be a few kilobytes. I am a bit disappointed that Sharepoint doesn't resize images automatically. I kind of feel that since it knows how big the display images are, it could resize them accordingly, and even though it didn't do it now, I thought it must be fixed in a service pack or subsequent release.

Anyway a couple of weeks ago a partner colleague mentioned to me his frustration with these images, so I spent a few minutes and resized all the .jpg images in SharePoint. I was only using about 30 images, so it only took about 30 minutes. It is interesting how much difference this has made to the performance of my web site. The home page on my web dashboard shows average response times for the major file types (.aspx, .axd, .css, and .jpg) over the last 60 days. If you look at this graph (top right on http://RichardLees.com.au/Sites/Demonstrations) you will see .jpg has dropped in response time around May 21. Althought there is quite a lot of daily variations since it is an average of many different size images. I haven't had such a large performance change since I talked to my son about his download activity (http://richardlees.blogspot.com/2009/01/is-my-web-site-getting-slower.html).

You are able to drill into the response time graph, and even take it to a new window if you know how to drive PerformancePoint (see http://richardlees.blogspot.com/2009/04/try-out-microsofts-zero-footprint-cube.html for some quick instructions). I have done this, and drilled down to the photo of Anna and myself on unicycles (one of the main MB culprits). The graph is attached. You can achieve the same, although you will see a more up to date chart since the cube is updating every few minutes.

Two takeaways from this initiative.

If you are using SharePoint 2007, it pays to resize your images to the display size.
It always pays to monitor and log performance of any production application so that you can see user performance and the impact of your tuning exercises

For more close to real-time performance information of my internet site and the two servers behind the site goto http://RichardLees.com.au/Sites/Demonstrations

Analysis Services Database Name and DatabaseID

2009-06-06T06:38:00.003+10:00

Typically an Analysis Services database has a DatabaseID the same as the database name. However, if you rename a database, the DatabaseID will stay the same. This can be a source of confusion and it can lead to useful operational techniques.

Whenever anyone connects to an Analysis Services database, they do so by selecting a particular database name. So when you rename a database, users will see and use the new name. However, SSIS packages that perform Analysis Services processing tasks select the database by the DatabaseID. Also, Analysis Services keeps the DatabaseID as a unique identifier. So when you rename an Analysis Services database, existing SSIS packages will be looking for the old DatabaseID.

So if you have an Analysis Services database, say EasternMining, and you rename it to Eastern Mining, then users will only connect to it by asking for Eastern Mining, which is fine. However if you try to restore a database called EasternMining (which doesn't exist anymore) it will fail, even though you have enabled Allow Database Overwrite. The message will be something like Backup and restore errors: An object with the same 'databaseid' ID but different 'databasename' Name already exists on the instance. (The AllowOverwrite flag only applies to the Object ID, not to the object name.) I find the message a bit misleading, but what it is saying is that a database with that DatabaseID exists already.

Unfortunately, you cannot change the databaseID, without doing something like backing up and restoring the database. Interestingly, an xmla create database statement will overwrite an existing database and DatabaseID. If you want to know the DatabaseID for an Analysis Services database, just right click on the database and select properties.

I have previously blogged on how you might use this to maintain close to continuous availability when you need to deploy a new Analysis Services cube structure. http://richardlees.blogspot.com/2009/05/new-analysis-services-database-version.html

Data Mining as an Enhancement to Basket Analysis

2009-05-31T07:24:00.010+10:00

Big retailers all want to perform some form of basket analysis, where they analyse the mix of products that customers purchase in one basket. This tends to be an extremely hardware intensive exercise. Using OLAP technologies can help in this exercise, but even with OLAP hardware can be stressed since you really need a dimension on TransactionID. (OK, there are some optimisations you can make to minimise this dimension, but it is still very large and expensive.) However, if you want to analyse which products are bought together (along with other variables such as time of day, customer gender etc) then data mining (DM) can be a great tool and offer its analysis at a very low cost (ie you only need a small commodity server to perform the analysis). DM algorithms have been available as part of the standard edition of SQL Server since 2000.

Essentially, you ask the data mining to create a model to predict products, based on other products (and other variables such as time of day, store, customer gender etc). The data mining algorithms can scan billions of transactions and produce a data mining model in a relatively short time. The data mining algorithms search through many combinations of products/attributes and discover which products/attributes have strong associations. Essentially, the DM model will only stores relevant associations and their predictive strength. The data mining model tends to be very small since it only contains the associations, even though the work it has done to find them can be resource consuming. The data mining model does not need to store the billions of baskets, or the associations between irrelevant products. You are able to very easily browse the model to visualise products (and potentially other attributes such as gender and time of day) and their associations. Also, you can use the data mining model to predict which products might be bought in a particular basket. This data mining prediction query is extremely fast, typically a few milliseconds, so it is quite plausible that online transactions are probing the data mining model for suggested products at real time. For example, your billions of transactions might be terabytes of data, while the resultant DM model could be a couple of hundred megabytes. of data.

I have an online demonstration of a model that does something like this on http://EasternMining.com.au/sites/Demonstrations/Pages/LibrariesSuggestions.aspx I don't have a retailer's transaction history. Most companies privacy policies prevent them from me displaying them on the internet, however, if yours doesn't let me know and I will host the db on my site. What I do have is the borrowing history of a real municipal library (with names and personal identifiers fudged), which is very similar in structure to retailer transactions. The book is like a product and a borrower's loaned books are a transaction or basket. In my demonstration, you don't need to enter the books and gender, just select any borrower, and the demonstration will select the books that they have borrowed and their sex. This is a relational query and is actually embedded in the data mining (DMX) query. Here is the "product suggestion" DMX query. It looks a bit wordy, but it is not complicated, and the SQL Server wizard creates most of the query for you.

SELECT flattened
t.[BorrowerNo],t.sex,t.[decadeofbirth],
TopCount(predict([Loans n Titles],include_statistics),$probability,12)
From BookSuggestionFullDT
PREDICTION JOIN
SHAPE {OPENQUERY([Libraries],
'SELECT
BorrowerNo,
Sex,
DecadeOfBirth
FROM Borrowers_Interesting')}
APPEND
({OPENQUERY([Libraries],
'SELECT
Title,
BorrowerNo
FROM LoansInterestingFull
WHERE Borrower = ''Damen Roxburgh'' ')}
RELATE BorrowerNo TO BorrowerNo)
AS Loans_interesting AS t
ON BookSuggestionFullDT.Sex = t.Sex AND
BookSuggestionFullDT.[Decade Of Birth] = t.DecadeOfBirth AND
BookSuggestionFullDT.[Loans n Titles].Title = t.Loans_interesting.Title

Here is a similar query with the values coded into the query. As you can see, it is much easier to read. I have found the DMX language easier to learn than MDX, since the tasks you are requesting tend to be simpler. I.e. you are generally only asking for a prediction based on some input variables.

SELECT flattened
MyCustomer.Customer,MyCustomer.Sex,MyCustomer.DecadeOfBirth,
TopCount(predict([Loans n Titles],include_statistics),$probability,12)
From BookSuggestionFullDT
PREDICTION JOIN
(SELECT "Richard Lees" as Customer,
"Male" as [Sex] ,
"1960s" as DecadeOfBirth,
(select 'Marie Claire' as [Title] union
select 'Cleo' as Title) as books) as MyCustomer
ON BookSuggestionFullDT.Sex = MyCustomer.Sex AND
BookSuggestionFullDT.[Decade Of Birth] = MyCustomer.[DecadeOfBirth] AND
BookSuggestionFullDT.[Loans n Titles].Title = MyCustomer.Books.Title

So what I am suggesting here is that if you are performing basket analysis, or have a desire to, then you should also consider creating a data mining model. The DM model will help find the associations and optionally predict potential products. You could even have online shopping sites use DM models to make predictions based on the basket content so far. The query tends to be a few milliseconds, certainly sub-second.

If you require any assistance in creating or exploiting your data mining models, please don't hesitate to ask me for assistance.

For more online real-time business intelligence demonstrations see http://RichardLees.com.au/Sites/Demonstrations

Richard

Creating your first data mining model

2009-05-29T07:12:00.005+10:00

Data Mining is a entirely new exercise using new technology for most developers. It can be awkward creating your very first model. To help people create their very first data mining model using real data, I have written a whitepaper on msdn. So if you want to create your first model, visualise it, query it, and create a Reporting Services report from it go to http://technet.microsoft.com/en-us/library/dd883232.aspx

You can see a real-time demonstration of data mining on http://EasternMining/sites/Demonstrations/Pages/PredictingLatenciesusingDataMining.aspx

Of course, if you require any assistance with more advanced features of data mining, or applying it to your business strategy, just ask me.

Don't be afraid of Data Mining models and DMX

2009-05-28T15:58:00.004+10:00

Since SQL Server 2000, Microsoft have included data mining algorithms in the standard edition. It continues to astound me that people are still not taking advantage of this technology. OK, you will have to learn something new, but it is a lot of fun and can have profound impact on an organisation. Sometimes all you want, or need, to do is build the model and visualise it.

However, it is often desirable to use the model to make predictions. To do this, you really need to write DMX (Data Mining version of MDX) although the wizard does a good job of helping you. I have some real time data mining demonstrations on my internet site. For example, http://EasternMining.com.au/sites/Demonstrations/Pages/PredictingLatenciesusingDataMining.aspx will query a data mining model with the last 50 hits to the site and predict what the response time will be. Of course there is no benefit in such a model, as we already know the outcome, but it is an interesting demonstration of data mining. The predictions could be for delivery time, probability of fraud, customer profitability, new hire longevity, medical prognosis etc. Here is the DMX query behind the report.

SELECT
Last50.[rid],
Last50.[LogTime],
Predict(ProcessingTimeDT.[Processing Time]) as [Prediction],
Last50.ProcessingTime,
Last50.BytesIn,
Last50.BytesOut,
Last50.Country,
Last50.Resource,
Last50.ClientAgnt as [ClientAgent],
Last50.http_status,
Last50.Operation,
Last50.ReferringServer,
Last50.os,
Last50.City
From
ProcessingTimeDT
PREDICTION JOIN
OPENQUERY([Weblogs],
'SELECT
[rid],
[LogTime],
ProcessingTime,
BytesIn,
BytesOut,
Country,
Resource,
ClientAgnt,
http_status,
Operation,
ReferringServer,
ResourceType,
OK,
OS,
City
FROM
(SELECT TOP (50) rid, LogTime, ProcessingTime, BytesIn, BytesOut, Country, State, City, resource, resourcetype, clientagnt, OK, http_status, operation,
referringServer, calyear, Calmonth,OS
FROM InternetLog_Wide
ORDER BY RID DESC) as Last50Hits') AS Last50
ON ProcessingTimeDT.[Bytes In] = Last50.BytesIn
AND ProcessingTimeDT.[Bytes Out] = Last50.BytesOut
AND ProcessingTimeDT.Country = Last50.Country
AND ProcessingTimeDT.Resource = Last50.Resource
AND ProcessingTimeDT.ResourceType = Last50.ResourceType
AND ProcessingTimeDT.ClientAgnt = Last50.ClientAgnt
AND ProcessingTimeDT.OK = Last50.OK
AND ProcessingTimeDT.[Http Status] = Last50.http_status
AND ProcessingTimeDT.Operation = Last50.Operation
AND ProcessingTimeDT.[Referring Server] = Last50.ReferringServer
AND ProcessingTimeDT.OS = Last50.OS
AND ProcessingTimeDT.City = Last50.City

The DMX query is actually a hybrid query, with the inner query (for the last 50 hits) coming from the SQL relational database. If we take out the hybrid query and just enter some data manually, the query looks a lot cleaner. For example here is a DMX query where I type in a couple of the attributes (country, city, resource) and get a prediction based on this input.

SELECT
Predict(ProcessingTimeDT.[Processing Time]) as [Prediction],
TypedData.*
From
ProcessingTimeDT
PREDICTION JOIN
(SELECT
'UNITED STATES' as Country,
'RichardsWelcomePage.aspx' as Resource,
'MSIE 8.0' as ClientAgnt,
'New York' as City )
AS TypedData
ON ProcessingTimeDT.Country = TypedData.Country
AND ProcessingTimeDT.Resource = TypedData.Resource
AND ProcessingTimeDT.ClientAgnt = TypedData.ClientAgnt
AND ProcessingTimeDT.City = TypedData.City

Notice how the data mining model does not need to have all the attributes. The more you give the query, the more accurate the query will be.

If you need any assistance designing or querying your data mining models, don't hesitate to ask for my assistance. Data mining is one of my favourite activities.

Richard

For more real-time data mining and OLAP demonstrations goto http://EasternMining.com.au/Sites/Demonstrations

For more about Richard Lees goto http://richardlees.com.au/

MDX for Business - Text as a calculated measure

2009-05-27T14:22:00.009+10:00

It surprises many developers that you can have a text value as a calculated measure, since a measure usually contains an aggregated number. However, a text value is very simple and very useful in a calculated measure. It could be as simple as getting a property of a member such as a customer's mobile number -

Member Measures.MobileNumber as Customers.Customer.properties("Mobile Number")

I have a real example, in a report that shows hours on rows and the last three days on columns, and for each cell it shows the top City (customer, product, whatever) by virtue of the number of web requests in that hour. This is shown on the default page of my web dashboard on http://EasternMining.com.au/Sites/Demonstrations See down near the bottom is a table called Top Cities by Hour - Last 3 Days. It shows the city with the most activity on my site for that hour. It might look ahead of time to you, if you are in America or Europe. It is actually very close to real-time in Sydney Australia.

Here is the mdx

With Member Measures.TopCity as
Topcount([Client Host].[Client Geography].[City]
,1,measures.[request count]).item(0).member_caption
Select
tail([Date].[Year Month Day].[Day],3) on Columns,
[HoursOfDay].[HoursOfDay].[Hour Of Day] on Rows
From EasternMining
Where Measures.TopCity

See how simple the mdx is. Again, imagine having to write the same query in SQL. Also, if I want to change something in the report, such as last 5 days, last 3 months etc, it is very simple. In fact, If I chage [City] to [Region] I get the following report. This report tends to show the time zones of Asia, Europe and Americas. See how Americas tend to be early morning (Sydney time) while Europe is late at night and Asia is visiting during the online day.

It's so very easy with MDX. If the answer is in the data, then one MDX expression should be able to ask the question. Let me know if you need any assistance in writing or learning mdx.

MDX for Business - Ranking Customers/Cities

2009-05-22T12:36:00.009+10:00

I was just asked to help with an MDX query to list out the top 50 Customers and provide ranks for the last 3 periods. This is a relatively simple MDX query, which someone with moderate MDX skills should be able to write. It might be interesting to you, so here it is.

with
Set Last3Months as tail([Date].[Year Month Day].[Month],3)
Member Measures.Last3MonthsHits as Sum(Last3Months,Measures.Hits)
Set Top50Cities as topcount(filter([Client Host].[Client Geography].[City],[Client Host].[Client Geography].member_caption<>"-"),50,Measures.Last3MonthsHits)
Member Measures.RankThisMonth as
rank([Client Host].[Client Geography].currentmember,order(Top50Cities,Measures.Hits,BDESC))
Member Measures.Movement as Measures.RankThisMonth-(Measures.RankThisMonth,[Date].[Year Month Day].prevmember)

Select Last3Months
*{Measures.Hits,Measures.RankThisMonth,Measures.Movement} on 0,
Top50Cities on 1
From EasternMining

It is a real query and I have executed it on my Weblogs cube (all hits and sessions from my web server). The results are formatted in Excel, which I have found interesting. Sydney (my home town) naturally is top of the list, but then comes New York and London. I don't do any business in these cities, which is why I find it interesting. Perhaps I should have offices there? Redmond is quite high. I guess my old Microsoft colleagues are still finding the real time demonstrations interesting.

Anyway, you can read the MDX and see what it is doing. Notice the elegance of the MDX query. It is very succinct, and easy to read. For those of you who know SQL, can you imagine writing this query in SQL? It wouldn't look anywhere near as elegant, nor would it perform as well.

If you would like assistance with your OLAP cube design or MDX query writing, please don't hesitate to ask.

Perfmon Has Everything

2009-05-22T11:29:00.007+10:00

I think I have blogged on the Perfmon cube before, but I am blogging again since many people still do not appreciate how valuable and comprehensive the information from Perfmon can be. When you have a production performance issue, it is so much easier to diagnose if you can do so without adding further stress on the server and by looking at trend information. For example, if your server is busy and going slow, wouldn't it be good to see what cpu/memory/io metrics are now, and compare them to how they were before the performance issue? If you don't store historical information, trend analysis is not possible. And without historical information you will be less likely to preemptively act to avoid a performance problem.

My dashboard http://EasternMining.com.au/sites/Demonstrations/Shared%20Documents/Windows%20Performance%20Monitor/Perfmon.aspx shows many perfmon metrics, but the dashboard conceals how rich the cube is. Notice the headline information in the dashboard above. Now let's assume that there might be an IO bottleneck (very common in database servers nowadays). Click on the Disk tab at the top of the dashboard. This will show you the Physical Disk counters from perfmon. You can filter it by a particular machine. Now click on Disk Bytes/sec in the main display table. Notice how the two graphs on the right dynamically chart this counter for the last 60 minutes and last 60 hours. We can see that there is a large amount of IO on the B: drive during the last couple of days. Now we want to know which program (windows process) is consuming this IO. Perfmon has all the information. Click on Process tab at the top of the dashboard. Then click on IO Data Bytes/sec in the main table. Notice how the graphs on the right chart the top processes of this counter. Also try clicking on IO Write Bytes/sec and see that most of the IO activity is write IO. The chart tells us which process is consuming the IO. In my case it is msmdsrv.exe but it might be different by the time you look at it. All this analysis can be performed without increasing load on the target servers.

If you are interested in Windows performance, try clicking around the Perfmon dashboard tabs on my demonstration site above. The Perfmon metrics are very close to real time (<=180 second lag). You can also use ThinSlicer for ad hoc queries of the Perfmon cube from http://richardlees.com.au:8080/iislog/tsPerfmon.aspx?Column=[DateTime].[Year%20Month%20Day%20Hour]&Row=[Counters].[Counter%20Hierarchy]&Measure=Measures.[ValueAverage]

Also if you would like a Perfmon cube in your organisation, I would be happy to help you. The only technologies used to create the real time dashboard above is Microsoft SQL Server and Microsoft PerformancePoint (PerformancePoint is optional for an application like this, since it is just the vehicle for displaying the information from the cube).

New Analysis Services database version with minimal downtime

2009-05-17T20:11:00.008+10:00

It can be operationally challenging to deploy a new AS database version without cube downtime. Typically, if you deploy a new version of an AS database, the database will be offline until the database has completed a full process. This might be a long time in an environment where the large measure groups are very large and are only incrementally processed (on a daily, hourly, minute etc. schedule), since the full process may take many hours. This can be a problem in SQL Server OLAP as we have been operationally used to a cube being available 24 by 7, since SQL Server 7.0.

One workaround is to deploy a new version of the database with a slightly different name, for example the existing database might be Perfmon and the new version might be Perfmon_V2. In this way, the new database can be deployed and processing while the existing database is online and available. The trick comes when the new database has completed processing. The old database could be deleted and the new database renamed to the original name. This has a slight snag, since when you rename a database, the databaseID remains the same, and the DatbaseID is used by SSIS in AS processing tasks. So you would need to change all the SSIS packages that refer to the database. This would be confusing to manage since the database names would be out of sync with the databaseIDs. The extra trick is to back up the new AS database after processing and restore it over the original database. When you restore an AS database, the database name and databaseID are in sync.

Hence, for an AS database that takes a long time to fully process, to deploy a new database version follow these steps.

Deploy the new AS database with a suffix of _V2
Fully process the new AS database
Backup the new AS database
Restore the AS database over the original database (Allow Database Overwrite)

The downtime is limited to the duration of the restore operation.

Note, the original AS database could continue with incremental processing during the new database full process, although some consideration needs to be given to the way you manage high water marks in the measure groups. This workaround is suitable for SQL Server 2005 and SQL Server 2008.

For realtime online OLAP and data mining demonstrations see http://RichardLees.com.au/Sites/Demonstrations

SQL Server Fragmentation

2009-05-04T18:36:00.012+10:00

There are two types of fragmentation

SQL Server Fragmentation

You can analyse SQL fragmentation with DBCC SHOWCONTIG, and use SQL Server utilities to reorganise, if necessary. Most DBAs know about this type of fragmentation and SQL Server utilities can help you reoganise your databases online.

OS Level Fragmentation

SQL Server knows nothing about this fragmentation since it is managed by the OS. You need to use the system defragmentation tool (or file copy) to defrag, which is not easy while SQL Server has the file open. A much better strategy is to avoid OS fragmentation. To do this simply ask SQL Server to grow files at a reasonable size. 1 MB is not a good increment for a terabye database. Also the default of 10% is not very good, since an active transaction that requires additional space will have to wait while the entire 10% is formatted before it can continue. Not a good thing to happen in a high OLTP environment. Notice how the Defrag utility is showing the number of fragments per file. You should be a little bit concerned about database files with over about 100 fragments, and anything over 1000 fragments may well be causing you performance issues.

The best strategy is to preallocate the space in large chunks, during quiet times, so that no transaction has to wait and you avoid OS fragmentation. However, just in case the db file is out of space give it a smallish allocation size of something like 200MB. This is a compromise between not having the file too fragmented and not having online transactions wait for several minutes for formatting.

Photo Switcheroo

2009-04-21T06:15:00.009+10:00

Everyone knows that my web logs go into a cube and provide a demonstration of how powerful OLAP cubes are (http://RichardLees.com.au/Sites/Demonstrations) Well I have noticed an increasing number of hits from personal sites that have links to photos on my site. One in particular - the surfing photo on at top left. I am not sure if they do it for copyright or web performance reasons. Anyway, I have switched the top left photo for one of myself - top right. Is that an awful thing to do? If my conscience gets the better of me, I will switch them back.

Here is a list of a few of the sites that are using the photo and now have my photo. No doubt they will notice the old guy on the surf ski, at some stage, and update the photo.

http://www4.ncsu.edu/~btcapo2/
http://forum.fok.nl/topic/1222710/5/50
http://www.myspace.com/apinos
http://www.myspace.com/szandrathequeen

There are more sites using the surfing photo. If you are interested, you can query my weblogs cube and look for Referrers to Surfing.jpg It is one 0f the top referring targets. You can also see which sites are still using it. Just look for hits after April 20.

Richard

For real time OLAP and data mining demonstrations go to http://RichardLees.com.au/Sites/Demonstrations

Try out Microsoft's zero footprint cube browser

2009-04-09T09:44:00.012+10:00

Many people are not aware that Microsoft has a thin client cube browser. It is part of the current version of PerformancePoint Server, (although that will change in future and become part of SharePoint).

I have PerformancePoint on my demonstration site, so you are able to try out the cube browser. All you need is Internet Explorer (version 6 or above). It doesn't appear to be supported by other cube browsers as yet. To try out the cube browser, simply go to my demonstration site, http://RichardLees.com.au/Sites/Demonstrations. The home page is a PerformancePoint page with several SharePoint webparts. All the charts on this home page are from a real time database of web activity on RichardLees.com.au. Some of these web parts, I have written MDX to get exactly the chart I want. These charts are not interactive. However many of the charts I have created by simply slicing and filtering the data, which means that you can continue to slice and dice. For example, on the demonstrations home page try right clicking on the chart Top Referrers Last Few Months column heading March and select Drill Up. That will take you to the higher level on the Date dimension. Then right click on 2009 and select drill up again, which will take you to the All member of the date dimension. Now right click on one of the row headings (these are referring sites to RichardLees.com.au) and select Drill Up. Right click on the row heading again and select Drill Up. Now you will be at the All level of the entire cube, and able to drill to any part of the cube. Note, you will also find a useful button on the right hand side of the webpart, which has an option to Open in new window. This will give you much more real estate to work with. You can right click on any of the row/column heading and select Drill Down to and select any dimension hierarchy such as Client Agent, Client Host, Date, Entry Exit, Hour, Machine, Operation, Port, Status HTTP, Status Win32 etc. Actually, there are more dimensions than the webpart can display. As you hover over each dimension the available hierarchies are displayed.

Some of the useful thin client functions you have are Show only, Remove, Sort (rows and/or columns), Report Type (grid, column, line, stacked, stacke 100%), Pivot, Filter. You really can slice and dice to any part of the cube you desire.

Try it out on http://RichardLees.com.au/Sites/Demonstrations If you are good, you should be able to navigate to filter out your own activity on this site and see where you have been. There are other cubes on this site that you can also navigate such as the New Zealand census, Perfmon stats from my demonstration machines and Foodmart. If you are having trouble understanding how to use the functionality and want a simpler cube, use the New Zealand census cube, since it has a much simpler structure and is easy to consume.

Internet Explorer 8 is Now Available

2009-03-31T07:52:00.004+11:00

There has been a jump in Internet Explorer 8 activity on my site since it was release a couple of weeks ago. It jumped from about 5% of site activity to about 15%. Not surprisingly this increase has come from IE6 and IE7. However, what really intrigues me is how much Internet Explorer 6 is still being used. It still represents a little over 20% of total activity. See chart on right, or for a live graph see http://RichardLees.com.au/Sites/Demonstrations The data is kept up to within 15 minutes of real time. You are able to slice and dice the web activity via the PerformancePoint graphs or "ThinSlicer on Weblogs". Note, these thin client cube browsers work best with IE.

Nigel Pendse' BI Survey 8

2009-03-29T08:14:00.003+11:00

As a participant, I have just received a complimentary copy of BI Survey 8. I have been reading Nigel's surveys since 1999. While I often disagreed with some of his findings, I have always considered him the best analyst in the Business Intelligence space.

Unfortunately, I am not able to quote anything from the BI Survey. Nigel Pendse is remunerated by subscribers (not vendors) which makes his surveys refreshingly independent.

I would encourage any organization, about to embark on a data warehouse/business intelligence project, to subscribe to this analysis. Unless you are a seasoned BI developer, you will benefit from many insights and be much better armed before being caressed by a dapper BI sales team.

The current survey includes analyses on the selection process, who succeeded by vendor, biggest outstanding issues, size of deployment etc. etc. Those who are familiar with Nigel’s reports will know that he is very candid when talking about vendors and products. He really doesn’t hold back, understandably since this is what his subscribers are paying for.

I encourage openness in our BI industry. My hope is that the more informed organizations are, the better the decisions they will make, improving the effectiveness and reputation of our BI industry.

You can download a free chapter or subscribe to the full survey on http://www.bi-survey.com

Richard
For real-time online BI demonstrations goto http://RichardLees.com.au/Sites/Demonstrations

Error 44. No mapping between account names and security IDs was done

2009-03-06T15:59:00.004+11:00

Have you ever received this error?

Error 44 The following system error occurred: No mapping between account names and security IDs was done.

You will receive this error when you have windows accounts or groups in an AS project that you are deploying to a server in a different domain. Ie. the server does not recognise these accounts.

A simple work around is to leave the windows users and groups out of the roles and add them in from SQL Server Management Studio. Another option, if it is a common deployment is to use AMO and add the appropriate accounts automatically. Also, if you use the AS Deployment wizard there are options to omit role deployment.

Richard

Space Considerations for Various OLAP Data Types

2009-02-18T19:32:00.003+11:00

Data types can have huge space and performance implications on large SQL tables, and there are similar implications for large OLAP cubes. We all know that double word numbers take up more space than single word numbers, but what exactly is the difference?

I have performed a simple benchmark to compare the space used of the various data types. My tests involved a

44 Million row fact table
3 measures containing values between -99 and 1039.22
5 dimensions
SQL Server 2008
Zero aggregations

I used 6 datatypes in SQL Server tables. The SQL data types I employed were

Money
Float
Decimal
Bigint
Integer
Real

For the Money data type, I created two cubes, since Analysis Services defaulted the Money data type to Double. I created one cube with the default Double data type, and another with the cube data type as Currency.

The results show that the best performing (in terms of size) were

Bigint and Integer (41% smaller than Double)
Currency (25% smaller than Double)
Single (3% smaller than Double)
Double

Note, Sometimes the data type you choose is determined (or constrained) by the domain of the numbers you are aggregating. For example, if your numbers contain significant digits after the decimal point, then Bigint and Integer are inappropriate, unless you are happy to round them. Likewise, for imprecise numbers, Double gives you the most precision, which you might need. But just because the underlying SQL table uses a single or double byte precision data type, if the number is always an integer, you might as well tell Analysis Services to use a Bigint and save on space.

It shouldn't surprise you that the binary numbers (Bigint and Integer) are the smallest on disk, nor that they took the same space as each other. Analysis Services compresses all the cells before writing to disk, and the binary numbers will tend to have lots of leading zeroes, which compress very well. Similarly, but to a lessor extent, Currency (really a packed decimal) compresses next best.

Just in case you are wondering why bother fiddling with data types when the cube is only a gigabyte or two in size, and disk space is plentiful. The reason is that if you can save 40% in your cube size, processing and querying will be noticeably faster. With a smaller cube, your cache will warm faster, and if the entire cube doesn't fit in RAM, you will have a greator proportion of your cube in RAM with a smaller cube. Ie. your queries will run much faster.

Richard

for real-time online OLAP demonstrations go to http://RichardLees.com.au/Sites/Demonstrations

How many rows in a table

2009-02-09T19:45:00.004+11:00

A very common DBA/developer task is to query how many rows there are in a table. Of course the natural way to do this is to write a query such as

SELECT COUNT(*) FROM MyTable

However, this query will be slow for a very large table, particularly if it only has a clustered index. Why not just ask SQL Server for the stats on the table? If your SQL Server database has the default settings, it will be continually sampling the table and will generally have a very good idea of how many records there are. A very simple way of asking the optimiser for these stats is to write the following query

SELECT * FROM MyTable

and click on the Display Estimated Execution Plan button, which looks a bit like 3 green boxes and a caption. SQL Server will tell you, amongst other things, the number of records it expects to read as you hover over the scan object.

I encourage developers to click on this button, not just to get a quick count, but so that they start to use the showplan more often. In my view, the more often you use showplan and the more familiar you get with the optimiser, the higher performing SQL you will write.

Richard

for real time SQL, OLAP, Data Mining, PerformancePoint, Excel Services see http://RichardLees.com.au/Sites/Demonstrations

Microsoft is discontinuing PerformancePoint Server

2009-02-02T13:06:00.003+11:00

This is hard for me to fathom, let alone beleive. http://www.informationweek.com/news/business_intelligence/analytics/showArticle.jhtml?articleID=212902915

Microsoft was a new entrant to the Financial Planning software arena with PPS back in 2007. However it's V1 product received positive reviews (see Nigel Pendse's report). However, it appears that they won't be doing any more work on the planning component after the release of PPS 2007 sp3, and will roll the dashboard software into SharePoint Server.

I am pleased to hear that they are continuing the dashboard software, since that's the component that I often use. See my demonstration site on http://RichardLees.com.au/Sites/Demonstrations and it makes sense to roll it into SharePoint. Hopefully this will mean that it will be easy to license it for Internet use.

But I can't help but feel that they are making a mistake in dropping the planning product that could have a huge future.

Richard
http://RichardLees.com.au

Is my web site getting slower

2009-01-30T10:10:00.004+11:00

Strange things have been happening in our household over the Christmas holidays. My web dashboard tells me that as from January 1st, my web server's average respone times have deteriorated badly. A few days ago my ISP informed me that I had exceeded my monthly limit, and one of my sons has been watching a lot of movies on his laptop lately.

The chart above (you can see the latest from http://RichardLees.com.au/Sites/Demonstrations) shows the dramatic slow down of the response times since the downloads were happening. Has anyone using my site noticed the slow down?

My son confesses that he might have used up a little of our bandwidth downloading these movies and from now on he will limit it to one at a time and mostly after hours. Also, I think that since school has started he won't have so much time to watch movies on his laptop. At least I am hoping.

What would be really nice is an ability to give a lower priority to these downloads (happening in the background) over online http requests to my server. I am not much of a network engineer. Does anyone know if there is a simple way of prioritising this adsl2 traffic?

Richard

http://RichadLees.com.au

Drill Through in Analysis Services

2009-01-10T10:36:00.008+11:00

Drill through functionality was not included in the first version of OLAP technology from Microsoft (SQL Server 7). I found it such sought after requirement that I created my own drill through for ThinSlicer (the first thin client cube browser). It would simply generate an SQL statement based on the dimension members and assumed that the level names were the same as the column names. Then in 2000, drill though functionality was included in Analysis Services, so I removed my custom code.

However, things changed in 2005 (SQL Server 2005) when drill through architecture was changed so that a drill through request did not go to the relational database (for the atomic records) rather, it returned the most detailed cell data from the cube. This, was a huge change in functionality, and some would argue that is not "drill through" but "drill bottom". As you can imagine, there are security implications with drilling through to relational data. In the cube, you can include quite complex security functionality, which would be difficult to honor in a drill through. This might be the reason drill through was dramatically changed in SQL Server 2005.

I hope that one day SQL Server will bring back drill through to relational data. It is such a useful function for many cubes. In the meantime, if you want to include drill through to the atomic records, you will need to create a cube action and write some custom code.

One thing to take into account when you do drill through to atomic data in the relational database is SQL performance. As we know, cube queries tend to have more reliable response times than ad hoc SQL queries. If you write your own customer drill through functionality, I have two suggestions

Use "Top n" in your query (or equivalent from a non ms db). The optimiser takes this into account.
Create a non clustered index on each of the foreign keys

For an example of how easy drill through is simply go to my demonstrations home page http://RichardLees.com.au/Sites/Demonstrations, right click on one of the charts and select "show details". Here is an example of the "drill through" records you will be returned.

I didn't need to write any code for this drill through. The graph is presented by PerformancePoint, and like any good cube browser, it simply honors the drill through functionality of Analysis Services.

Enabling drill through on the cube, couldn't be simpler, you simply enable drill through on the role.

Richard

http://richardlees.com.au/

Who's Reading This Blog?

2008-12-21T11:15:00.016+11:00

I don't host this blog site, consequently I don't have access to the logs to analyse activity on the site. However, that doesn't mean that I can't monitor blog activity, and indeed, let all of you analyse the activity of this blog.

This is an old trick really. I have simply embedded an tag in my profile that has a reference to http://EasternMining.com.au/blogs.gif That resource does not exist, but it requests my own web server, which I have an automated process to add logs to a publicly available cube. You can see lots of real-time web log analysis demonstrations on my home site http://RichardLees.com.au/Sites/Demonstrations

So you and I can view how many people are reading this blog, when and where they are reading from. See Blog Activity or navigate there from the "Web Performance" page of the "Web Analytics" dashboard on my demonstrations site. Ignore all the graphs, except the chart at the bottom left of the page. This chart is showing the number of visitors by Geography for the last 60 days. It's only showing about 10 days today, since I added the tag in on December 11. You don't have to stop there, since the graph is presented by Microsoft PerformancePoint, it is interactive and you are able to drill down further, change chart type etc. for example, here is a sorted list of client IP addresses. You can ask for exactly the same, and you will be able to find your own IP address in the list from when you read this blog. Because, whenever you go to this page, you make a real-time query to the web logs cube.

I would like to point out that I have added this functionality simply to provide an interesting live demonstration. I don't have any particular reason to know anyone's IP address. Although having said that, this might be a useful reminder to many people of the tracks they are leaving behind on the Internet.

Richard

http://richardlees.com.au/

Book Review: Data Mining with SQL Server 2008

2008-12-11T20:39:00.006+11:00

I remember when data mining algorithms were first included in SQL Server 2000. It was very exciting and I immediately went to the municipal library and talked them into giving me an extract of their database for data mining demonstrations. That version of SQL Server had no data mining documentation. But that didn't worry me and I helped a few organisations exploit data mining. However there weren't many people that went to the trouble of learning SQL Server's data mining technolgy. SQL Server 2005 had some documentation but data mining still wasn't used to its potential. SQL Server 2008 has been greatly beefed up in its data mining capability, primarily in the ancillary tools that professional data miners demand, such as lift charts and validity testing. Now SQL Server 2008 has, arguably, the best set of data mining tools in the market. So if you have been putting off data mining, don't delay any longer.

This book by Jamie MacLennan, ZhaoHui Tang and Bogdan Crivat (all developers of the product in Redmond) is a very practical guide and quite readable by someone new to data mining. It starts with the data mining tools included in Excel 2007 and goes on to detail all the algorithms, the syntax of the DMX language and embedding data mining in your applications. Experienced data mining will also find the book useful. I found many useful tips. For example, I only just learnt that you can nest MDX in your DMX queries. It is more common to embed SQL in data mining queries.

To learn about data mining, I really believe that you need some real data to explore. The book has a download site where readers can download databases and demonstrations to experiment with.

If you are using Analysis Services and haven't yet started data mining, I suggest that you get a copy of this book and teach yourself data mining. Data mining is going to be really big.

I did have trouble with Wiley download url, but here is a direct link for the exercise data. http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470277742,descCd-DOWNLOAD.html

By the way, for anyone who is interested, there are a couple of live data mining demonstrations on my site. Where data mining is embedded in Reporting Services (also covered in the book). There is a book suggestion tool using the library data I mentioned above, and the another that predicts response times for the last 50 http requests on my web server. This demonstration has no practical value, but hopefully you can draw the analogy to a similar model that predicts customer profitability etc. http://RichardLees.com.au/sites/Demonstrations/Pages/LibrariesSuggestions.aspx

Richard

http://richardlees.com.au/

Mixing SQL and MDX queries

2008-12-10T12:42:00.002+11:00

A very useful tool in any BI developers toolbox is the OpenRowset query that can combine the data from an SQL and AS database. If you do Data Mining, the chances are that you use it all the time. The basic format is quite simple.
SELECT * FROM OPENROWSET(provider, connection, query)

There are many occasions when this heterogeneous query is useful, for example

ETL processes that need to query source systems depending on what data is in the OLAP database, or where the OLAP database is up to.
SQL transaction system might need to query summary data, and while the programmers can query the OLAP database with a MSOLAP provider, they might find it easier to use their existing SQL provider with an OpenRowset.
You need a combination of aggregated and atomic data for a query
You are data mining and want to predict values based on data within an SQL database

Here is a trivial example of an SQL query joining with an OLAP query. The OLAP database is the Perfmon cube (on http://RichardLees.com.au/Sites/Demonstrations Perfmon) which is joined with sql data from sys.databases.

SELECT
db.*,
olap."[Measures].[Value]" as "Average Transactions/Second"
FROM OpenRowset('MSOLAP','DATASOURCE=Localhost; Initial Catalog=Perfmon;',
'SELECT
Measures.[Value] ON COLUMNS,
[Counter Details].[Database Name].[Database Name] ON ROWS
FROM Perfmon
Where ([Counter Details].[Counter].[Object].&[SQLServer:Databases].&[Transactions/sec]) ') as olap
INNER JOIN sys.databases db on convert(varchar(255),olap."[Counter Details].[Database Name].[Database Name].[MEMBER_CAPTION]")=db.name

Richard,
http://RichardLees.com.au

Imagination is More Important Than Knowledge

2008-12-06T19:25:00.005+11:00

This immortal quote from Albert Einstein applies perfectly in developing Business Intelligence solutions. Not to belittle knowledge and skills, they are obviously important, but just like its cousin, Business Strategy, BI requires a great deal of imagination and creative thinking to get great solutions.

In my view, you don’t create great BI solutions by implementing text book solutions. Every organization has its unique set of data and its unique Business Strategy so BI solutions need to be uniquely developed.

The question is, how do you improve your creative thinking? In my view there are a few “other side of brain” activities that help. I find that juggling, unicycling and surfing help me. When I first joined Microsoft (1995) there was a culture of juggling, and every few hours we would take a 10 minutes juggling break. At the time, I simply saw juggling as a more healthy excuse than smoking for social interaction. But I do think the peripheral brain activity somehow facilitated creative thinking. When you juggle, you cannot focus on the balls, rather, you rely on peripheral vision. This leaves you to focus on something else, and somehow you come up with insightful options with your peripheral brain busy.

Recently, our family has taken up unicycling. Yes, anyone can learn to unicycle. I am getting that same feeling of exercising the other half of my brain when unicycling. So, if you want to be more creative, I encourage you to spend time doing these “other side brain” activities. What’s your creative activity; drama, photography, creating music?

Richard Lees
http://richardlees.com.au/

Analysis Services Won't Start - Invalid Data Directory.

2008-12-05T09:25:00.004+11:00

MSSQLServerOLAPService File system error: Error occurred during the creation of directory.
This can be really annoying. You are trying to start Analysis Services service, but it refuses to start and you either don't know why, or you now know realise that you have updated the data directory and it doesn't exist. But how do you change it now that Analysis Services won't start?
This can happen when you change the data directory for Analysis Services to an invalid directory, or the drive is not available. An invalid drive includes a root directory drive. You really need to create a directory for Analysis Services and ensure that the service has read/write access rights to that directory.
The solution is relatively simple. Simply find the msmdsrv.ini file (it is typically in a directory like C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Config. Edit the file using notepad and change the DataDir value to what it should be. Analysis Services should start up now.
Having said that, I do encourage people to set the data and temp directories for Analysis Services to appropriate directories. On a large OLAP server, you really want to have these directories on fast disk subsystems. The temp directory, in particular, can incur large volumes of read/write activity when processing a large cube.

Richard
http://richardlees.com.au/

Customer Retention and other complex analysis made easy with reports over OLAP

2008-12-03T09:12:00.001+11:00

Analysis Services and Reporting Services make it easy to create complex analysis embedded in easy to consume reports. You can embed complex queries inside a report that everyone can use. For example, see my "Customer Retention" live demonstration on
http://richardlees.com.au/sites/Demonstrations/Pages/VisitorRetention.aspx It shows the retention figures for my web site over the last rolling 12 months. The algorithm for calculating retention in this demonstration is as follows
Number of Customers who were active in the preceding 2 months who have also been active in the current month
/Number of Customers who were active in the preceding 2 months

This demonstration is live and dynamic, so as you click on the report a query executes to determine the answer at the current point in time. The data is continually changing. Notice that you can also click on a particular retention figure to drill through to the customers that were active in the preceding two months but have not been active in the current month. This is just one example of a "complex" query made easy within a Reporting Services report. Imagine how difficult it would be to get the same answer by simply using the slice and dice functionality of a cube browser (albeit a powerful one) such as Excel. Combining dynamic MDX with SQL Server Reports is a great way of providing analysis to end users in a very easy to consume format.

Richard

http://RichardLees.com.au

Elaborate your cubes with conditional formatting

2008-11-29T06:12:00.004+11:00

SQL Server Analysis Services cubes have supported conditional formatting of measures since 2000. Now that Excel supports conditional formatting, they really are a "must have" to make some cubes more readable. For example, my PerfMon cube has the same “Value” measure, which could be “Bytes of Virtual Storage” or “Seconds per Read IO”. You really want to format the number depending on what it is. For example, a 9 digit number probably wants to be formatted as #,#, while a number of about 0.001 probably wants 6 significant digits.
This is all very easy with Analysis Services, simply put a condition on the FORMAT clause. For example
FORMAT_STRING =
iif(ROOT(DateTime),[Measures].[Value])>100,"#,#",
iif(ROOT(DateTime),[Measures].[Value])>0.1,"#,#0.000","#,#0.000000"))
I like to add colour formatting, so that the number formatting stands out. For example, on the same measure, I might add
FORE_COLOR=
iif(ROOT(DateTime),[Measures].[Value])>100, RGB(0,0,0),
iif(ROOT(DateTime),[Measures].[Value])>0.1, RGB(0,155,0), rgb(180,180,180)))
I use the ROOT() function so that the formatting is not specific to any particular cell, which would be annoying to the eye. Rather all cells for any one counter will be consistent in their format. For an demonstration of conditional formatting at work (on a live cube using the cube browser I wrote, ThinSlicer) go to http://richardlees.com.au:8080/iislog/tsPerfmon.aspx Just clicking “Display” you will see the numbers in a light grey colour, since these numbers don’t mean anything. Once you drill down one of the counter hierarchies the true number and colour formatting come into effect. It works exactly the same in Excel. Unfortunately, Reporting Services does not support these conditional formats yet, although, one could argue that the need for RS to honour conditional formatting is less. Performance Point dashboards support the conditional number formatting. For a demonstration you can see the same live cube through a PPS dashboard on http://RichardLees.com.au/sites/Demonstrations/Shared%20Documents/Windows%20Performance%20Monitor/Interactive%20Chart-Grid.aspx

Not all cubes have the need for conditional formatting, since the numbers in any one measure tend to be homogenous. However, cubes that contain company accounts (GL cubes etc) really need this sort of conditional formatting, as the number you are looking at could "Turnover", "Average Tax/Transaction" etc. These really need conditional formatting.

Richard

http://RichardLees.com.au

Can No Longer Change Tables in SQL Server 2008 Management Studio

2008-11-24T10:30:00.006+11:00

Have you made a design change to a table in SQL Server 2008 and got an error message saying that "Saving changes is not permitted? The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

The default option has been changed in SQL 2008, so that changes requiring dropping and recreating tables are not permitted dynamically. I guess this has been changed because too many DBAs have made changes to large tables and got themselves into trouble.

If you know that your table is small, and you want to be able to save changes, you can simply change the default action in the Tools / Options / Designers settings and uncheck the "Prevent saving changes that require table re-creation" option.

Warning, don't use this to make changes to very large tables. For very large tables, I suggest you manually create the new table and use SSIS to load the new table from the old table. Then drop the old table and rename the new table. Also remember to recompile your views on this table.

http://RichardLees.com.au