Neudesic Blogs

Using Virtual Machines in Windows Azure

This post is an attempt to summarize features and issues in using Virtual Machines in Windows Azure platform.

Virtual Machine in Cloud:

Cloud environments in both private and public networks are generally built upon a huge network, CPU, RAM, and storage resources distributed across datacenters. However, this platform appears to be a single computing environment to end users. We generally virtualize all physical servers in cloud computing environments for maximum resource utilization.

Virtual machine in cloud shares resources of a physical server among several virtual environments. This concept allows one machine to pretend to be several computers of different configurations. Obviously, the constraint is that we can only share the maximum CPU and memory resources allocated to the machine.

Windows Azure Virtual Machines:

With the launch of Infrastructure as a service, windows azure offers a wide range of virtual machines with full control and extensive management access to its users.

 

 

It allows us to create virtual machines in our Azure accounts using our own Windows or Linux server images. Alternatively, we can select from a list of readily available images in the Windows Azure gallery. This helps ease the process of moving existing applications and infrastructure to the cloud by eliminating the need to modify existing applications.  This could obviously save a lot of time and money to enterprises. 

With full administration access, we can remotely connect to these machines and work in the same way we work on our on-premise servers.

Why to use Azure VM:

Using VMs in Azure will enable us to create our servers or server infrastructure in the cloud where we can scale our infrastructure up and down based on business requirement changes. As we are completely utilizing cloud resources from Microsoft, customers don’t need to invest bulk amounts on creating IT infrastructures which will be highly beneficial to starting and mid-range enterprises.

With the ability to remote desktop (RDP) to cloud servers, we can work on Azure VMs in the same fashion that we work with our on-premise servers. We are free to install/uninstall/copy/delete any of our applications or data.

If customers are already using Microsoft applications in their on-premise servers, we can seamlessly move them in to the cloud using VMs without any investments in application changes.

This provides us application mobility very easily by moving virtual machine hard disks (VHD) back and forth between on-premise and cloud environments.

Starting with Azure:

To start exploring cloud computing features, Windows azure offers a 90 day free trail to every new customer with all available offerings in Azure. This helps us to experiment or evaluate Azure offerings and features. There is no obligation to continue with your Azure account at the end of free trail. We are free to cancel our subscription anytime during trail period.

Though we are free to use Azure capabilities during the trail period, there is an upper limit set for each azure feature as listed below.

 

Feature	Upper limit for trail period
Compute	750 small compute hours per month
Websites	10 websites
Mobile Services	10 mobile Services
relational database	1 SQL database
SQL reporting	100 hrs per month
Storage	35 GB with 50,000,000 storage transactions
data transfer	unlimited inbound and 25 GB outbound
media services encoding	50 GB
Content Delivery Network (CDN)	20 GB outbound with 500,000 transactions
Cache	128 GB
service bus	1500 relay hours and 500,000 messages

 

Here, we need to have some deeper understanding about compute hours to avoid surprises while using it. Compute hours start to accumulate as soon as we deploy an application or VM. This means that we are accountable for billing as long as something deployed in our account.

Also, compute hours are calculated on a per core basis. This means that if we use a VM of medium size, the sum of compute hours will be 375 hours as it contains two cores. See the example below to help better understand metering on compute hours.

If we deploy 2 medium VMs (2 core) for four hours, it would be 2*2*4=16 hours of compute time.

Billing method for Azure VMs:

Billing will always get priority from customer prospective while opting for any new services in IT. Obviously we should show them better ROI than their existing infrastructure investments for any shift to take place. Azure offerings also are no different from this.

Billing rates in azure VMs vary based on the size of the VM. Charges cover windows server licencing costs. To minimise expenses, it is best to use a machine size that meets our minimum hardware requirements and increase the size later when required. We can use Pricing calculator in the Azure portal to evaluate pricing costs based on our requirements.

Below is VM Pricing list as of January 31, 2013. Since VMs are still in preview stage, we’ll get 33% discount on this pricing until the feature becomes general availability.

Instance Size	No. of CPU Cores	RAM size	Windows Server Price  (per compute hour)	Non-Windows Server Price  (per compute hour)	SQL Server Price (per compute hour)
Extra Small	shared	768 MB	$0.02	$0.02	N/A
Small	1	1.75 GB	$0.08	$0.085	$0.55
Medium	2	3.5 GB	$0.16	$0.17	$0.55
Large	4	7 GB	$0.32	$0.34	$0.55
Extra Large	8	14 GB	$0.64	$0.68	$0.10

 

