Microsoft Azure.... the blue loo

Microsoft announced it's Azure service last week. In their words Azure is a tool that allows you to "Build new applications in the cloud - or use interoperable services that run on Microsoft infrastructure to extend and enhance your existing applications". From our, the BI developers', perspective Azure is hosting not just data but also Visual Studio solutions ie developing and deployment within the cloud.

Not that it's limited to Microsoft tools and environments of course.

So I immediately joined the CTP for SQL Services. I've downloaded the SQL Data Services SDK and have been placed on a waitlist for access to the cloud.

From what I can tell so far, you can use REST or SOAP for CRUD operations and LINQ for queries. Interestingly, there is no 'schema' as such - new properties can be added to entities as required - no need to add a new 'column'. What that means in practical terms I'm yet to find out. I suppose it will become apparent once I can access the cloud.

More info will follow shortly.

What’s New in SQL 2008 – Part 3 – Table Type

The new Table type allows you to save a table definition in the database which can be reused later to define table variables, stored procedure parameters and functions. To create a Type, we use the CREATE TYPE clause, for example:

CREATE TYPE dbo.ProductList as TABLE (

    ProductName varchar(200) NULL,

    ProductCategory varchar(200) NOT NULL)

Important Notes:

• The metadata is visible using sys.table_types
  

• Variables/parameters cannot be set to NULL – if no table is supplied, an empty table is generated
  

• Not affected by transaction rollbacks
  

• Do not cause recompilations
  

Examples

Create Table-Type Variables	DECLARE @t as dbo.ProductList; INSERT INTO @t VALUES ('Grey Slate Paver 10x10','Paver'), ('Blue Slate Paver 10x10','Paver'), ('Heritage Blue 12x6','Brick')
Creating a Stored Procedure with a Table-typed Parameter	CREATE PROC dbo.uspGetOrdersforProduct ( @t as dbo.ProductList READONLY, @StartDate as Date, @EndDate as Date ) AS SELECT o.OrderId, o.OrderDate, o.CustomerId, o.ProductId, o.Amount, o.Qty FROM tOrder as o INNER JOIN tProduct t on t.ProductId = o.ProductId INNER JOIN @t as f on f.ProductCategory = t.ProductCategory and (t.ProductName is NULL or t.ProductName = f.ProductName) WHERE o.OrderDate between @StartDate and @EndDate
Passing Table-parameters into Stored Procedures	DECLARE @t as dbo.ProductList; INSERT INTO @t VALUES (NULL,'Paver'), (NULL,'Brick') EXEC dbo.uspGetOrdersforProduct @t

What’s New in SQL 2008 – Part 2 – Data Types

Date and Time

Data Type	Size (bytes)	Example	Notes	Client Providers
DATE	3	'2009-10-01'		OLEDB: DBTYPE_DBDATE ADO.Net: DateTime ODBC: SQL_DATE
TIME	3-5	'13:12:00.1234567'	Accurate to 100 nanoseconds	OLEDB: DBTYPE_DBTIME ADO.Net: TimeSpan ODBC: SQL_TIME
DATETIME2	6-8	'2009-10-01 13:12:00.1234567'	Accurate to 100 nanoseconds Can specify precision ie decimal places eg DATETIME(3)	OLEDB: DBTYPE_DBTIMESTAMP ADO.Net: DateTime ODBC: SQL_TIMESTAMP
DATETIMEOFFSET	8-10	'2009-10-01 13:12:00.1234567+02:00'	Accurate to 100 nanoseconds	OLEDB: DBTYPE_DBTIMESTAMP ADO.Net: DateTime ODBC: SQL_TIMESTAMP

There are a number of new functions which can also be used with these new datatypes including:

• SYSDATETIME (returns DATETIME2), SYSUTCDATETIME, SYSDATETIMEOFFSET (includes time zone in return value) – Current Date and Time
  
• SWITCHOFFSET – to change the timezone for a DATETIMEOFFSET value
  
• TODATETIMEOFFSET – convert non-timezone-aware values to time-zone-aware
  

Other

Data Type	Size (bytes)	Useage	Returns	Notes
HIERARCHYID	VARBINARY eg 5 bytes for 100k records at 6-Levels	DECLARE @h hierarchyid = hierarchyid::GetRoot() DECLARE @c hierarchyid SET @c = @h.GetDescendant(NULL, NULL) SELECT @c.ToString()	'/1/'	Represents position in a hierarchy Actually a CLR function with several methods eg GetDescendant/IsDescendantOf GetLevel/GetRoot GetReParentedValue ToString/Read/Write (CLR)

