Data Otaku

Presenting Actuals and Forecast Concurrently in a Write-Enabled Cube

2013-01-26T18:00:00Z

I have written a series of entries on writeback applications and wanted to add this last entry highlighting a common cube design challenge associated with these. Quite often with write enabled cubes, we enter forecasted data for future periods. When those periods come to pass we bring in actual (historical) data into the data model while holding onto our forecasts for these periods. And while its easy enough to present forecasted values side-by-side with actual values, we often wish to look over longer spans of time at a single set of values some of which represent actuals and others of which represent forecasts.

To illustrate this, consider a utilization model for employees in a consultancy. In this model, we forecast the number of hours we expect each employee to work over the weeks of a year with the objective of understanding the utilization percentage those employees will obtain. (In a consultancy, maintaining individual and organizational utilization rates (percentages) are critical for ensuring profitability and avoiding employee burn-out and turn-over.)

As we look at an employee's utilization potential utilization rate for a given year, we have some weeks for which we must forecast values and other weeks for which the employee has submitted his or her actual values. The end of the year utilization rate represents a combination of forecasted and actual values.

In our cube, we typically keep forecasted and actual values separated by either employing two separate measure groups, i.e. one for actuals and another for forecast, or by recording both sets of values in a single measure group by assigning actuals to a scenario member in a Scenario dimension that is reserved just for actuals. In the sample database I've provided with this post, I've used to former approach to keep things a bit more simple.

To present actuals and forecast data together, I've modified an otherwise standard Time dimension by adding an attribute, [Is Actual], which is used to identify dates that have passed (and for which I therefore have actuals) and dates that are yet to come (and for which I must rely on forecasted values). I then created a calculated member, [Measures].[Hours], where I bring together actuals and forecasted values based on their association with the [Is Actual] attribute. Here's the calculation:

([Measures].[Actuals],[Date].[Is Actual].[True]) + ([Measures].[Forecast],[Date].[Is Actual].[False])

The result of this expression is that when I am reviewing a time period comprised solely of actuals, the first part of the expression returns a value while the second returns nothing. When I review a period that is comprised solely of forecast, the opposite is true. And when I review a period that should be represented partly by actuals and partly by forecast, each side of the equation returns the proper part. Utilization is then calculated of this "combined" measure.

UPDATED: Getting the Timeline Filter (Slicer) in Excel 2013 to Work with an Analysis Services OLAP Cube

2012-11-30T20:05:00Z

In Excel 2013, there is a new Timeline filter (slicer) that allows you to easily select a range of dates over which you want to filter your data. The feature is very slick. Here's an picture:

I wanted to use this slicer in an Analysis Services demo but run into a bit of an issue getting this to work with my OLAP cube. After spending a little time with the Excel team, it turns out that this slicer picks up on the MemberValue property. If you map an attribute's MemberValue property to a field that uses a date data type, the Timeline filter is available to you.

In my scenario, that means that I'm setting up a Date dimension with a leaf-level Date attribute. That attribute's MemberKey property is assigned to an integer surrogate key column and it's MemberName property is assigned to a friendly formatted string (WChar) column. I have set up all my Type properties (on the dimension and this and other attributes) as you would expect (because I want to use some of the Time Intelligence features in Analysis Services). And now I map the MemberValue property on the Date attribute to an actual field using a Date data type to enable the Timeline filter.

It turns out that in Excel 2010 & 2007 this same step (of assigning the MemberValue property to a date data type field) triggers a Date Filter drop-down on row and column filters. Who knew?! :-)

UPDATE After posting this, Greg Galloway sent me this link to an Excel 2007 document identifying OLAP design practices that will light up functionality in the Pivot Table. This includes the Date filtering mentioned above. The requirements mentioned there are that the dimension has a Type of Time and the leaf-level attribute has a MemberValue property mapped to a field with a date data type. These are the exact constraints on the Timeline filter.

Writeback Application Code Samples

2012-07-20T17:33:32Z

In order to help folks get started with writeback applications, I'm posting here an Analysis Services database, it's associated data source, and three sample Excel applications demonstrating different forms of writeback.

By downloading these, you assume all risk and agree to work with these in an isolated, test environment only. I want to make sure I'm clear that these are not thoroughly tested, do not employ validation or error handling, and are otherwise in no way ready for a production deployment.

That said, if you do download and use these, you'll need to change the data source connection string in the Analysis Services database to point to wherever you deployed the WritebackDemo database. You'll need to deal with permissions issues between your Analysis Services instance and SQL Server Database Engine instance. You'll also need to modify the connections in the workbooks; these are accessible through the Data tab's Connections item.

Also, I should note that both databases use SQL Server 2012 RTM. You should be able to download a copy of Evaluation Edition to get these to work if you do not otherwise have Developer Edition available to you.

I hope these are helpful in demonstrating the concepts explained in the posts and giving you a starting point for your writeback applications.

Managing Writeback Cubes

2012-07-20T17:23:04Z

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.

In presenting on the topic of writeback applications with Analysis Services, there are a few administrative questions that seem to come up which I will attempt to address here. These tend to be focused on:

· Backup & Recovery
· Writeback Partition Maintenance
· Update Conflicts

Backup & Recovery

The writeback application is dependent on a number of assets. These include the user-interface and any supporting components, the Analysis Services database, the source database(s) and the writeback partition table. Assuming you have the user-interface and supporting components covered, I’ll focus on the AS database, source database(s) and writeback partition table which I’ll refer to collectively as the data assets.

To recover the data assets, you need to implement standard backup procedures. If you limit your cube to a single source database and keep your writeback partition table in that database, then you can backup and recovery that database without a potential loss in data consistency. That said, there is the potential for the source database (including the writeback partition table) to be inconsistent with data in the Analysis Services database. If you must recover one or the other or both, execute a full process on the Analysis Services database following recovery in order to ensure they are in sync. As Analysis Services databases associated with writeback applications tend to be relatively small, the full process should not be too burdensome.

Writeback Partition Maintenance

I cannot stress this enough. LEAVE THE WRITEBACK PARTITION TABLE ALONE! If you are concerned about performance, use the MOLAP storage option for the writeback partition as this will improve query performance even when there are large numbers of entries in this table. If there has been an invalid writeback operation, consider correcting it with another writeback operation.

If you do modify the data in the table – did I mention that I really think you shouldn’t do this? – then be sure to process the associated measure group once you are done to ensure it is in sync with the new data in the table. This goes for whether you are employing MOLAP or ROLAP for your writeback partition.

If you are bringing in additional data to your data source, such as data flowing in through an ETL process, model your data in such a way that that data is separated from the writeback entries. Some examples of how to do this include having a measure group for actuals and another for forecast where actuals enter the database through ETL and forecast enter the database through writeback. Another approach is to assign incoming data to a special scenario, e.g. an Observed, Actuals, or Historical scenario, and have writebacks take place against other scenarios.

There are many ways you can ensure that new data coming into your data source do not invalidate the writeback entries. If you cannot then you may have to assume that with a data update (through the ETL) the writeback entries are no longer valid. In this case, you may need to delete entries from the writeback partition table (or truncate the whole table) to reset the data. If you do this, be sure to process the associated measure group in the cube to make sure it is in sync.

Finally, please be aware that making changes to a measure group with a writeback partition may require the structure of the writeback partition table to change. Once a writeback application is in use, you must carefully consider how any changes to a measure group receiving writebacks will be implemented.

Update Conflicts

When a user writes back to a cube, the changes are held in memory as part of a connection-specific writeback cache. That cache is isolated from other users so that the associated user can perform multiple cycles of What-If analysis before publishing or discarding those changes. Only when those changes are published (committed) are they potentially exposed to other users. So, how does Analysis Services deal with the potential situation within which two users are writing back to overlapping parts of the cube?

In short, it doesn’t. If one user makes changes while another user makes changes, they are isolated from one another. If one user publishes his or her changes, there is no signal to the other user that the data he or she is working with is no longer current. If the other user commits his or her changes not knowing the first user has made changes, the other user’s changes overwrite the first user’s changes and become the current state of the data.

At first this sounds terrible, but let’s think about this a little further. Writeback applications are intended to be expert systems. This assumes a limited number of users with writeback permissions. Next, can use dimension data and cell data security to control to which parts of the cube individual users can write and in doing so can ensure we have few potential overlapping writes. And finally, if we are building a custom application, we can always open a second connection and validate the state of the data prior to committal. This later step is a little extreme to me for most applications, but if you were not mitigate the problem with limited numbers of writeback users and avoidance of overlapping write-enabled portions of the cube, it’s another option.

Writeback to a Regular Dimension

2012-07-20T14:46:00Z

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.

In the post on writeback to a parent-child dimension, writeback was implemented using the ALTER CUBE MDX statement. If you attempt to use that statement to writeback to a regular dimension you will get an error stating that “Dimension writeback using this syntax is supported only on parent-child members.” To get around this, you’ll need to writeback to a regular dimension using one of three XMLA commands.

Before diving into those commands, it should be pointed out that this isn’t as intimidating as it might sound. The commands themselves follow some simple patterns, and once you learn these and get past the verbose inclusion of namespaces they are surprisingly straightforward. In addition, the pattern established before with opening a connection in ADO or ADO.NET using the MSOLAP provider works the same with XMLA as it does with MDX. Finally, it’s worth noting that these XMLA commands could be used in place of the ALTER CUBE MDX statement for updating a parent-child dimension as well. I focused on the ALTER CUBE statement in the previous post simply because that’s the traditional pattern used for these kinds of applications.