Note: Compute hour charges do not include any Azure storage costs associated with VM image files which will save in Azure storage account.

 

Storage capacity is billed in units of the average daily amount of data stored (in GB) over a monthly period for Drive storage. For example, if we consistently utilized 10 GB of storage for the first half of the month and none for the second half of the month, we would be billed for the average usage of 5 GB of storage. Changes are at $0.95 per GB for first 1TB usage and are subject to change.

Data transfer charges:

All inbound data transfers (i.e. data going into Window Azure data centers) are free. The price for outbound data transfers (i.e. data going out of Windows Azure data centers) is based on a graduated, region-specific scale detailed below.

OutBound Data Transfer	US & Europe Pricing (Per GB)	Asia Pricing (Per GB)
First 10 TB / Month	$0.12	$0.19
Next 40 TB / Month	$0.09	$0.15
Next 100 TB / Month	$0.07	$0.13
Next 350 TB / Month	$0.05	$0.12

 

Issues we faced while creating azure VM:

Recently, Chris Myers and I had the opportunity to set up an Azure VM under the supervision of Rick Garibay using BizTalk 2013 image from the gallery. This was for one of the market leaders in entertainment domain. As a result of it still being in preview stage, it was, unfortunately, not as smooth of a ride as we had hoped for. We came across couple of issues in setting up end-to-end BizTalk application on this server for customer demo. Good thing is that, we can solve all the issues just like we do in on-premise machines as we get full control on VM. We are trying to cover most of them in this blog.

IIS server not readily available:

The first thing we noticed after creating VM was that the IIS server is not readily configured out-of-the-box with the BizTalk 2013 template. We needed this functionality in order to demo BAM portal features, as well as a front-end web application, to our customer.

We ended up needing to configure IIS manager using the “Turn Windows features on or off” wizard. You most likely have performed this operation in the past on a development machine.

Since the IIS manager was not available, thus the BAM portal was also not readily available. After configuring IIS, we ran the BizTalk configuration wizard in order to configure the BAM tools and portal.

WCF LOB adapters not installed in VM:

The next thing we noticed was that the LOB adapter pack was not installed in the VM. This was crucial, since we extensively utilized WCF adapters in our BizTalk app for both SQL and other application interactions. We searched the VM’s disk for the necessary install, but had no luck finding it.

We also didn’t see any LOB adapter pack download links from MS for the BizTalk 2013 beta version. Eventually, we downloaded the BizTalk 2013 Beta version installation files and extracted the ASDK_x64.zip, ASDK_x86.zip, AdapterPack_x64.zip, and AdapterPack_x86.zip files from setup files. Last, but not least, we copied and installed the files to the BizTalk VM in order to bypass this hurdle.

Performance with small core configuration:

Initially we created the VM with a core size of small, which comes with 1.75 GB of RAM. We deployed the BizTalk application to the VM and started testing our solution using both SOAPUI and a web application deployed that was deployed to IIS. We observed that the machine was very slow to respond for any action. At least, it was not ready for client demo.

We then decided to upgrade machine configuration to medium core size which comes with 3.5 GB of RAM. It is extremely simple to upgrade the machine configuration as depicted in the walkthrough below.

<!--[if !supportLists]-->1.     1 . <!--[endif]-->Login to https://manage.windowsazure.com portal and click on the “virtual machines” tab on the menu on the left hand side. Click on the respective VM link that we want to upgrade the configuration for.

 

 

 

2. Click on the “Configure” link top menu of next page as shown below.

 

 

3. We can choose VM the size from available options in next page as shown below.

 

 

 

 

We need to keep the following points in mind while we are doing this to avoid later surprises / challenges:

1. Changing the configuration will require a VM restart before the changes will be effective.

2. While restarting, all temporary disk (D drive) data will get deleted permanently as it will not get persisted to storage disk. So we have to copy important data from D drive to C drive before doing this.

3. Changing to medium core size will reduce compute hours to 375(750/2) which means our trail account will get disabled after 15 days of usage. 

 

 

Visual studio is still RC version:

As per Brian Harry’s comments in MSDN blogs, VS 2012 RC was ceased to function from January 15th and there is no workaround to extend this for usage. However, the BizTalk image in the gallery comes equipped with  the VS 2012 professional RC version. Clearly, this reflects a problems as we will not be able to use it.

We have to provide the existing version with a license or extend the 90 day trail period version in order to make use of it.

Office tools are not available:

The project that we used the VM for required BAM capabilities. Unfortunately, I quickly found, when I tried to open the BAM Excel View files, that we are unable to do so. After searching around online,  I learned that MS office is not licenced to run on a VM from below FAQ in MS forums.

Q: Can customers run Microsoft Office and Windows 7 Client on Virtual Machines?

A: No.  Under the Microsoft Product Usage Rights (PUR), Office and Windows 7 are not licensed to run on Virtual Machines.  In particular, Microsoft Office is classified in the PUR as “Desktop Applications”, which is not included in Licensing Mobility.

 

 

Spending Limit and disabling services:

To avoid any unexpected billing, there is spending limit feature available in azure accounts. We can set this spending limit to any billing amount. At the point of reaching spending limit, our subscription will be disabled for remaining period of billing cycle.

This action removes any hosted services that have been deployed in the account. The data in storage and databases will be accessible in read-only manner. Deployed VMs will be removed, but VHD files of VMs will remain in storage account. Subscription gets re-enabled at the beginning of next billing cycle and we can re-deploy hosted service(s) back and will have full access to your storage accounts and databases. We can restore VMs back from VHD discs in storage account.

For all new customers signing up for a 90 day trail period, spending limit will be set to $0 by default.  We can remove this spending limit any time if we desire.  In doing so, our services will not get disabled even after reaching upper limits. However, as you may expect, removing the spending limit could result in charges. One thing to keep in mind is that once removed, spending limit feature cannot be re-enabled.

 

Deleting Virtual machines from Azure account:

Deleting virtual machine from Azure account will not delete all the resources associated to it. This action will only detach underlying VHD file from VM usage. This VHD file of VM is a blob in Azure storage account registered as disk object and attached to a VM when created. This object will still remain in storage account after deleting VM from Azure account.

To delete virtual machine along with related resources, we first need to delete virtual machine instance, delete respective cloud service created after deleting VM and then delete respective disks and VHDs.

Screenshot showing delete action:

 

 

 

 

 

 

 

To delete cloud service:

 

 

Below screenshot shows deleting disks from Azure account after deleting VM and cloud service. It’ll take a while to enable deletion button after removing VM. As we see in below screenshot, we can either delete or retain associate VHD while deleting disk by choosing respective option on delete.

 

 

 

BizTalk Server 2010 EDI/AS2 Gotchas

When you are Configuring the BizTalk EDI/AS2 Runtimes for BizTalk Server 2010 you have to watch out for some unique features that can cause a big headache.  Here are some of the things you have to be aware of before you start configuring the BizTalk Server for EDI/AS2.

1. BizTalk EDI/AS2 Runtime will only work in 32bit mode.  You have to make sure that the In-Process Host and Instance is set for 32-bit only
   
2. If you are wanting to Enable BizTalk EDI/AS2 Runtime Status Reporting you will need to make sure BAM is configured
3. In a Multi-Server environment make sure that the SQL Server and the App Server have the account that will be used for the Host in the Users group (otherwise the installation will fail because of login errors)
4. When you are installing the BizTalk EDI/AS2 Runtime feature it needs to be done at the same time the BizTalk Runtime is created.  If you do not do this then the installation will give you an error that the SQL Adapter could not use the Host Instance and needs the Host Instance to be recreated for the Adapter.

I hope this helps everyone when installing the EDI/AS2 Runtime for BizTalk Server 2010.

Desert Code Camp - ASP.Net Web API from Top to Bottom

Thanks to all who attended my session on ASP.Net Web API at Desert Code Camp!  Here are the bits as promised. 

Demo.Corp.Customer.zip (13.42 mb)

Modeling Several Boolean Attributes Using a Junk Dimension

One of the discussions that I recently had involved modeling a business process associated with several Boolean attributes. The current model addressed each of these attributes as a separate dimension. While there is nothing necessarily wrong with this approach, when reviewing a model, I always try to think the way Ralph Kimball and colleagues at the Kimball Group would think. If you’re not modeling the way that they would model that process, you probably should re-evaluate and redesign.

