The impact of the host OS on virtual machine performance
by Christo on Nov.30, 2011, under Christo Olivier
If you use virtual machines on a daily basis then I am sure you would have at some point wondered which host OS is the best to run your VM’s on. So I decided to do a very quick basic benchmark of a Windows Server 2008 R2 virtual machine on both Windows 7 and Ubuntu 11.10 as hosts. For the benchmark I used PassMark PerformanceTest 7 (installed on the VM).
The machine that I ran this on was my Sony Vaio VPCF115FM latop. It has a core i7 1.60 processor with 8GB of RAM. The VM was on a Seagate 1TB external drive which runs at 5400rpm.
The file system on the external drive was NTFS and remained NTFS for both Windows and Ubuntu tests.
The Windows 7 with SP1 host OS installation only included Office 2010 and all anti virus and firewall software turned off and closed down. I also set the Removal Policy of the external drive to Better Performance instead of the Quick Removal default.
The Ubuntu host OS was stock standard Ubuntu 11.10 with the Nvidia drivers from the Ubuntu package library. It also had all firewall and anti virus software disabled and turned off.
After three executions on each machine I got a proper baseline and the results are displayed in the two images included.
Ubuntu 11.10
Windows 7 SP1
It seems that Ubuntu has the upper hand in CPU, Memory and HDD performance and Windows only beat it (although by quite a bit) on 3D performance. (Which I expected as the Nvidia GT330M graphics card seems to always be a sticking point for Linux distro’s)
I was rather surprised by the difference in performance of the CPU and especially the HDD. Since we know that disk performance is always the biggest bottleneck for VM’s then it seems that if you want to squeeze the maximum performance out of your VM’s using Ubuntu as a host OS might just be a good choice.
Running Total using Window Functions in Denali
by Christo on Oct.07, 2011, under Christo Olivier
At the SQLRelay event in London last night Itzik Ben-Gan did a great session on the Window Functions in Denali. This session got me thinking about the benchmarking that I did a while ago for running totals. In this benchmark the Quirky update performed the fastest by far, however using an update statement is sometimes just not feasible in certain scenarios.
The Window Functions in Denali introduces another way to calculate running total and I thought I would give this a try to see how it stacked up against the quirky update.
Here is the code to create the base table:
CREATE TABLE RunningBalance (Ident INT IDENTITY(1,1) PRIMARY KEY,
CustomerName VARCHAR(50),
Value INT,
RunningBalance INT)
DECLARE @Start INT = 1,
@End INT = 1000000
WHILE @Start <= @End
BEGIN
INSERT INTO RunningBalance (CustomerName,
Value)
VALUES(‘Customer1′,1);
SET @Start += 1
END
Since the tests were done on a VM I did a run of the quirky update on the VM and it returned completed in 11 seconds and if we enable the discarding of results after execution in SSMS then the result is 7 seconds.
So next up was the SELECT statement that used the Window Function to calculate the running total.
SELECT Ident,
CustomerName,
SUM(Value) OVER (PARTITION BY CustomerName
ORDER BY Ident ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningBalance
FROM RunningBalance
ORDER BY Ident ASC
This statement returned the results in 14 seconds. Which is fantastic as this includes the time to return the results to the grid control in SSMS.
If we enable the discarding of results after execution to eliminate the amount of time it takes to populate the grid control in SSMS then the SELECT with the Window Function returns in 6 seconds. This is truly great performance for this type of query!
The Window Functions in Denali will definitely be adding some fantastic options for solving some of the rather thorny challenges in previous versions of SQL.
Setting up a virtual BI environment
by Christo on Oct.02, 2011, under Christo Olivier
For anyone wanting to setup a test BI environment and looking for a step by step guide have a look at the following TechNet articles http://technet.microsoft.com/en-us/library/hh223286.aspx The step by step guide has both written instructions and webcasts so you can take your pick of the format you like most.
Remember if you are using virtual machines and you copy them from a base line virtual machine you will need to reset the SID of the copied virtual machines using SYSPREP, ensure you have the Generalize checkbox ticked to generate a new SID.
PerformancePoint Analytic Chart showing unformatted value of SSAS measure
by Christo on Jul.07, 2011, under Christo Olivier
I have recently come across some very peculiar behaviour of the PPS Analytic Chart where it for some reason showed values with a scale of 7 instead of the formatted value which has a scale of 2. To make this even more bizarre when you change the chart type to grid and look at the same data it shows all the values as zero (zero is the expected value in this case) with a scale of 2 (0.00).
In the screenshots below the value should display a flat line of 0. As you can see the chart does not display this but shows some very small value with a scale of 7.
To make things even stranger when you hover the mouse over the chart the tool tip that displays the value for the particular area on the chart displays the measure value of 0.00 and not the value shown on the y axis as can be seen below.
If the same analytic chart is changed by right clicking on it and choosing the Grid report type then the values are shown correctly as being 0.00
So this made me think that for some reason the Analytic Chart is not plotting the formatted values received from the SSAS server but that the Analytic grid does. To ensure that this is the case I ran the MDX query that returned the results with CELL PROPERTIES VALUE to return the unformatted value from the cube. The result confirmed my suspicion as can be seen in the screenshot below.
So PPS is not plotting the formatted value but the actual unformatted value of the measure. When you look at the MDX generated by the Analytic Chart you see that it brings back both the unformatted and formatted Cell Properties and from what I can tell it plots the chart using the unformatted value but uses the formatted value for the tool tip. If you change the MDX generated you lose the interactivity of the Analytic Chart so that is not the solution in this case.
We have only one option and that is to use Currency as the data type in SSAS for the measure, which ensures we will not have the scale issues as it is an exact numeric data type, it does have some great performance benefits too which can be seen in this blog post from the SQL CAT team.
After making this change the PPS Analytic Chart is behaving the way we want it to as can be seen in the screenshot below.
So in conclusion it does look like PerformancePoint has a bug on the Analytic Chart component which in certain cases let it plot the unformatted value of a measure. The one way around this is to change the data type to an exact numeric such as Currency. In this example we could make that change but if we truly needed to use the Double data type for a measure in SSAS then we would have had some serious problems trying to get PPS to display the correct values in the Analytic Chart.
PowerPivot is ‘Rank’
by asadler on Jun.27, 2011, under Front End
Firstly no this isn’t a post dissing what i think is currently the most enjoyable and exciting MS BI tool in the stable. This is about a quick tip to perform a Rank using PowerPivot.
Firstly here’s the main problem:
- PowerPivot doesn’t have a Rank() function – yet (I believe this is coming in v2)
So what do we do about it, well there are a few options:
1. There is a DAX formula work around which requires you to write something like:
COUNTROWS(
FILTER(
ALL(DimCustomer[CustomerName]),
DimCustomer[CustomerSales]
(Values(DimCustomer[CustomerName]))
< DimCustomer[CustomerSales]
&& DimCustomer[CustomerName] <>
Values(DimCustomer[CustomerName])
)
) +1
This can be seen in this excellent post here : http://powerpivotpro.com/2010/03/08/writing-a-rank-measure-and-living-to-tell-the-tale/
2. This (1. above) is a bit complicated, so the workaround to this workaround in 1. above is to basically materialise the ranks into a view and then get Powerpivot to read that view. (apologies for over simplifying here – this solution is actually really very neat and in a lt of scenarios will work really well as a stop gap until v2). This can be seen here: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/11/dynamic-ranking-with-excel-and-powerpivot.aspx
3. I tried to work through both of these (for my particular requirement) then i had that ‘this-is-more-complicated-than-it-needs-to-be’ feeling,
[aside: anyone who is a Douglas Adams fan will know what i mean when i talk about the bird with the mound of decaying leaves in his book “Last Chance to See”]
Anyway, i had a little think, and another glass of wine, then a very obvious thing occurred to me (again apologies if this is soooo obvious that everyone already knows about it and it is only me playing catch up).
MDX has quite a nice Rank function that looks a bit like this:-
Thefore if i could use this Rank with the powerpivot model then that would be cool. So here’s what i did.
- Installed the nice Pivot table extensions add on to pivot tables that enable the easy creation of custom aggs or measures (which can be found on codeplex here http://olappivottableextend.codeplex.com/ – there is even a 64 bit version of this addin – take note Microsoft re the Data mining add ins – no 64 bit version for 3 years debacle – #fail)
- Created a Pivot table against the Powerpivot model (in the standard way)
- Right click on the pivot table and fire up the OLAP pivot table extensions – which can see the powerpivot model (as if it were a cube – kinda)
- write a good ole MDX Rank against the Powerpivot measure i want to Rank on
- Click Add to pivot table
- Job done
** Now i know that this is not an ideal way to do this in this specific example, while we wait for v2, but the principle of being able to use a combination of MDX and DAX in a front end model really got me thinking – and i think there may be lots of interesting uses of this **
Classic OLAP bridging table solution using PowerPivot – 1.
by asadler on Jun.27, 2011, under Front End
I had cause to build a mapping/bridging table solution for a prospect, so i decided to test it out in PowerPivot (for speed of turn around, and ease of messing about), but the solution required the ability for end users to re-map time periods from one time period to another and reflect the aggregated / rolled up data at run time in a pivot table view (through Excel Services).
So i applied the classic OLAP bridging table structure to a very simple set of PowerPivot tables and managed to get a working bridged table – the relationships look like this:
Just need to wait now for PowerPivot v2 – so we can hopefully get data updates scheduled at < 1 day intervals 
.
Next steps for this are to tidy up and look at making this production ready , possibly maintaining the mapping table in a BCS Sharepoint view, and updating the data in the model using the JSOM of Excel services (not sure whether the JSOM of Excel Services can ask Powerpivot server side to refresh – yet).
The simple powerpivot model is attached here.
Calling all Microsoft BI peeps
by asadler on Jun.24, 2011, under Uncategorized
We have immediate availability for BI savvy people to work with us at Hitachi Consulting either on a contract or FTE basis. We have a number of very exciting projects just about to start, using the latest and greatest in the Microsoft stable of BI products. If you are interested please contact me – asadler@hitachiconsulting.com unless of course you are a recruitment agent 
.
SQL Server 2008 R2 Analysis Services Operations Guide
by Christo on Jun.02, 2011, under Christo Olivier
The SQL Server 2008 R2 Analysis Services Operations Guide has been released and is a must read for any SSAS professional. It can be downloaded at the following link http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SSASOpsGuide2008R2.docx
BI – Labs from MS
by admin on May.15, 2011, under Mark Hill
Title say it all really , some cool stuff on here.
http://www.microsoft.com/bi/en-us/Community/BILabs/Pages/Home.aspx
Create a Last 30 Weekdays Named Set in SSAS
by Christo on Apr.27, 2011, under Christo Olivier
One of the most common Sets to be create is the “Last N Days” type of set which is normally just the last 30 or last 60 days from the current day. Another common request is for a Last N Working Days or Weekdays. Lets have a look at how we can create a Named Set that will always provide the last 30 Weekdays using the Adventure Works cube solution.
Lets have a look at the MDX statement that will do the work for us.
Tail
(
Extract
(
Exists
(
{NULL
:
Exists
(
[Date].[Date].[Date].MEMBERS
,[Date].[Is Current Day].[True]
).Item(0)
}
,[Date].[Is Weekday].[True]
)
,[Date].[Date]
)
,30
)
Lets break the MDX down into the main parts and explaining each step in the above statement.
Tail
(
Extract
(
Exists
(
{NULL
:
Exists
(
[Date].[Date].[Date].MEMBERS
,[Date].[Is Current Day].[True]
).Item(0)
}
,[Date].[Is Weekday].[True]
)
,[Date].[Date]
)
,30
)
The highlighted piece of MDX above will get the current day member from the Date attribute hierarchy in the Date dimension. The method used here is described in detail in a previous blog post here. This can be replaced with any MDX that will return the Current Day member.
Tail
(
Extract
(
Exists
(
{NULL
:
Exists
(
[Date].[Date].[Date].MEMBERS
,[Date].[Is Current Day].[True]
).Item(0)
}
,[Date].[Is Weekday].[True]
)
,[Date].[Date]
)
,30
)
The above highlighted part of the code will create a set containing all the members of the Date attribute hierarchy in the Date dimension before and up to the current day (including the current day).
Tail
(
Extract
(
Exists
(
{NULL
:
Exists
(
[Date].[Date].[Date].MEMBERS
,[Date].[Is Current Day].[True]
).Item(0)
}
,[Date].[Is Weekday].[True]
)
,[Date].[Date]
)
,30
)
Next we use the Exists function highlighted above to return only the members that are weekdays. This uses an attribute, “Is Weekday”, on the Date dimension that has a True or False as value similar to the “Is Current Day” attribute.
Tail
(
Extract
(
Exists
(
{NULL
:
Exists
(
[Date].[Date].[Date].MEMBERS
,[Date].[Is Current Day].[True]
).Item(0)
}
,[Date].[Is Weekday].[True]
)
,[Date].[Date]
)
,30
)
Our second last part of the MDX expression is highlighted above. The Extract function is used to only return only the tuples from the Date attribute hierarchy from the current set.
Tail
(
Extract
(
Exists
(
{NULL
:
Exists
(
[Date].[Date].[Date].MEMBERS
,[Date].[Is Current Day].[True]
).Item(0)
}
,[Date].[Is Weekday].[True]
)
,[Date].[Date]
)
,30
)
The last part of the MDX is to use the Tail function to get the last 30 tuples and thus create a set that contains the last 30 weekdays.
Since the Adventure Works demo DW does not have data for 2011 in it I forced the 31st of August 2004 to be my current day in the cube. The result of using the above MDX in a query is shown below.
All that is left is to add the MDX to a Named Set in the Adventure Works cube in BIDS.
This method of creating an attribute that indicates if a specific date is a weekday can be modified and used in many different ways to create sets containing specific tuples.