The Basic Structure of the XMLA Commands

To add, modify, or delete a dimension record in a regular dimension, you’ll use the Insert, Update, or Drop XMLA commands. The basic structure of these commands is as follows:

<Command   xsi:type="Command"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200"
xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object> … </Object>
        <Command Specific Elements/>
</Command>

In place of the word Command, you will use Insert, Update or Drop. Be sure to employ the appropriate command name for the opening and closing tags of the XMLA statement, but also use the command name with the xsi:type attribute. In addition, be sure to follow the sentence case pattern – first letter in upper case and the remaining in lower case - used in the sample above.

The Object element in these commands identifies the database and dimension being operated on and is consistent across all three commands:

<Object>
        <Database>Writeback</Database>
        <Cube>$Project</Cube>
        <Dimension>Project</Dimension>
</Object>

Notice in this element that a cube is identified. For the cube element, provide the name of the database-level dimension (not a cube dimension) you are updating preceded by a dollar-sign. If you are curious as to why this is, the short answer is that you are modifying a dimension object that exists at the Analysis Services database level. At that level, a dimension is thought of as its own cube.

In addition to these elements, the commands will make use of one or more additional elements, namely the Attributes and Where elements, depending on the specific command employed. These will be addressed in the context of the specific commands in the following sections.

Adding a Dimension Member

To add a dimension member, use the Insert command. This command follows the basic pattern described above and employs the additional Attributes element.

The Attributes element identifies one or more attributes for the newly added dimension entry as demonstrated in this sample:

<Attributes>
        <Attribute>
               <AttributeName>Project</AttributeName>
               <Name>Project 06</Name>
               <Keys>
                       <Key>Project 06</Key>
               </Keys>
        </Attribute>
        <Attribute>
               <AttributeName>Owner</AttributeName>
               <Name>Owner 02</Name>
               <Keys>
                       <Key>Owner 02</Key>
               </Keys>
        </Attribute>
</Attributes>

As you can see in this sample, an entry in the Project dimension has two attributes, Project and Owner, as identified by the AttributeName element under each Attribute element. For each attribute, we identify a name and key value. If an attribute has a multi-part key, each key value is specified in the order in which they are defined for the attribute, but as each attribute here has a single key, only one Key element is employed under each Keys element.

The Attribute element has many more sub-elements so that you are not limited to the items identified here. For more information on what can be prescribed to an attribute through the Attribute element, please refer to this entry in Books Online.

Dropping a Dimension Member

To drop a dimension member, use the Drop XMLA command. As with the Insert command, Drop follows the basic pattern discussed at the top of this entry, but its one command-specific element is the Where element.

The Where element behaves very much like a WHERE clause in a SQL statement. Think of it as specifying matching criteria which can identify one or more entries in a dimension. In the Where element, one or more attributes are identified by attribute name and key value. If multiple attributes are supplied, these are considered using OR logic.

In this example, one attribute is specified so that this Where element can be thought of as behaving as a SQL WHERE clause with Project = ‘Project 06’ as its logic:

<Where>
       <Attribute>
               <AttributeName>Project</AttributeName>
               <Keys>
                       <Key>Project 06</Key>
               </Keys>
        </Attribute>
</Where>

If you would like to ensure that only one row is identified through a Where element, specify criteria for nothing more than your leaf-level attribute.

Modifying a Dimension Member

Dimension members are modified using the Update XMLA statement. This statement follows the basic structure identified at the top of this post and makes use of both the Attributes and Where elements.

As with the Drop command, the Where element when used with the Update command identifies the dimension members you wish to affect. The Attributes element, structured same as it is with the Insert command, identifies the new values for these members.

Executing the XMLA Commands

I’ve said a few times that the XMLA commands are executed using the same pattern you’ve used the MDX statements. You simply open a connection using the MSOLAP provider, execute the statement, and then close the connection. But given that XMLA is so different from MDX, I want to make sure I am being crystal clear about this.

Here is a code sample, written in VBA, that demonstrates this pattern. The MyStatement variable can be assigned either the ALTER CUBE MDX statement or one of the three XMLA commands addressed here and it will work the same:

'Add reference to Microsoft ActiveX Data Objects 6.0 Library
'MyStatement is either ALTER CUBE or one of the XMLA DDL statements but defined somewhere else

'1. Establish Connection to Analysis Services Database
Dim cn As New ADODB.Connection
cn.Open "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Writeback Demo;"

'2. Execute Command & Commit Transaction
cn.Execute MyStatement

'3. Wrap Up
cn.Close
Set cn = Nothing

NOTE This has been a very basic exploration of how to employ the Insert, Update and Drop XMLA commands. For more information on these, please check out this entry in Books Online.

Writeback to a Parent-Child Dimension

2012-07-20T00:45:00Z

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.

Writeback to a parent-child dimension is handled using the ALTER CUBE MDX statement. This statement identifies the cube along with the operation to be performed. While there are many operations that can be performed through the ALTER CUBE statement, we are interested in those allowing us to add, move and drop a dimension member.

NOTE There full array of operations supported by the ALTER CUBE statement is something you may wish to explore if you are building an application against Analysis Services. For a more complete survey of these operations, please check out this Books Online entry.

Adding a Member

To add a member to a dimension, the CREATE DIMENSION MEMBER clause is used to identify the new member by name and by key (should the key and the name not be the same) and to specify the value of any additional properties associated with the new member. In this sample, a new member, Objective A.X which sits under the parent member Objective A, is added to the Objective dimension’s Objectives parent-child hierarchy. The member’s lone property, Owner, is assigned a value in the statement as well:

ALTER CUBE [Project Scorecard]
CREATE DIMENSION MEMBER
[Objective].[Objectives].[Objective A].[Objective A.X],
[Owner] = "Owner 01";

As the key and the name for the Objective A.X member are the same, the key is not identified in the previous statement. However, the following statement, employing the Key keyword, could be issued to the same effect:

ALTER CUBE [Project Scorecard]
CREATE DIMENSION MEMBER
   [Objective].[Objectives].[Objective A].[Objective A.X],
   Key = "Objective A.X",
   [Owner] = "Owner 01";

Moving a Member

To move a member to a new position within a parent-child hierarchy, the MOVE DIMENSION MEMBER clause is employed to identify the member and its new parent. In the following sample statement, the member Objective A.X is moved to a position immediately under member Objective B:

ALTER CUBE [Project Scorecard]
MOVE DIMENSION MEMBER
[Objective].[Objectives].[Objective A.X]
UNDER [Objective].[Objectives].[Objective B];

If you wish to skip levels between the new parent and the moved member, the SKIPPED_LEVELS keyword can be employed along with a valid positive integer value. If not specified, it is assumed that the new member resides just under its newly assigned parent.

Please note, should the member being moved have descendants, those descendents are moved with it to the newly specified position.

Dropping a Member

To drop a member, the DROP DIMENSION MEMBER clause is used, identifying the member to be removed. The WITH DESCENDANTS keyword phrase can be used to drop any descendant members of this member. Without that subclause, descendants of the dropped member are moved under the it's parent (retaining the hierarchical structure of the descendants).

In this sample, the Objective A.X member is dropped along with any potential descendants:

ALTER CUBE [Project Scorecard]
DROP DIMENSION MEMBER
[Objective].[Objectives].[Objective A.X]
WITH DESCENDANTS;

Introducing Dimension Writeback

2012-06-20T21:07:00Z

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.

Up to this point, we’ve focused exclusively on the update of measures using cell writeback. When we need to insert, update or delete dimension records, a different form of writeback, i.e. dimension writeback, is employed. Far less frequently used than cell writeback, dimension writeback is a very useful technique when we need to give analysts control over dimension data.

NOTE As a reminder, dimension writeback is not available in SQL Server Standard edition.

Like cell writeback, dimension writeback is performed by establishing an (ADO or ADO.NET using the MSOLAP provider) connection to Analysis Services and submitting statements to affect the writeback operation. However, there is no writeback cache with dimension writeback.

Instead, the operation is performed immediately through Analysis Services and against the associated table (or updateable view) in the relational database. There is no separate step to discard (rollback) or publish (commit) the changes. In addition there is no inherent audit trail retained for the data changes and the operation must meet all the constraints imposed on the relational database table, e.g. provide values for non-nullable fields, if the operation is to succeed. In this regard, Analysis Services simply provides a consistent point of interaction for the writeback application.

NOTE Permission to perform dimension writeback is often limited to a small subset of the application’s users. In addition to meeting the security requirements of Analysis Services, the connection through which interactions with the underlying dimension table take place must have the permissions required to perform the associated DML operations within the relational database. Without these permissions, an error will be generated in the relational database and surfaced through Analysis Services.

From a structural perspective, a dimension supporting writeback must be mapped to a single table in the data source view. That table should map to a single table or updateable view in the associated relational data source. The dimension must also have its WriteEnabled property set to True, and users without full control of the Analysis Services database must be assigned Read/Write permission to the dimension through a role. Then, depending on the type of dimension being written to, i.e. a parent-child or regular dimension, the application must submit either the ALTER CUBE MDX statement or one of three XMLA statements, respectively. Writeback to parent-child and regular dimensions are addressed separately in the next two blog entries.