So how would the Kimball Group model this scenario? The answer is most likely with a Junk dimension since a Junk dimension consolidates several low cardinality attributes into a single useful dimension. Since all of these attributes have only two possible values, either Yes or No, seems like a very likely candidate for a Junk dimension!

There’s several advantages to modeling this scenario with a Junk dimension. Below are three of them

(and you’re always free to disagree)

1. Fact table contains a single field rather than a whole bunch of columns to refer to these flags. More on that later.
2. Greater flexibility on defining the labels of each attribute, especially when utilized with a view layer abstraction between the source and the user presentation layer, and
3. Easier to enhance the cube should additional Boolean attributes be identified later in the project lifecycle.

Defining the Junk Dimension

While in most cases, the key for a dimension is a meaningless number and assigned by an Identity or Sequence, in the case of a Junk dimension consolidating several Boolean attributes a better approach is to calculate this key. For instance, if I’ve consolidated four Boolean attributes, I can calculate this key by multiplying the value of the Boolean by a corresponding power of 2, either 2⁰, 2¹, 2², or 2³ depending on the identity of the attribute. A T-SQL statement that defines a table consolidating four Boolean attributes is given below.

WITH cteFlags AS (
    SELECT    0 AS Value
    UNION ALL
    SELECT    1
)
SELECT    
    (
        Power(2, 3.0) * Flag3.Value 
     +  Power(2, 2.0) * Flag2.Value 
     +  Power(2, 1.0) * Flag1.Value 
     +  Flag0.Value
    ) AS ID, 
    Flag0.Value,
    Flag1.Value,
    Flag2.Value,
    Flag3.Value
FROM 
    cteFlags Flag0
CROSS JOIN 
    cteFlags Flag1
CROSS JOIN 
    cteFlags Flag2
CROSS JOIN 
    cteFlags Flag3
ORDER BY
    ID

The ETL to load fact tables is now slightly more complex. Rather than each dimension being represented as separate columns, a single column is used. But since the surrogate key for the Junk dimension is not incrementally assigned but coded based on business domain values, this value can be calculated rather than being looked up based on the individual column values.

Greater Flexibility

One of the problems with inclusion of each Boolean attribute as a separate dimension is that when modeled as a role-playing dimension in a multidimensional database, you’ll end up with a whole bunch of Yes and No or True and False entities. When querying this model, it’s not too difficult to get completely lost in the meaning of each of those values. Using a Junk dimension, one can easily manipulate the labels or names applied to each of those entities. So the True flag applied to the Base Fee Schedule Overridden? attribute can be changed from Yes to Base Fee Schedule Not Used or some other description more in line with the business domain.

Easier to Enhance

Another advantage is the ease in which additional Boolean attributes can be added to an existing data mart. Most of the values for these attributes are likely to be False for the vast majority of cases as these attributes are generally used to identify exceptions. Therefore, by simply adding another column to your Junk dimension with False as its value you’ve already accurately included the additional attribute for the vast majority of fact records without even touching the fact table. How Agile is that?

To complete inclusion of the exceptions, there are two steps that need to be executed. First, new rows need to be added to the Junk dimension. These can be simply added by appending the true values for the additional attribute to the existing Junk dimension records. To illustrate using the previous case, the query defining new rows to the Junk dimension created above would simply be the following:

WITH cteFlags AS (
    SELECT    0 AS Value
    UNION ALL
    SELECT    1
)
SELECT    
    (
        Power(2, 4.0) * New_Attribute.Value
     +  Power(2, 3.0) * Flag3.Value 
     +  Power(2, 2.0) * Flag2.Value 
     +  Power(2, 1.0) * Flag1.Value 
     +  Flag0.Value
    ) AS ID, 
    Flag0.Value,
    Flag1.Value,
    Flag2.Value,
    Flag3.Value,
    Flag4.Value
FROM 
    cteFlags Flag0
CROSS JOIN 
    cteFlags Flag1
CROSS JOIN 
    cteFlags Flag2
CROSS JOIN 
    cteFlags Flag3
CROSS JOIN 
    cteFlags New_Attribute
WHERE    
    New_Attribute.Value = 1
ORDER BY
    ID