What's New in SQL 2008 - Part 1 - Functionality

Function	Usage Example	Notes
Declare & Initialise	DECLARE @iValue as integer = 100;
Compound Assignments	SET @iValue -= 50 UPDATE dimCustomer SET Balance += 200	+= -= *= /= %=
Multi-Row Inserts Using VALUES	INSERT INTO dimProduct (Label, BusinessKey,LastUpdatedBy,LastUpdatedDate) VALUES ('Grey Slate Paver 10x10',1111,'dbo','20091001 13:12:00'), ('Blue Slate Paver 10x10',1112,'dbo','20091001 13:12:00'), ('Black Slate Paver 10x10',1113,'dbo','20091001 13:12:00')	The insert is executed as a single transaction. Interestingly, it can also be used like this: SELECT * FROM (VALUES ('Grey Slate Paver 10x10',1111,'dbo','20091001 13:12:00') ('Blue Slate Paver 10x10',1112,'dbo','20091001 13:12:00') ('Black Slate Paver 10x10',1113,'dbo','20091001 13:12:00') ) as p (Label, BusinessKey,LastUpdatedBy,LastUpdatedDate)
MERGE	MERGE INTO dbo.dimProduct as d USING dbo.dimProductStage as s ON d.ProductKey = s.ProductId WHEN MATCHED THEN UPDATE SET d.ProductName = s.ProductName, d.ProductCategory = s.ProductCategory WHEN NOT MATCHED THEN INSERT (ProductName,ProductCategory) VALUES (s.ProductName, s.ProductCategory) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, deleted.ProductId as DeletedId, inserted.ProductId as InsertedId;
Grouping Sets	SELECT Year(OrderDate) as OrderYear, SalespersonId, OrderType, count(OrderId) as OrderCount, sum(Amount) as OrderAmount FROM factOrders GROUP BY GROUPING SETS ( (OrderType, Year(OrderDate)), (SalespersonId,OrderType, Year(OrderDate)), (SalespersonId, Year(OrderDate)), (Year(OrderDate)), ());	() empty brackets represents an 'ALL' grouping
Filtered Indexes	CREATE NONCLUSTERED INDEX idxOrders_Product ON factOrders(OrderId,ProductId) WHERE CreditFlag IS NULL
Change Data Capture	** to be discussed in further detail in an upcoming post **	Tracks data changes into a table. Enabled using sys.sp_cdc_enable_db or sys.sp_cdc_enable_table Disabled using sys.sp_cdc_disable_db or sys.sp_cdc_disable_table Uses SQL Agent

SharePoint Magazine - Part 2 available now

My next article for SharePoint Magazine is available now.

http://sharepointmagazine.net/technical/administration/sell-sell-sell-why-build-a-dashboard-anyway-part-2-of-6

PerformancePoint Planning: Error When Deleting Members of a Member Set

Unfortunately I discovered a problem in PPS Planning today which caused me a few headaches... thought I would pass it on to you!! The issue occurs when editing/creating a dimension member set... a save will fail if you do an outdent and a delete member immediately afterwards. Let me demonstrate.

Let's presume you have a structure something like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Salaries
--- --- --- --- Base Salary
--- --- --- --- Bonuses
--- --- --- --- Allowances
--- --- --- Tax Expense

But you want to remove the Salaries level and end up with a structure that looks like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Base Salary
--- --- --- Bonuses
--- --- --- Allowances
--- --- Tax Expense

If you do this:

1. Outdent the 3 leaf level members
2. Remove the Salary level member
3. Save

You will get an error when you save similar to the following:

In order for it to work correctly, you MUST (in this order):

1. Outdent the 3 leaf level members
2. Save
3. Remove the Salary level member
4. Save
   

I have logged this problem on Microsoft Connect. Please feel free to vote! https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=375153&SiteID=181

SharePoint Magazine - Part 1 Out Now!

I've commenced a 6-part series on building PerformancePoint dashboards in SharePoint which you can read at SharePoint Magazine.

I'll be releasing a new part each week for the next 6 weeks covering the various aspects of building dashboards - including why bother at all!

Have a read if you're interested. Feedback so far has been excellent.