Finally, on the topic of security, end-users you wish to perform dimension writeback must be granted Read/Write permissions on the dimension(s) to which you wish to allow them to write. This can be assigned to the dimension at the database level or to cube dimensions (at the cube level), though database-level rights make the most sense to me personally. Users with Full Control Permission on a database and AS admins also have permission to write data (assuming the dimension other wise meets the requirements for this feature).

Allocation across a Parent-Child Hierarchy

2012-06-19T21:31:00Z

NOTE This is part of a series of entries onthe topic of Building Writeback Applications with Analysis Services.

A parent-child hierarchy consists of members with which values are associated at various levels. A frequently cited example of such a hierarchy is the Employees hierarchy of an Employee dimension. Applied to a Salary measure, members up and down the hierarchy are associated with values. For managers, the associated salary value reflects not only that individual’s salary those of any subordinate members.

To differentiate between that part of the measure value that is directly attributable to the member and that which represents an aggregate of its subordinates, Analysis Services injects a special (leaf-level) member, the Member with Data member, directly under each member of the parent-child hierarchy. The Member with Data member holds the directly attributable portion of the value, leaving the member with which it is associated to present the aggregate of the Member with Data member and any of its other subordinates.

By default, the Member with Data member is not visible in a parent-child hierarchy but this can be changed through the hierarchy’s MembersWithData property setting. Visible or not, the Member with Data member is always present so that when data is written back to a member of a parent-child hierarchy, the Members With Data member is considered for allocation.

To illustrate the consequence of this, let’s say Lisa is a member of the Employees parent-child hierarchy and has two subordinates, John and Jane, neither of which has subordinates of their own. If we writeback a value of $150,000 to the Lisa member and use the default equal allocation (USE_EQUAL_ALLOCATION) technique, John and Jane each reflect $50,000 and Lisa reflects the $150,000 we wrote. The missing $50,000 has been assigned to Lisa’s Member with Data member.

If this is what we intended with the writeback, then great! But if it’s not what we intended, how else might we approach this problem?

First, we could write back only to members with no other subordinates other than their Member with Data member. If values need to be written up and down the hierarchy, this might require you to expose the Member with Data member through the MembersWithData property setting.

Alternatively, we might write back to the attribute hierarchy upon which the parent-child hierarchy is based. (For example, the Employees parent-child hierarchy is based on an Employee attribute hierarchy so that we might write back to it instead.) This works so long as navigation of the attribute-hierarchy is not problematic.

Finally, you might allow write back to any member in a parent-child hierarchy and take explicit control of the allocation process using a weighting expression. In the expression, you might identify the Member with Data member using the DATAMEMBER function.

Of course, there’s always the option of not using a parent-child hierarchy in your cube design and avoiding this complication altogether. However, parent-child hierarchies are very flexible making them ideal for performing dimension writeback, a topic I will present in the next few blog entries.

Understanding Allocations

2012-06-16T20:18:00Z

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.

In my previous entries on this topic, I’ve been writing data to the Score measure at the intersection of an individual project and a leaf-level objective. The value that is being written represents data at the finest level of granularity supported in the cube as no dimension members within the Project or Objective dimensions rolls up into that value. We might describe this as writing at the leaf-level as I am working with only leaf-level members across all my related dimensions.

By working at the leaf-level, I’ve avoided the complexity of allocation. Allocation occurs when a value is written above the leaf-level. For example, if I were to write a score for the All Projects member of the Project dimension, Analysis Services would need instruction on how to allocate that score across the leaf-level projects in the cube. Regardless of the level at which you write data, Analysis Services always allocates your value to the leaf-level and then aggregates the value back up.

To consider how Analysis Services goes about this, let’s use a very simple cube. In this cube, we are focused on forecasting sales of products. There is one dimension, Product, and it has two attributes, Product Category and Product Subcategory, the latter of which rolls up into the former. In the cube’s one measure group, we have two measures, Sales (Amount) and Quantity, both of which aggregate by simple summation.

Let’s say that from a previous forecasting exercise, Sales for Subcategory A.1 was expected to be $180 and Subcategory A.2 was expected to be $20 giving us forecasted sales of $200 for Category A.

Two-hundred dollars seems a bit low for Category A so we write a value of $300 to its Sales measure. What should be the new Sales Amount values for subcategories A.1 and A.2?

Analysis Services provides you four options for handling this. These options represent the intersection of the answers to two simple questions:

What would you like to allocate?
How would you like to allocate it?

The answer to the question of what to allocate is either the value you wrote or the difference between that value the previous value. In our sales forecast example, the value is $300 and the increment – the difference between the new value and the previous value – is $100.

Using either the value or the increment, we now consider how to allocate it. Here we have two options. We can evenly divide the value or increment evenly across the leaf-level members under us – in our case that would be subcategories A.1 and A.2. Or, we can employ a weighting expression to determine which proportion of the value or increment is applied to each leaf member. (This is referred to as weighted allocation.)

NOTE I’m saying “leaf-level member” as I think it’s the easiest for most folks to digest but in reality Analysis Services is considering the leaf-level tuples that aggregate to the tuple to which we are writing. For the very, very simple cube we are using, the two phrases are highly interchangeable. For more complex cubes, understanding the difference in these concepts might be helpful. But then again, that’s probably more technical than most folks need.

The weighting expression is certainly the more complex of these two options but it gives you far more control over the allocation process. The expression is an MDX expression that is evaluated for each leaf-level member. Typically, the weighting expression is calculated as a ratio between 0 and 1 and the sum of all of its evaluations across the leaf-level entries sums to 1. However, neither of these constraints is enforced. This gives you maximum flexibility but also means you need to carefully consider (and validate) your weighting expressions.

The default weighting expression (when weighted allocation is employed but no expression is provided) performs a proportional allocation based on the leaf-level member’s contribution to the original value that is now being overwritten. In our sales example, a proportional allocation would assign 0.90 as the weighting value to Subcategory A.1 as that subcategory’s value of $180 represents 90% of Category A’s value of $200. Similarly, Subcategory A.2 would be assigned a weighting value of 0.10. An equivalent weighting expression would look something like this:

([Measures].[Sales], [Product].[Product Subcategory].CurrentMember) /
([Measures].[Sales], [Product].[Product Category].CurrentMember)

NOTE There are so many ways you can write this expression, and I have purposefully added otherwise unnecessary elements in order to give is a bit more clarity. (If you are new to MDX, clarity is relative, eh?)

Getting back to the questions of what to allocate and how to allocate it, you can now see how we arrive at Analysis Service’s four options for handling allocation. The keyword controlling the allocation behavior is presented in the chart below. This keyword is tacked onto the end of the UPDATE CUBE statement (followed by the weighting expression if one of the weighted options is employed and other than a proportional allocation is desired). If no keyword is provided and allocation must be performed, Analysis Services uses the USE_EQUAL_ALLOCATION option which allocates the value equally amongst the leaves.

To ensure these four options are crystal clear, let’s return one last time to the sales forecast example. We had an original value of $200 for the Category A sales which was the sum of $180 for Subcategory A.1 and $20 for Subcategory A.2. We then updated the sales for Category A from $200 to $300. That gives us a new value of $300 and an increment of $100.

If we perform an even allocation of the value (USE_EQUAL_ALLOCATION), Subcategory A.1 becomes $150 ($300/2 leaves) and Subcategory A.2 becomes $150 ($300/2 leaves). If we perform an even allocation of the increment (USE_EQUAL_INCREMENT), Subcategory A.1 becomes $230 ($180 + $100/2 leaves) and Subcategory A.2 becomes $70 ($20 + $100/2 leaves).

If we perform a weighted allocation of the value (USE_WEIGHTED_ALLOCATION) and use the default weighting expression, Subcategory A.1 becomes $270 ($300 * $180/$200) and Subcategory A.2 becomes $30 ($300 * $20/$200). If we perform a weighted allocation of the increment (USE_WEIGHTED_INCREMENT) and again use the default weighting expression, Subcategory A.1 becomes $270 ($180 + $100 * $180/$200) and Subcategory A.2 becomes $30 ($20 + $100 * $20/$200).

NOTE When the default weighting expression is used, the resulting values are the same even though they are derived using differing logic.

Before leaving the topic of allocations, there are three final items I need to briefly address. First, allocation always occurs at the leaf-level and is aggregated back up. As a result, there can be an accumulation of rounding errors which cause you to get back a value slightly different from the one you wrote. If this is a problem, consider writing to the leaf-levels yourself where you can have explicit control of your values.

Second, some older documentation on allocation in Analysis Services highlights a NO_ALLOCATION option. I’m not sure the story behind it but please be aware there is no such supported option. Some folks say it is accepted in older versions but doesn’t do what’s expected and others say it causes an error to be returned. Either way, don’t use this.

Finally, the Update Isolation Level connection string parameter can be used to improve Analysis Service’s performance during allocation. That said, don’t use it unless you really need it and only use it if you fully understand its impact. I might write a longer entry on this parameter in the future, but for now here’s the short version: setting this parameter to 1 tells Analysis Services that there is no overlap between the cells impacted by allocation in a single UPDATE CUBE statement. This allows Analysis Services to skip some steps it might otherwise perform (which boosts performance). But these steps are also used to ensure you get the right values back from the cube so again don’t use this unless you really need it and you fully understand it.