With the new dimension records added, the second step is to update the fact records identified as “exceptions”. Again, as opposed to a complete reloading of the fact table to include the additional attribute, the Junk dimension key value for these exceptions can be updated accordingly. One way to complete this task is to load a temporary staging table that has the identifying fact fields for all records with a true New_Attribute value. By joining to this table and applying the following update conditions to the corresponding records,  JunkDimensionKey = JunkDimensionKey + 2⁴, you’ve now successfully included a new Boolean attribute into the model. If there are many records where the New_Attribute value is true and your fact table is very large, your best approach would likely be to use a technique that I described in a previous blog post nearly two years ago.

SSRS–Creating the Stephen Few Data Visualization

In my last blog post, I described an approach where the DateTool dimension of Marco Russo was extended to include an Aggregate Month attribute. When referenced in a query in combination with a member of the Aggregation attribute in an MDX query, the Aggregate Month attribute produces the time periods that compose that aggregation. Enhancing your SSAS multidimensional cube in this manner provides functionality that is similar to the Time Intelligence functionality available in PerformancePoint Services, sort of a way of providing dynamic date specific named sets. I feel they’re advantageous for several reasons but I’m a bit on the biased side. For one thing, these “dynamic time-based sets” such as the last twelve months prior to the selected month will be available in any front-end and not just PPS. And as referenced in this SSAS forum post, this approach was successfully used to provide a “Date Sets Alternative” in Tableau.

The original purpose of the enhancement was to facilitate creation of the Stephen Few sales performance dashboard data visualization from Information Dashboard Design. The goal was to make creation of the visualization simple enough that it could be created in ReportBuilder. The end result using the modified Adventure Works source will end up looking something like the following.

In the visualization above, the Year-Over-Year YTD Reseller Sales Growth is shown by Country. For this demonstration, a targeted YTD YoY growth of 15% is expected. Any YoY growth rate that is 0.85 or below is considered poor while any value between 85% and 115% was considered Satisfactory as indicated by the linear ranges of the bullet graph shown in the fourth column of the visualization.

To create this visualization, the following steps need to be executed.

1. Create a single dataset to source all elements,
2. Create a Tablix control bound to the dataset with a Grouping on Country,
3. Add Sparkline, Bullet Graph, and Indicator controls, and
4. Add the legend and scale adornments to the visualization.

Creating the Dataset

Having made the modifications to the Adventure Works cube described in the previous blog post, creating a single dataset to source all data visualization components is simplified greatly. For each Country shown in the diagram above, the dataset produced will need to include the following data elements.

1. Reseller Sales Amount by month for the Past 12 Months,
2. YTD Reseller Sales (horizontal bar in bullet graph above, fifth column, and Indicator value),
3. Prior Year YTD Sales to calculate target value (vertical line in bullet graph above), and
4. Columns that represent the  boundary values for the linear ranges defining the bullet graph.

The challenge in creating such a dataset is that the date context and date aggregation are at different grains within the same query. To create the dataset, first define a data source to the Adventure Works cube. Next, create a new dataset and in the Query Designer window, drag the [Date].[Month] and [Date Utility].[Aggregation] attribute hierarchies to the Filter Pane. Check the Parameters checkbox for the [Date].[Month] attribute so that consumers can dynamically change the filter context and specify [Last 12 Months] as the filter for the [Date Utility].[Aggregation] filter. [I also added a Filter to limit Countries to only Canada, France, United Kingdom, and the United States only because the Adventure Works cube is pretty sparse] The initial dataset was defined by dragging the [Geography].[Country], [Date Utility].[Aggregate Month], and the[Measures].[Reseller Sales Amount] to the Data Pane. Now you have a query produces the Reseller Sales Amount by Country for the Last 12 Months from whatever selected month the User selects to run the report. How easy was that?

To complete the query, the Actual, Goal, and boundary values for each of the linear ranges need to be added to the query. Since the Aggregation context differs from the [Last 12 Months] created as a filter, calculated members will be created to overwrite this context. These calculated members are created in the Calculated Members Pane of the designer and have the names and definitions as given in the following table.

Two additional points should be specified that were left out of the Definition column above. As the Value and Goal measures overwrite the current context of the [Date Utility].[Aggregate Month] attribute, these metrics were wrapped in an IIf function so that every member of the [Date Utility].[Aggregate Month] hierarchy was returned. (i.e. IIf(([Measures].[Reseller Sales Amount], [Date Utility].[Aggregate Month].CurrentMember) = 0, NULL, …..) ) Additionally, since the constants as defined will also return every member of the [Date Utility].[Aggregate Month] hierarchy, these constants were multiplied by [Measures].[Reseller Sales Growth Value]/[Measures].[Reseller Sales Growth Value] so that NULLs were returned for months outside of the Last 12 Months range.

Defining the Tablix Control

Adding the Tablix control is the simplest step in the process. Insert a Table control to the report and set the DatasetName property equal to the Dataset created above. Add two additional columns to the table, create a grouping on Country (and delete the row matrix column created without deleting the group), and delete the Details section from the report. Bind the third column to the Country field and define the fifth column to the Max([Reseller Sales Growth Value]). The last little bit of formatting involved setting the BorderStyle to None for all textboxes except for the bottom border of the column headers. That’s it. The framework for the data visualization is now defined and now the more difficult process of defining the data visualizations begins.

Creating the Visualizations

Insert a Sparkline in the first column of the table. Set the Category Groups to Aggregate Month and the Values to the Reseller Sales Amount. That’s it for that.

Defining the Indicator is not much more difficult. The only thing to watch out for is that the expressions for Values and States should use the Max, Min, or Avg aggregations of Reseller Sales Growth Value and Reseller Sales Growth Goal rather than the default value of Sum. Once completed, four out of five columns are now there. The only one left is the bullet graph.

Rather than detail how the bullet graph was formatted, most of the steps that I followed are described in detail in Tim Kent’s post from 2009 without all the jiggery-pokery that was required in previous versions of SSRS. The linear ranges were defined using the Max value of the dataset columns explicitly created for that purpose. The linear ranges were defined to have a start and end width of 75% while the solid fill colors for the Poor, Satisfactory, and Good ranges were defined to be Gray, Silver, and WhiteSmoke respectively, providing the contrast necessary to distinguish in the figure shown previously. That’s it for the visualizations.

Completing the Data Visualization

To complete the process, legends and scales were added to the Tablix. The legend is the simpler task. The rightmost report header columns were merged and a rectangle control was inserted to allow for free-entry into that cell. Three additional small rectangles and three textboxes were added to identify the Poor, Satisfactory, and Good linear ranges. The BackGroundColor property for each of the rectangles was set to the appropriate color as identified above.

The scale was added by inserting a Country group summary row and adding another bullet graph to the fourth column of that row. The width properties for all linear ranges and pointers was set to zero so that these elements of the Gauge control were effectively not displayed. Trial and error techniques were used to determine the optimal Scale properties. The most likely change that will be necessary is to increase the Font size of the Scale to an absurdly large amount. For the visualization shown above, the Font size ended up being 48pt.

The completed report is available on my Skydrive account.

Summary

The greatest challenge in replicating the Stephen Few data visualization with SSRS is the creation of a single dataset in which all visualizations can be sourced. By enhancing the DateTool dimension of Marco Russo to include an additional Aggregate Month attribute, the enhanced Date Utility dimension discussed in the previous blog post greatly simplifies the necessary steps.

Enhancing the DateTool Dimension

I had this issue in the past and addressed it in a very inelegant manner involving a many-to-many relationship that I won’t reproduce here. For all you poor souls that inherited that approach, I sincerely apologize. I was young (not really) and inexperienced (also, not really) and knew when I implemented it, it was not the right way (definitely true). However, the users liked it, needed it, and once deployed, it was really difficult to retract.

The scenario that I’m addressing here has its roots in that same place. Say that I want to reproduce the Sales Performance Dashboard data visualization that Stephen Few presents in the last chapter of his book, Information Dashboard Design, within a SQL Server Reporting Services Tablix control. An example of this data visualization can be found here. The visualization includes a Sparkline, an Indicator, and a Bullet Graph for various different financial performance metrics. The difficulty in reproducing this visualization in SSRS is that all data used to generate these visualizations must be included in the same dataset. While the indicator and bullet graph are populated by Year-to-Date values, the Sparkline is sourced by current month values over the last twelve months. Therefore, two different date aggregation contexts, Year-to-Date and Last 12 Months, and two different date contexts, the selected month for the Bullet Graph and Indicator, and the component months for the Sparkline need to included within the dataset sourcing the visualization.