NOTE There really is a fourth thing I need to address, and that’s around a special leaf-level member in a parent-child hierarchy and how it is handled during allocation. But that’s a topic for another entry.

Sidebar: The MS IT Pattern for Custom Writeback Applications

2012-06-05T14:27:00Z

NOTE This is part of a series of entries on the topic of Building a Writeback Application with Analysis Services.

Writeback to cubes through Excel applications is used extensively within Microsoft to support a number of our internal processes, including our mid-year business review. Sergei Gundorov and Pablo Trejo Montemayor with MS IT outline how writeback fits into this process within this white paper. It’s well worth a read.

As you might imagine, supporting a custom application written in Excel within a company the size of Microsoft can be challenging. To address this, Sergei and Pablo have focused on standardizing the implementation of the writeback code and extending the capabilities of Excel Services in SharePoint to manage the code base. And they do this in a manner that retains the flexibility that drives most analysts to employ Excel. Check out these white papers to explore the details of what they are doing:

Focusing on the Excel application aspects of what Sergei and Pablo are doing, the process in a nutshell is to use the Excel cube functions to retrieve data from a cube, have users enter data in associated Excel cells, use a custom function to evaluate the user’s input, and then pull all the valid data input together to form an UPDATE CUBE statement that is then submitted and committed to Analysis Services. If you are just getting started with Excel cube functions, you might find that starting with a PivotTable containing the data you want and then converting the PivotTable to formulas (based on cube functions) is the easiest way to go. If you’d like to review a working demo that incorporates Sergei and Pablo’s code and follows the basic Excel pattern, please check out the sample macro-enabled workbook associated with this entry.

Building a Custom Writeback Application

2012-06-05T01:09:00Z

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services. If you have not read the other posts in this series, you will want to read A Technical Review of the Simple Demonstration before proceeding.

Once you have a write-enabled cube in place, you then need a writeback application to interact with it. As shown in an earlier demonstration, Excel 2010 has writeback functionality built into its PivotTable feature. However, if you need another means to interact with your cube, you can build a custom writeback application by following a relatively simple pattern.

The pattern consists of connecting to the Analysis Services cube, issuing statements to update the cube, and then committing or rollingback these updates. Yes, there are other parts to this such as the retrieval and presentation of the data, the handling of data entry, etc. but those are presentation details I’ll assume most application developers are familiar with. (That said, I will address a few presentation details in my next post highlighting a pattern used by Microsoft IT for building writeback applications within our company.)

To establish a connection to Analysis Services, use a standard library such as ADO or ADO.Net. Analysis Management Objects (AMO) and ADOMD can also be used but these build off of ADO.NET and don’t really add much in the context of most writeback applications.

Minimally, the connection string employed should specify the MSOLAP provider and identify the Analysis Services instance and database. Here is a sample connection string that gets the job done:

Provider=MSOLAP;Data Source=localhost;Initial Catalog=Writeback Demo;

NOTE Once the connection is established, don’t close it until you are ready to commit or rollback your updates. Closing the connection will cause the writeback cache to be discarded, effectively rolling back any outstanding transactions.

With the connection established, you can initialize the writeback cache by issuing a BEGIN TRANSACTION statement. Excel 2010’s PivotTable does this but it’s not actually necessary. If you don’t initialize the cache explicitly, an implicit transaction is created with your first data update.

NOTE If you are familiar with Transact-SQL (T-SQL), you probably know you can manage transactions using BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN or BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION. The MDX language used by Analysis Services does not accept the shortened form of the transaction keyword. You have to spell it out in full or you will receive an error.

Updates are submitted over the connection using the UPDATE CUBE statement. The statement is pretty straightforward in that you identify the cube you wish to update and assign values one or more tuples (cells) in a comma-delimited list. If you are assigning values to a nonleaf tuple, you can identify an allocation method as the last part of the statement. (I’ll cover allocation in much more depth in a later post.) Here is a sample UPDATE CUBE statement that assigns values to two leaf-level tuples:

UPDATE CUBE [Project Scorecard]
SET
   (
   [Project].[Project].[Project 01],
   [Objective].[Objectives].[Objective A.1],
   [Measures].[Score]) = 5,
   (
   [Project].[Project].[Project 01],
   [Objective].[Objectives].[Objective A.2],
   [Measures].[Score]) = 3;

Whether you explicitly initiated a transaction or allowed the UPDATE CUBE statement to implicitly create one for you, the final step to the writeback application pattern is to either commit or rollback that transaction. Committing the transaction with the COMMIT TRANSACTION statement forces Analysis Services to write the writeback cache to the writeback partition (table) created at cube design time. If the writeback table is not accessible due to either the relational database being offline or a permissions issue, an error will be generated. To roll back the transaction, the ROLLBACK TRANSACTION statement can be used or the connection to Analysis Services can simply be broken.

The following VBA code sample puts this all together for us. Why VBA, you ask? Because most writeback applications are written as Office applications. Keep in mind that most writeback applications are targeted at a small set of (typically expert) users for whom Office is preferred and VBA is familiar. Here’s the code sample:

'Add reference to Microsoft ActiveX Data Objects 6.0 Library

'Update Statement to Submit
Dim UpdateStatement As String = _
        "update cube [Project Scorecard] " + _
        "set ([Project].[Project].[Project 01]," + _
        "[Objective].[Objectives].[Objective A.1]," + _
        "[Measures].[Score])=4;"

Dim CommitStatement As String = "commit transaction;"

'1. Establish Connection to Analysis Services Database
Dim cn As New ADODB.Connection
cn.Open "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Writeback Demo;"

'2. Execute Command & Commit Transaction
cn.Execute UpdateStatement
cn.Execute CommitStatement

'3. Wrap Up
cn.Close
Set cn = Nothing

To make this a bit more interesting, here is a variant of this pattern using VB.NET, ADO.NET and the Command object:

'Update Statement to Submit
Dim UpdateStatement As String = "update cube [Project Scorecard] " + _
"set ([Project].[Project].[Project 01]," + _
"[Objective].[Objectives].[Objective A.1],[Measures].[Score])=4;"
Dim CommitStatement As String = “commit transaction;"

'1. Establish Connection to Analysis Services Database
Dim cnstr As String = "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Writeback Demo;"
Dim cn As New Data.OleDb.OleDbConnection(cnstr)
cn.Open()

'2.Set Up Command
Dim cmd As Data.OleDb.OleDbCommand = cn.CreateCommand
cmd.CommandType = CommandType.Text

'3. Execute Command & Commit Transaction
cmd.CommandText = UpdateStatement
cmd.ExecuteNonQuery()
cmd.CommandText = CommitStatement
cmd.ExecuteNonQuery()

'4. Wrap Up
cn.Close()

And here is a final variant, again using VB.NET, ADO.NET and the Transaction object. The Transaction object doesn’t really add much to the sample but I’ve put it here for completeness:

'2. Set Up Command & Its Transaction
Dim trn As Data.OleDb.OleDbTransaction
Dim cmd As Data.OleDb.OleDbCommand = cn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = UpdateStatement

'3. Execute Command & Commit Transaction
trn = cn.BeginTransaction()
cmd.Transaction = trn
cmd.ExecuteNonQuery()
trn.Commit()

'4. Wrap Up
cn.Close()

Building a Write-Enabled Cube

2012-06-04T04:49:00Z

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.

To build a writeback application, you need a write-enabled cube and an application with which to interact with it. I’ll cover the application in my next post, but for now I’ll focus on the cube.

The design of the cube depends on the model you wish to employ. The key thing to keep in mind is that the concerns about data volumes, scalability, maintenance and processing cycles we typically weigh when designing a cube do not come into play as much with a write-enabled cube. The write-enabled cube is often of limited scope, receives smaller and less regular data updates, and is used by smaller numbers of more specialized (often expert) users. These cubes are sometimes mapped to a data warehouse but more frequently are built against a specialized data mart. There are always exceptions but the point I want to drive home is that you are not typically injecting writeback into the middle of a traditionally built OLAP cube.

As a cube designer, the key thing to concern yourself with are the limitations imposed on the measures and measure groups of a write-enabled cube. Any measure to which you wish to writeback data must be mapped to values in the data source view, i.e. not calculated members defined in the cube script. These measures must employ signed numeric data types and use the SUM aggregation function. And, the measure group housing a writeback measure must employ regular relationships only.

Thinking back to the cube in the simple demonstration, you may be curious how these restrictions were overcome. The cube seemed to employ a many-to-many relationship between the measure group housing the Score measure and the Scenario dimension and the Score measure itself employed a weighted average for aggregate values. All this was handled with a little sleight-of-hand in the cube script. While not a requirement, write-enabled cubes tend to make very heavy use of the cube script to both work around the limitations imposed on the cube and implement the model’s calculation logic.

Returning to the writeback measure, if you intend to commit (publish) data the limitations on the writeback measure apply to all measures in its measure group. To clarify, if you only intend to have users perform writeback on a measure and then discard their changes, measures in the same measure group as the measure to which the user is writing do not have to follow the rules above. However, if you wish to commit the data (which is the much more typical case) ever measure in the group must adhere to these rules, regardless of whether they receive writeback modification.