To produce a dataset that could be used to generation this data visualization, the DateTool dimension described by Marco Russo in this blog post was enhanced. The dimension will be referred to as the [Date Utility] dimension in the remainder of this blog post. These enhancements included adding new utility attributes to represent the component members involved in a date aggregation. To illustrate using the Adventure Works cube, if [Date].[Calendar].[May, 2008] and [Date Utility].[Aggregation].[Last 12 Months] are specified in the slicer, including the newly added [Date Utility].[Aggregate Month] attribute to the row axis would generate a set equivalent to [Date].[Calendar].[Jun, 2007] : [Date].[Calendar].[May, 2008]. This set represents the time periods that compose the ([Date].[Calendar].[May, 2008], [Date Utility].[Aggregation].[Last 12 Months]) calculated cell with the current cube context.

The first modification to Russo’s Date Utility dimension was to simply Cross Join the [DateTool].[Aggregation] and [DateTool].[Comparison] views with another view that included each possible [Date].[Calendar].[Month] member. (For this case, detail at the granularity of the Day level was not needed.) Even though the approach involves cross joining three views, each has a relatively small number of possibilities. The definition of the [DateTool].[CalendarMonths] and [DateTool].[DateTool] views are given below.

CREATE VIEW [DateTool].[CalendarMonths] AS
	SELECT	
		dt.MonthKey,
		dt.MonthDesc,
		dt.MonthStartDate,
		dt.QuarterKey,
		dt.QuarterDesc,
		dt.QuarterStartDate,
		dt.SemesterKey,
		dt.SemesterDesc,
		dt.SemesterStartDate,
		dt.Year
	FROM	DateTool.DimDate dt
	WHERE	dt.DateValue		= dt.MonthStartDate 
GO
CREATE VIEW [DateTool].[DateTool] AS
	SELECT	
		a.ID_Aggregation, 
		s.ID_Comparison,
		m.MonthKey, 
		(
			CAST( ID_Comparison AS VARCHAR ) + ' - ' 
			+ CAST( ID_Aggregation AS VARCHAR ) 
			+ ' - ' + CAST(m.MonthKey AS varchar)  
		) AS Description
	FROM	DateTool.DateAggregation a
	CROSS JOIN DateTool.DateComparison s
	CROSS JOIN DateTool.CalendarMonths m
GO

The second modification to Russo’s Date Utility dimension was to add an Aggregate Year –> Aggregate Quarter –> Aggregate Month natural hierarchy to the [Date Utility] dimension. A Business Information Model of the Date Utility dimension is shown below

The last modification is to define MDX script calculations to return cell values for the Aggregate Month, Aggregate Quarter, and Aggregate Year attributes. These additional calculations are placed between the script defining the Aggregation members and the script defining the Comparison members. This placement allows an aggregated comparison to also be decomposed into its constituent time periods.

The entire solution (at least with the Aggregate Month calculations defined) is available from my Skydrive account. To describe the approach, the definition of the subcube defined by the [Date Utility].[Aggregation].[Last 12 Months] member and [Date Utility].[Aggregate Month].[Aggregate Month] level will be described as shown in the code below. Only when a single member of the [Date].[By Calendar].[Month] dimension is included in the current context will a non-NULL value be returned. If that check passes, the next check is to determine if the current member of the [Date Utility].[Aggregate Month] attribute is within the last 12 months of [Date].[By Calendar].CurrentMember using an Intersect function call. If so, a tuple is returned that assigns the equivalent of LinkMember( [Date Utility].[Aggregate Month].CurrentMember, [Date].[Month] ); otherwise, a NULL value is returned.

SCOPE( 
    [Date Utility].[Aggregation].[Last 12 Months], 
    [Date Utility].[Aggregate Month].[Aggregate Month] 
);
    THIS = IIf(
        COUNT(EXISTING { [Date].[By Calendar].[Month].Members } ) = 1,
        IIf(
            Intersect(
                { [Date Utility].[Aggregate Month].CurrentMember },
                { 
                    LinkMember( 
                        [Date].[By Calendar].CurrentMember, 
                        [Date Utility].[Aggregate Month] 
                    ).Lag(11)
                    :
                    LinkMember( 
                        [Date].[By Calendar].CurrentMember, 
                        [Date Utility].[Aggregate Month] 
                    )
                }
            ).Count > 0,
            (
                LinkMember( 
                    [Date Utility].[Aggregate Month].CurrentMember, 
                    [Date].[Month] 
                ),
                [Date Utility].[Aggregation].DefaultMember,
                [Date Utility].[Aggregate Month].DefaultMember
            ),
            NULL
        ),
        NULL
    );
END SCOPE;

Lastly, to demonstrate its application, consider the following MDX statement which includes a single member of the [Date].[By Calendar] hierarchy and a single member of the [Date Utility].[Aggregation] attribute in the WHERE clause.

SELECT    
    {
        [Measures].[Internet Sales Amount]
    } ON COLUMNS,
    NON EMPTY
    {
        [Date Utility].[Aggregate Month].[Aggregate Month].Members 
    } ON ROWS
FROM    
    [Adventure Works]
WHERE
    (
        [Date].[By Calendar].[May, 2008],
        [Date Utility].[Aggregation].[Last 12 Months]
    )    

Including the [Date Utility].[Aggregate Month] attribute on the ROW axis returns the [Date Utility].[Aggregation] constituent members as shown below

Aggregate Month	Internet Sales Amount
Jun, 2007	$554,799.23
Jul, 2007	$886,668.84
Aug, 2007	$847,413.51
Sep, 2007	$1,010,258.13
Oct, 2007	$1,080,449.58
Nov, 2007	$1,196,981.11
Dec, 2007	$1,731,787.77
Jan, 2008	$1,340,244.95
Feb, 2008	$1,462,479.83
Mar, 2008	$1,480,905.18
Apr, 2008	$1,608,750.53
May, 2008	$1,878,317.51

While this query doesn’t really illustrate why you would include an Aggregate Month enhancement, in a future post, use of the enhanced Date Utility dimension will be used to show how the Stephen Few data visualization including an Indicator, a Bullet Graph, and a Sparkline can be generated using Report Builder.

Getting Nerdy with Workflow: Present and Future

I am very excited about PCSUG upcoming talk on Azure Workflow 1.0 with Dave Cliffe, Program Manager on the Windows Azure team!  Should be a great discussion and always a treat to talk with someone directly from Microsoft.  Details about the event can be found at the PCSUG web site listed below. 

http://www.pcsug.org/Home/Events

Please sign up for the event as well through Eventbrite so we know you will be there!

http://pcsug.eventbrite.com/

Phoenix Connected Systems User Group vNext

The only consistent thing is change.  After an excellent run of four years heading up the Phoenix Connected Systems User Group, Rick Garibay moves to a support role and I will take over as President with Chris Myers serving in the Vice President role. I am very excited about the opportunity especially as the Microsoft integration platform has become more diverse than ever before.  Open source solutions for messaging and new opportunities for mobile device integration has also grown giving the group plenty to talk about for the foreseeable future.

So what does vNext look like?  Our original charter over four years ago was to focus on scenario based discussions applying core technologies like WCF, WF and BizTalk.  Imperative versus declarative style of development was a big focus.  That message is still very important and hopefully will evolve to bring in many of the new technologies emerging to support an increasingly distributed and connected world. Like many user groups, sometimes we focus on scenarios and applying the technology and sometimes it is just showing cool technology and letting you decide how you might use it.

The technology landscape has changed quite a bit from the start of the group.  I found it very interesting to look through the topics dating back to 2008 and watched the content evolve with the industry.  We plan to continue this trend trying to keep up with the latest and greatest.  So what does that technology stack look like today? Here is a start at a list that is sure to grow:

• WCF
• WF
• BizTalk 2010 R2
• BizTalk PaaS
• Service Bus
• Azure Workflow 1.0
• ASP.Net Web API
• Access Control Service
• Windows Server AppFabric
• Azure Caching
• RabbitMQ
• NServiceBus
• SignalR

Many times we also like to get into supporting technologies like Git, No Sql Databases (MongoDB, RavenDB, etc) and Entity Framework.  We generally don’t discriminate against technologies in the group.

The group will still meet at the Microsoft office in Tempe on the first Wednesday of every month.  More details about the group and upcoming events can be found on our website.

http://www.pcsug.org/

The group is driven by the community and can only be successful with your participation!  Whether speaking on these new technologies or attending so we have someone to listen (and eat the free pizza), this does not work without your support!