To house the committed writeback data for the measure group, you must setup a writeback partition per these instructions. This creates a writeback table in a relational database for which you have established a data source connection. (This is typically the same source to which the measure group is mapped, but you have flexibility should you need it.) Keep in mind that the account used with the data source must have the permissions required to create this table when the cube is published and read and write permissions to it once it is deployed.

On the topic of security, any end-users you wish to perform writeback to the cube must be granted Read/Write permissions at the cube level. By setting read/write permissions on the cell data, you can more narrowly define to which cells a user can write. Users with Full Control Permission on the database and Analysis Services administrators also have read and write permissions across the cube.

A Technical Review of the Simple Demonstration

2012-06-03T19:10:00Z

NOTE This is part of a series of entries on the topic of Building a Writeback Application with Analysis Services and makes direct reference to a previous post containing a simple demostration of what-if analysis.

In the simple demonstration of writeback, the analyst connected to a write-enabled cube, assembled a PivotTable, write-enabled the PivotTable, entered data and recalculated the cube, and then either published or discarded his or her changes. Each of these steps represents an interaction with Analysis Services that’s important to understand.

The connection to the write-enabled cube was established like any other connection to Analysis Services from Excel. In fact, the OLE DB provider used by Excel to make the connection has only one parameter relevant to a writeback scenario, but it is optional and was not employed for this demonstration.

NOTE I’ll point out that parameter in a later blog entry on allocations.

The analyst then assembled a PivotTable based on Analysis Services data. As with establishing the connection, there is nothing special or different about this interaction.

But then the analyst selected the option to write-enable the PivotTable. That instructed Excel to unlocked the PivotTable for data entry and caused a BEGIN TRANSACTION (MDX) statement to be submitted to Analysis Services. Now things are different. The BEGIN TRANSACTION statement causes Analysis Services to initialize an in-memory cache associated with the analyst’s connection.

The analyst now enters values into the PivotTable and requests for it to be recalculated. To recalculate the data in the PivotTable based on the data entered, an UPDATE CUBE statement containing these values is submitted to Analysis Services by the application. Analysis Services calculates the difference between its values and the assignments in the UPDATE CUBE statement and writes these to the connection-specific writeback cache.

While subsequent requests for data through this connection reflect the values in the writeback cache, the base cube remains unaltered. Other analysts can interact with the cube without awareness of these updates, providing the analyst the freedom to explore a wide range of values without interfering with others’ work.

When the analyst is done with his or her work, he or she must then decide what to do with the values written back to the cube. If the analyst decides to discard the changes, a ROLLBACK TRANSACTION statement is submitted which causes the cache to be reset. (Alternatively, the analyst could simple break the connection to Analysis Services and the cache would be discarded.)

If the analyst decides to publish the changes, a COMMIT TRANSACTION statement is submitted to Analysis Services which causes the data to be written to a writeback partition and to be reflected in data presented to all consumers of the cube. The writeback partition is established by the cube designer and houses the former contents of the writeback cache. It also associates date-time and user-identity values with the data to provide a basic audit trail.

Sidebar: The Other What-If Analysis Options in Excel

2012-06-03T19:07:00Z

NOTE This is part of a series of entries on the topic of Building a Writeback Application with Analysis Services.

What-if analysis against Analysis Services is just one form of what-if analysis supported by Excel. Other forms focus on spreadsheet-based models and are exposed on the Data tab. More information on these alternatives is found here.

NOTE Just to make things a bit more confusing, Excel can support even more forms of what-if analysis using the Solver add-in.

The basic pattern of all these what-if analyses is the same. The analyst constructs a model, varies inputs, and evaluates the outputs. The fundamental difference is the type of model used: a spreadsheet or an Analysis Services cube.

The question then becomes, Which model should I use for my analyses? In my opinion (and I’m by no means authoritative on this), Analysis Services is the right choice when the model is based on sets of related data with complex calculations across these.

In addition, Analysis Services writeback supports:

Data input from multiple analysts,
An audit trail of data input,
Preservation of data input between analyses,
Complex security for controlling the reading and writing of data

Of course, the use of Analysis Services as the model behind the analysis requires a moderate level of cube development skill within your organization. Other techniques (such as writing to a relational database which employ a more ubiquitous skillset) can be employed to address many of the previously stated requirements. Still, Analysis Services (and OLAP in general) is unparalleled in its ability to express complex calculations which is why many planning, forecasting, and budgeting applications employ OLAP.

A Simple Demonstration of What-If Analysis

2012-06-03T16:46:00Z

NOTE This is the second of a series of entries on the topic of Building a Writeback Application with Analysis Services.

To demonstrate writeback with Analysis Services, I’ll leverage a simple model built around a project selection process that takes place within a fictional company. Through this process, projects are submitted from across the organization by a variety of users making writeback (with its associated audit trail which I’ll discuss in later entries) very appealing. However, the true driver behind the use of writeback is the need to evaluate uncertainty surrounding the organization’s objectives.

Projects are submitted as part of a mid-year planning cycle. At this point in the year, it’s always a bit unclear exactly what the organization’s priorities will be at the start of the next year. If revenue targets are off, the company may enter the year focused on cost-cutting. Alternatively, the company may find itself responding to changes in regulations, facing new competitive pressures, or seeking new opportunities for growth. As there simply aren’t enough resources to address all the requested projects, the business needs a way to prioritize them in the face of this uncertainty.

To deal with this uncertainty, projects are scored against a wide range of objectives. Scenarios are created which reflect different potential areas of focus within the company in the next year, and objectives are weighted relative to their importance within each scenario. Average project scores reflecting these weights can then be calculated so that decision makers can quickly determine which projects should receive priority under different circumstances. Astute project sponsors might also consider how their projects might fare better or worse under a variety of likely scenarios with some minor adjustments. (A review process keeps everyone’s scores honest.)

To support this, an Analysis Services cube is developed which relates scores to projects and objectives and weighs objectives under differing scenarios. The following is a diagram of this model:

To interact with this model, the analyst launches Excel 2010 (which has built-in writeback capabilities) and connects to the Analysis Services cube. A PivotTable is then constructed showing scores for Project 01 relative to various objectives under Scenario 1. Scenario 1 provides an even weighting to all the objectives allowing the user to concentrate on data entry. The following illustrates the results of the analyst’s work:

NOTE I will not be covering the basics of connecting to Analysis Services from Excel and assembling a PivotTable and Slicers. If you would like more information on these topics, please review this document.

To enable writeback, the analyst selects a(ny) cell within the PivotTable and then navigates to the Options tab in the ribbon. He or she then selects Enable What-If Analysis located under the What-If Analysis item on the far right of the tab. This write-enables the PivotTable:

With the PivotTable write-enabled, the analyst changes the score for Objective B.1 from 5 to 3. If he or she then selects Calculate PivotTable with Changes, located under the What-If Analysis item in the Options tab of the ribbon, the average score for Objective B and the Grand Total are updated to reflect the data input:

NOTE If you would like the PivotTable to automatically recalculate itself with each data entry, select Automatically Calculate Changes located under the What-If Analysis item.

Selecting Scenario 2 in the Slicer to the left of the PivotTable triggers another recalculation, this time using an uneven weighting to the objectives that’s appropriate to that particular scenario. For example, in Scenario 2, Objective B.1 receives slightly less weight than Objectives B.2 and B.3 as reflected in the shift in the average score for Objective B between Scenarios 1 and 2:

NOTE The difference in weighting is so slight that the impact of this one change is lost in the rounding of the Grand Total value.

Once the analyst has completed entering data and reviewing the results, he or she then chooses to either discard the changes to the cube, resetting it back to its original values, or publish the changes to the cube, altering the values presented to other analysts. This is done by selecting either the Discard Changes or Publish Changes options (respectively) under the What-If Analysis item.

Understanding What-If Analysis

2012-06-03T16:25:37Z

NOTE This is the first of a series of entries on the topic of Building Writeback Applications with Analysis Services.

Business analysts frequently encounter questions focused on the impact of potential change:

What if we raised our parts and materials inventory? How might that impact costs? How might that impact the reliability of product delivery?
What if we ran this promotion on this product? How might that impact revenue and profits? What impact might this have on inventories?
What if we were required to reduce our budgets? Where might we make those cuts and what activities might be impacted?

The what-if question is focused on uncertainty. This differs significantly from the what-was question addressed (with relative certainty) using historical data sets. To answer the what-was question, the analyst turns to the data warehouse. To answer the what-if question, the analyst turns to a model.

The model is a representation of the portion of the business the analyst needs to consider. It accepts inputs and produces outputs based on these. The analyst devises scenarios representing combinations of inputs to the model and studies the associated outputs. This exercise clarifies the analyst’s understanding of the business and allows him or her to evaluate the range of outcomes likely to occur under differing scenarios. Based on this, the analyst can then provide informed guidance on how best to deal with an uncertain situation.

The model used depends on the problem to be solved. A simple model might be nothing more than a diagram jotted down on a sheet of paper or a whiteboard which provides a heuristic for the analyst. A more complex model might be assembled in a spreadsheet or developed using custom coding or statistical techniques. Still other models might be assembled in a database using sets of related data and calculation logic for deriving values from these.

This later kind of model fits neatly into the space occupied by OLAP technologies which is why most OLAP vendors provide writeback functionality with their products. Writeback allows analysts to enter data (inputs) into a cube (model) which are then incorporated into the cube’s calculations (outputs). Analysis Services, Microsoft’s OLAP technology, is no exception and has provided support for writeback since its first release (as OLAP Services) with SQL Server 7.0.

NOTE Cell writeback, the form of writeback we will focus on first, is supported with all editions of Microsoft SQL Server Analysis Services. Dimension writeback, a special case for writeback, is only supported with SQL Server Enterprise, Developer and Evaluation editions.

Building a Writeback Application with Analysis Services

2012-06-03T16:21:00Z

Recently, I delivered a presentation at the 2012 PASS SQL Rally titled Building a Writeback Application with Analysis Services. Based on feedback from that session, I’ve decided to address this topic in my blog, making this information accessible to a wider audience and allowing me to address various points in a bit more depth than the 1-hour presentation format allows.

There’s quite a bit of ground to cover here, so I won’t attempt to do this as a single blog post. Instead, I’ll spread this over a series of entries, each covering a bite-sized chunk of the material. I will address both cell writeback and dimension writeback, explain and demonstrate the interaction with Analysis Services, and provide code and database samples so you can try this at home. As new entries to this series are added, I will update this page which will serve as a kind of table of contents to the series. I hope this information is helpful and I look forward to your feedback.

An Introduction to Big Data Concepts

2011-11-01T23:41:52Z

The idea that data collected in computerized systems could be used to inform and thereby improve decision making has been around for quite some time. Over the last couple decades, ideas of how to assemble a decision support system have coalesced around the concept of a data warehouse.

The construction of a proper data warehouse requires a non-trivial investment. This investment is made with the expectation of benefits, but these are often difficult to enumerate prior to the warehouse’s construction and subsequent employment. For this reason, the data warehouse requires a leap of faith.

For many years, preparation for this leap was a significant part of the conversation with customers interested in Business Intelligence (BI). Today, in recognition of the data warehouse as a tool for navigating business challenges and uncertainty, the conversation tends to focus on maximizing the impact of BI on the organization.

As customers focus on how best to extract insights from data, there is growing recognition of untapped data resources especially unstructured data. These data remain largely untapped because:

The value of these data relative to the cost of their processing and storage is low.
These data are not easily stored and analyzed within the confines of the traditional data warehouse.

To illustrate these points, consider the data in a web log. These data could be very insightful to a business interested in engaging customers through a website. However, individual data records, holding information on a single page request or single image retrieval, are not likely to be high in value, especially over the longer periods of time in which data are stored in a traditional data warehouse.

Furthermore, the structure of many elements within the log records, such as the URI of the referrer or the query string associated with a requested resource is highly variable in nature. Differing questions posed against these data may require them to be interpreted in differing ways. Significant pre-processing of the data in order to neatly fit it into the traditional data warehouse may be unnecessary or even counter-productive.

Web logs are a commonly cited form of unstructured data. A better term for these data may be complex or mixed-typed data as at some level these data have a well understood and meaningful structure. However, this structure is often as a level of granularity higher than the level at which analysis is to be performed, and it’s this mismatch that leads to the unstructured moniker. Other forms of unstructured data include XML or JSON documents, images, video, or PDF, Word, or HTML documents.

The challenges of working with unstructured data, illustrated in the web log example, are often characterized in terms of four Vs. The four Vs are identified as:

Volume – Defined as the total number of bytes associated with the data. Unstructured data are estimated to account for 70-85% of the data in existence and the overall volume of data is rising.
Velocity – Defined as the pace at which the data are to be consumed. As volumes rise, the value of individual data points tend to more rapidly diminish over time.
Variety – Defined as the complexity of the data in this class. This complexity eschews traditional means of analysis.
Variability – Defined as the differing ways in which the data may be interpreted. Differing questions require differing interpretations.

The four Vs articulate the broad challenges of working with unstructured data, but the dominant challenge tends to be in terms of data volume. As a result, the effort to extract insights from unstructured data is often referred to as Big Data.

Because of the challenges of the four Vs, Big Data necessitates an alternative approach to Business Intelligence. This alternative approach, which we might refer to as the unstructured data warehouse or the Big Data warehouse, does not invalidate the traditional data warehouse but does acknowledge its limitations in extracting insights from the full range of available data resources. What exactly is the unstructured data warehouse and how it will relate to the traditional (structured) data warehouse has yet to be determined, but ideas are beginning to coalesce around distributed, algorithmic technologies such as Apache Hadoop.

An Introduction to BigData Concepts: My Disclaimer

2011-10-29T15:02:56Z

Recently, Microsoft announced its plans around Hadoop and all of a sudden BigData is part of the mainstream conversation we are having with our customers. As a Business Intelligence practitioner, I couldn’t be more excited about this, but having been heavily steeped in the structured world of traditional Business Intelligence approaches, I have to admit wrapping my head around the concept of BigData and why it necessitates a new approach has been a bit challenging.

I feel as though I am starting to grasp some parts of the BigData conversation and so I’m using this blog to share my understanding so that it may be challenged and that those similarly wrestling with this may hopefully find a nugget or two that helps them with their journey.

To be clear, I am not a BigData expert, and though I work for Microsoft, I am not part of Microsoft’s BigData team. Please interpret this and subsequent posts accordingly.

Creating Your First FileTable in SQL Server Denali CTP3

2011-09-20T15:53:00Z

With SQL Server Denali CTP3 now available, I'm starting to explore some of the new Database Engine capabilities. One that caught my attention is the FileTable.

The FileTable builds upon FileStream storage which allows BLOB data to be stored as individual files separate from a database's data files. In SQL Server 2008 (and 2008 R2), interactions with the FileStream files took place either through T-SQL or through code which engaged with OpenSqlFileStream API. This was an interesting direction for applications with large volumes of BLOB data which needed to be accessed quickly, but it wasn't the most accessible implementation. (For more info on FileStream storage including its benefits and trade-offs, check out this excellent white paper which includes best practices for FileStream configuraiton which you will want to employ.)

The FileTable addresses this by making the Windows share associated with the externally stored files directly engageable and by allowing the share to employ a familiar, hiearchical folder structure. (The folder structure is represented by the underutilized hierarchyid data type first introdued in SQL Server 2008. If you aren't familiar with it, check it out.) This, along with the semantic search capabilities of Denali, will be what make FileStream take off for database developers.

However, there are quite a number of steps involved with configuring a SQL Server instance and database before you can create your first FileTable, and if you are not familiar with the setup of FileStream storage, these steps can be challenging to perform. Having stumbled a couple times myself before finally getting it right, I figured document the steps involved with enabling the environment and creating a simple FileTable in SQL Server Denali CTP3.

1. Enable the FileStream driver on the SQL Server instance.

a. Open SQL Server Configuration Manager.
b. Navigate to the SQL Server Services folder in the left-hand pane.
c. In the right-hand pane, right-click the Database Engine instance on which you want to enable FileStream support and select Properties.
d. In the Properties dialog, navigate to the FILESTREAM tab.
e. Check the Enable FILESTREAM for Transact-SQL access and Enable FILESTREAM for file I/O access options.

NOTE You can also change the name of the Windows share, but for the purpose of these instructions, I'll leave it as its default, i.e. the name of the instance.

f. Check the Allow remote client access option for the purposes of this post. For production deployments, this may or may not be the right option for you and requires a consideration of the accessibility and security trade-offs.
i. Click the Apply button and then close the Properties dialog and SQL Server Configuration Manager.

At this point, you can see that the server now exposes a Windows share using the name of the Database Engine instance. You can see this by opening Windows Explorer and navigating to \\127.0.0.1. If you enabled remote access on the FileStream share (in Step 1f), you can access the share but it will be empty at this point. (In SQL Server 2008 and 2008 R2, attempting to access the share in this manner would result in an error regardless of the remote access setting.)

2. Enable SQL Server to use the FileStream driver.

a. Open SQL Server Management Studio and cancel the Connect to Server dialog.
b. Click the New Query button and connect to the SQL Server Database Engine instance you configured in Step 1.
c. In the query window, execute the following statements:

exec sp_configure filestream_access_level, 2;
reconfigure;

NOTE The filestream_access_level configuration setting set in Step 2d takes one of three values: 0, 1, or 2. The default value, 0, disables FileStream support. The value 1 enables T-SQL only access to FileStream files, and the value 2 enables both T-SQL and Win32 (direct) access to the files.

With FileStream now configured at the instance-level, you can now setup a new (example) database employing FileStream storage.

3. Create a new database employing a FileStream file group.

a. In the query window used in Step 2d (or a new one), execute the following statement to create a new database:

create database FileTableDemo;

b. In that same query window, execute the following statement to add a FileStream file group to the database:

alter database FileTableDemo
add filegroup FileStreamGroup1 contains filestream;

c. In that same query window, execute the following statement to define the storage associated with the FileStream file group added in the previous step:

alter database FileTableDemo
add file (
    name = FileStream1,
    filename = 'D:\Temp\FileStreamFileGroup1'
    ) to filegroup FileStreamGroup1;

NOTE Steps 3a, b, and c could be combined into a step using a single CREATE DATABASE statement. It's been decomposed into smaller steps here for greater transparency.

This last step deserves a bit more discussion. The path to the FileStream storage, as identified in the filename setting, must be a valid path on the SQL Server system up to the last folder in the path. In other words, D:\Temp must exist for the previous statement to work. However, the last folder, FileStreamFileGroup1 in the case of the previous statement, must not exist. That folder will be created by SQL Server upon statement execution.

With the database in place, you now need to configure how the database will present and control interactions with the FileStream data.

4. Configure how the database handles access to the FileStream data.

a. In the query windows from previous steps, define the name of the Share subfolder through which FileStream files associated with this database will be shared:

alter database FileTableDemo set filestream ( directory_name = 'FileTableDemo' );

b. In the query window, execute the following statement to enable direct read and write access to the FileStream data:

alter database FileTableDemo set filestream( non_transacted_access = full );

NOTE Options for non_transacted_access are off, read_only, and full. Off disables non-transactional access so that data in the FileTableDemo subfolder will not be accessible. Read_only provides read-only access to files (outside a transaction as handled through the OpenSqlFileStream API). Full allows files to be read and written through the subfolder.

If you accessed the share earlier, you might wish to review it again now. The instance's share at \\127.0.0.1 now has a FileTableDemo subfolder per Step 4a.

With all this in place, you can now define a filetable in the database.

5. Create a FileTable within the FileTableDemo database by executing the following statement in the query window from the previous step:

use FileTableDemo;
go
create table MyFileTable as filetable;

NOTE The CREATE TABLE statement used in the last step is very simplistic compared to what is possible with the structure of a FileTable but it provides a good starting point for working with this new feature. For more info on the broader syntax on the CREATE TABLE ... AS FILETABLE statement, please see this document.

With a FileTable now defined within the FileTableDemo database, you can now reconnect to \\127.0.0.1 and access the instance share. The FileTableDemo subfolder defined in Step 4a now has its own subfolder, MyFileTable, named for the FileTable created in Step 5. As the database has been configured to allow full non-transactional access to this folder, you can drag and drop files within the FileTable folder and even create subfolders under it. All of these will be reflected in the MyFileTable table when you interact with it using T-SQL. As you use T-SQL to insert, update, and delete data in the FileTable table, those changes will be reflected in the associated folder on the Windows share. Pretty cool stuff!

Virtualizing the SharePoint 2010 Database-Tier

2011-07-11T18:09:30Z

Over the last few months, I’ve had a number of conversations with customers regarding the virtualization of SQL Server for the database-tier of SharePoint Server 2010 deployments. Historically, administrators have been hesitant to virtualize SQL Server database servers out of concerns for performance. With advances in virtualization technology and the adoption of new IT standards, virtual deployments have become the new norm in many organizations.

Still, virtualization is not the right choice for every workload. The purpose of this blog is to highlight the key considerations that go into deciding whether to virtualize the SharePoint database-tier.

Support for Virtualization

The first thing to consider is the SQL Server support policy for virtualization. SQL Server 2005+ enjoys support on a wide range of virtualization platforms with two important limitations. First, mobility technologies, i.e. VMWare VMotion, are not supported with the exception of Hyper-V Live Migration which has received thorough testing with SQL Server. Second, virtualization snapshots are not supported on any platform, including Hyper-V.

Disk I/O Requirements of the Database-Tier

The next thing to consider is the disk I/O workload generated by SharePoint on the database-tier. Formal testing is required to determine the requirements of a specific deployment, but Microsoft IT’s standard of 1,000 IOPS per TB of data or 5,000 site collections is a good starting point for more highly-utilized deployments. On top of this, MS IT reserves another 150-200% of capacity for periods of high utilization and burst events such as database backups.

With disk I/O requirements known, the next thing to consider is how those requirements will be met. With any server, there is interplay between CPU, memory, controllers, and the disk subsystem which must be understood to achieve optimal throughput. This white paper provides some insight into this and shows how disk I/O capacity can be determined for a server.

NOTE The configuration of the virtual machine can have a significant impact on SQL Server throughput. Consider this guidance on the deployment of SQL Server on Hyper-V technology.

It is possible single machine will not be capable of achieving the IOPS required to support a SharePoint workload. This is true of both physical and virtual machines but more likely with virtual machines given their more limited scale. If the IOPS requirements exceed what can be supported by a single machine, consider scaling up (if possible) or scaling out.

Resource Isolation and Predictable Capacity

Finally, keep in mind any shared resources associated a server. If these resources are not isolated, the available capacity of the server at any point in time will be dependent on the consumption of these resources by other associated servers. With virtual machines, shared resources can include CPUs, memory (when dynamic), network cards, controllers and disk subsystems. With physical machines, shared resources are typically limited to disk subsystems. With both virtual and physical machines, shared resources can be configured for isolation and, therefore, predictable capacity.

Conclusion

When designing the database-tier, consider first and foremost the infrastructure's ability to accomodate the SharePoint-generated workload. Virtualization adds many more factors to the consideration but success on a virtualized platform is achievable. Likewise, a physical deployment does not guarantee success if not built against known requirements.

Securing the Data Warehouse

2011-06-28T13:21:16Z

After completing a series of posts on Building Secure Database Applications, the question of how the practices and features highlighted apply to data warehouses arose.

In a traditional database application, the database is a behind-the-scenes element, accessed indirectly by the user through an application front-end or middle-tier. In such an architecture, interaction with the database can be very tightly controlled to ensure security.

In a data warehouse, the database is the application. Users interact more directly with the tables and views it houses through numerous applications, issuing a wide variety of queries which may or may not be known in advance. The techniques that would be applied to secure the database behind a traditional application could severely constrain the users of the data warehouse and undermine its success.

Still, security is essential. Without appropriate security, the data warehouse, as the centralized hub of information on the status of the business, becomes just as much a liability as it is an asset. How then should it be secured?

In the previous series of posts, seven high-level considerations were put forward for securing a database application. Revisiting these considerations in the context of a data warehouse provides some changes in guidance:

NOTE If you haven’t read the original posts associated with each bulletpoint below, please click on the provided link to provide background on the additional commentary.

Harden the Server - No changes here. The software, operating system, network, and physical environment supporting SQL Server requires the same protection as before.

Regulate Network Connectivity – The guidance provided here is intended to make SQL Server harder to locate on the network by all but the intended applications. As the data warehouse is expected to provide flexible access to a variety of applications, hiding the SQL Server instance, disabling the SQL Browser, and employing application-specific endpoints doesn't exactly support this objective. Configuring non-standard TCP ports and named pipes some albeit little additional security to the data warehouse.

Secure the Authentication Process – The exclusive use of Windows authentication is strongly encouraged so that you may more accurately monitor end-user activity.

Assign Minimal Permissions – Users of the data warehouse typically require read only permission to the tables and views in their domain. By carving the database up into separate schemas (with differing owners to break ownership chains), custom database roles can be built with appropriate access to individual schemas. Synonyms and views can be employed to make database objects, e.g. conformed dimension tables, accessible across multiple domains. Another approach to assigning minimal permissions is to introduce an intermediate application between the user and the data warehouse. Such an application should support a fine-grain security model and query the data warehouse on behalf of the user. Examples of such applications include SQL Server Analysis Services and the soon to be release Business Intelligence Semantic Model (BISM).

NOTE Column-level security is often suggested in data warehousing scenarios. Please be aware that the has_perms_by_name() function, which is called directly and indirectly by many applications to determine which objects are available to a user, will only identify a table as accessible if all fields in the table are accessible to the user. As a result, tables on which column-level restrictions have been defined may not appear to the user in their application. Please test the impact of column-level security on the specific applications in your portfolio before employing this feature.

Encrypt the Data – Transparent Data Encryption (TDE) is a must if the data warehouse contains any data of significance. This will prevent someone from walking off with a backup of the database which could then be used to learn the ins-and-outs of your business. IPSec and SSL to encrypt over the wire should also be considered even if the database is used exclusively over a secured, internal network. Encryption over the wire prevents malicious users from intercepting data they might not otherwise have access to and/or by-passing the SQL audit.

Defend against SQL Injection – If formal applications are built on the data warehouse, the standard guidance on defending against SQL injection applies. However, the kinds of the tools users typically use to interact with the data warehouse aren't usually very susceptible to this kind of attack. Still, using the SQL audit feature to keep track of who is using which tools and then following up with a brief investigation of those tools is a good idea.

Monitor & Enforce Security Policies – The guidance here is relatively unchanged though you may wish to apply the SQL audit a bit more liberally where there is sensitive data. As mentioned in the related post, there is overhead associated with the audit but the frequency of queries within the data warehouse is often less than an active OLTP application so that the cumulative overhead of the audit should be lower.

Regulate Network Connectivity

2011-06-27T19:20:00Z

SQL Server accepts client connections over specific network protocols and (for lack of a better term) associated channels. The combination of network protocol, a channel, and the type of communication taking place over it is managed from within SQL Server as an endpoint.

SQL Server comes pre-configured with 5 endpoints intended to support traditional client-server communications. These are identified in this table:

Endpoint	Network Protocol	Channel
Dedicated Admin Connection	TCP	This endpoint is associated with a TCP port. With default instances of SQL Server, this is typically TCP port 1434. With named instances of SQL Server, this is typically a TCP port dynamically assigned during the start-up of the SQL Server service. With both named and default instances, the TCP port can be set to a differing (and fixed) value through the SQL Server Configuration Manager.
TSQL Local Machine	Shared Memory	This endpoint is not associated with a channel as the protocol directly transfers data between client and server through memory.
TSQL Named Pipes	Named Pipes	This endpoint is associated with a named pipe. With default instances of SQL Server, the server identifies this named pipe as \\.\pipe\sql\query and clients identify this named pipe as \\<computername>\pipe\sql\query. With named instances of SQL Server, the server identifies this named pipe as \\.\pipe\MSSQL$<instance name>\sql\query and clients identify this named pipe as \\<computername>\pipe\MSSQL$<instancename>\sql\query. The name of the pipe can be changed using the SQL Server Configuration Manager.
TSQL Default TCP	TCP	This endpoint is associated with a TCP port. With default instances of SQL Server, this is typically TCP port 1433. With named instances of SQL Server, this is typically a TCP port dynamically assigned during the start-up of the SQL Server service. With both named and default instances, the TCP port can be set to a differing (and fixed) value through the SQL Server Configuration Manager.
TSQL Default VIA	VIA	This endpoint is associated with VIA port 0:1433 on both default and named instances of SQL Server.

As the Shared Memory protocol can only be used locally on the server, the Dedicated Admin Connection (DAC) is configured (by default) for local connections only, and VIA is a deprecated protocol, the endpoints associated with these will not be discussed further in this post. That leaves the TSQL Named Pipes and TSQL Default TCP endpoints.

NOTE In the context of this discussion, TCP and Named Pipes refer to protocols employed by SQL Server. The use of one protocol or the other affects how SQL Server (and the client) manage and interact buffers through which data is exchanged. These protocols are employed a higher level in the networking stack than protocol employed by the operating system, typically TCP/IP. To illustrate this, consider that communications via the (SQL Server) Named Pipes protocol are handled over TCP port 445.

Returning to the concept of a client connecting to a SQL Server instance, the client must know a combination of network protocol and channel over which SQL Server is accepting connection requests. The client may “guess” these using well-known combinations, such as a default instance of SQL Server using the TCP protocol to take connections via TCP port 1433. However, if well-known combinations are not being used by the instance, the client must either be provided the network protocol and channel information in advance or a secondary service, the SQL Server Browser Windows service, must be available to provide this information on request.

The SQL Server Browser Windows service (SQL Browser) listens on UDP port 1434 for request for information. It responds to requesters with the network protocol and channel information (as well as other metadata) for all SQL Server (Database Engine) instances on the server. A client can then use this information to attempt a connection to a SQL Server instance.

To secure network connectivity to SQL Server consider moving instances from well-known network protocol and channel combinations. Consider too disabling the SQL Server Browser Windows service or simply registering a sensitive instance of SQL Server as hidden so that SQL Browser does not return any information on it to requestors. Information on changing TCP ports and pipe names is found here and here, respectively. Information on hiding a SQL Server instance is found here.

To take this one step further, consider establishing application-specific TCP endpoints, each associated with a differing TCP port. Doing so provides you the ability to carefully administer how differing applications connect to an instance of SQL Server. Should there be a need to disable connectivity for one application, its endpoint can be stopped while other endpoints remain active for another applications. Information on configuring multiple TCP ports for a SQL Server instance is found here. Information on granting and revoking endpoint permissions are found here and here.

NOTE Only the TCP and VIA network protocols support the creation of multiple endpoints.

DEMONSTRATION To review the steps required to configure an application-specific TCP endpoint, please review this post.

Monitor & Enforce Security Policies

2011-06-27T18:20:00Z

Once a security policy is defined, it then needs to be monitored and enforced to ensure compliance. SQL Server provides two features for this: policy-based management and audit.

Policy-Based Management

Introduced with SQL Server 2008, policy-based management (PBM) allows administrators to define conditions for various facets of the database environment. These conditions, combined to form policies, are then evaluated against policy targets on-demand, on-schedule, or automatically as changes take place. For more information on PBM, check out this document in Books Online.

NOTE Many of the facets of interest from a security perspective only support on-demand or on-schedule evaluation. To automate evaluation, consider using the on-schedule option and defining alerts as described in the Configuring Alerts to Notify Policy Administrators of Policy Failures section of the previously referenced document.

SQL Server allows you to define custom policies but also provides access to a number of policies based on established best practices, many of which are security related. Information on importing and employing these best practice policies can be found in this tutorial.

NOTE Many of these best practices are also evaluated through the the SQL Server Best Practices Analyzer and System Center Advisor. If these tools are not part of your administrative toolkit, it’s is well worth exploring them through the provided links.

Finally, PBM, as implemented in SQL Server 2008 and 2008 R2, provides some multi-server capabilities. Still, the Enterprise Policy Management Framework, available on Codeplex and documented in this white paper, extends PBM, making it a more effective tool for administrators of larger environments. The Framework has the added advantage of bringing PBM capabilities to SQL Server 2005 and 2000 deployments.

Audit

SQL Server audit allows information related to various events occurring within a database or SQL Server instance to be recorded for later review. Audit works through the definition of an audit (log) to which data is recorded and the identification of events to record to that log through audit specifications. For a more in-depth review of SQL Server audit, please review this document.

SQL Server audit supports three different audit logs: a binary file, the Windows Application log, and the Windows Security log. The Windows Security log is the most secure of these three options as special permissions are required to write to this log. These permissions also make configuration of the Windows Security log as the audit destination a bit more challenging. To read more about configuring the Windows Security log to accept audit records, please review this document.

NOTE If the Windows Security or Application log is used as the audit log, tools such as System Center Operations Manager can be used to consolidate audit information from across multiple servers.

When configuring audit specifications, keep in mind that recording event data to the logs adds overhead to the audited actions. While event data can be queued in memory before being written to the audit log, the best option for minimizing the performance overhead is to simply be selective of the events being audited. With some event types, those events recorded to the audit log can be restricted to those associated with a specific database object and/or user or role.

SQL Injection Demo

2011-06-26T13:21:00Z

The purpose of this post is to demonstrate a simple SQL injection attack. For information on securing a database against SQL injection, please review this post.

NOTE Implementing an actual SQL injection attack against a system for which you have not been provided explicit authorization may result in your prosecution. This post is intended to educate folks on the basic pattern so that they may plan their defense. The pattern/technique demonstrated here is highly simplistic and widely demonstrated and discussed in forums related to SQL injection defense.

The first step is to set up a demonstration database. In this database, a few tables and a stored procedure using dynamic SQL will later be created:

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SqlInjectionDemo') DROP DATABASE SqlInjectionDemo;
GO

CREATE DATABASE SqlInjectionDemo;
GO

Next, a couple tables representing products in an online product catalog will be setup and populated:

USE SqlInjectionDemo;
GO

CREATE TABLE dbo.ProductCategory (ProductCategoryID int not null, Name nvarchar(50));
GO
INSERT INTO dbo.ProductCategory VALUES (1, 'Electronics'), (2, 'Media');
GO

CREATE TABLE dbo.Product (ProductID int not null, ProductCategoryID int not null);
GO
INSERT INTO dbo.Product VALUES (1,1), (2,1), (3,1), (4, 2), (5, 2), (6, 2);
GO

To access the products in the product catalog, a stored procedure will be employed which accepts as its parameter the name of the product category on which to restrict the products returned:

CREATE PROC dbo.spGetProducts @ProductCategoryName NVARCHAR(50)
AS
   DECLARE @sql NVARCHAR(MAX)='';

   SET @sql = 'SELECT a.* '+
               'FROM dbo.Product a '+
               'INNER JOIN dbo.ProductCategory b '+
                   'ON a.ProductCategoryID=b.ProductCategoryID '+
               'WHERE b.NAME='''+ @ProductCategoryName +''''

EXEC(@sql)
GO

The application is intended to call this stored procedure passing the name of a product category. When this takes place, the proc returns a limited set of data:

EXEC spGetProducts N'Electronics'
GO

ProductID   ProductCategoryID
----------- -----------------
1           1
2           1
3           1

(3 row(s) affected)

However, using SQL injection techniques, a malicious user could alter the statement logic to return all products:

EXEC spGetProducts N''' OR ''1''=''1'
GO

ProductID   ProductCategoryID
----------- -----------------
1           1
2           1
3           1
4           2
5           2
6           2

(6 row(s) affected)

Going one step further, a malicious user could terminate the original SQL statement and append another one disclosing still more information:

EXEC spGetProducts N''';SELECT * FROM sysobjects WHERE NAME <> '''
GO

ProductID ProductCategoryID
----------- -----------------

(0 row(s) affected)

name
--------------------
sysrscols
sysrowsets
sysallocunits
...
(56 row(s) affected)

To defend against this attack, the stored procedure simply needs to be rewritten as follows:

ALTER PROC dbo.spGetProducts @ProductCategoryName NVARCHAR(50)
AS
   SELECT a.*
   FROM dbo.Product a
   INNER JOIN dbo.ProductCategory b
      ON a.ProductCategoryID=b.ProductCategoryID
   WHERE b.NAME = @ProductCategoryName
GO

To be fair to developers, not all dynamic SQL is as easily replaced with parameterized SQL. Still, avoiding dynamic SQL is the key to preventing SQL injection.

To clean up the demo environment, execute the following statements:

USE master;
GO

DROP DATABASE SqlInjectionDemo;
GO