SQL Kit

I was playing around with a recently downloaded Beta 2 of VS 2010. As of me , I work a lot with Databases and Data warehouses but I did have an opportunity to work with C# based development using ADO.NET entity framework sometime back.

I am a big fan or writing test cases and making sure that I achieve a good code coverage. With VS 2010 I can write test cases for database objects like stored procedures. In this blog post I am going to write a very small bit about how you can write test cases for SQL Queries using VS 2010. (You don’t need any NUnit or csUnit as you might need in previous versions of VS and even in that case I don’t see an easy way as VS 2010 provides).

Start VS 2010 and go to ribbon on the top

Click New Test and select Database Unit Test

Configure the database connection string and Project name and other stuff

 

You will have a window which will look like this. Click on the CREATE NEW link

 

You can my TestMyProc.cs . This is a C# class. You can Paste you SQL code in the designer and the C# class will wrap this and execute it using ADO.NET . You need not worry writing the TESTFIXTURES and etc etc

The designer has this commented bit on top which is self explanatory.

/*
Add T-SQL statements here to exercise the database object
that you want to test.

To test a stored procedure, invoke it here by adding
an EXEC statement that has appropriate parameters.

In the lower pane, you can add test conditions that verify
whether the results of your T-SQL statements match what
you expect.
*/

Ok . Now lest write some test . Lets Begin with a negative on. Which Will fail.

I have configured my Database to connect to adventureworks.

The SQL Code I want to test is

SELECT * FROM HumanResources.Employee   WHERE BusinessEntityID  = 1 --will fail

Now go down to test condition panel. Here you can specify the conditions which will be based on what is the expected output. So Lets say I don’t expect any result set.

After you have configured the Condition you need to run this test.

The test result panel will have the below output stating that test has failed.

Now change the SQL Query to

SELECT * FROM HumanResources.Employee   WHERE BusinessEntityID  = -1 --will succeed

and run the test again. The test result panel will show PASSED

So we have written our first Unit Test to Test a SQL Query. The same thing can be extended to Stored Procedures. I don’t know as of now if it supports MDX unit testing out of the box but if go the code behind (F7) you will see a C# class. You can add ADOMD or AMO and test MDX , XMLA or anything else in theory. I have not tried it yet though.

Happy Test Driven Development for Database Guys!!. I will be blogging more on this.

I got a email today at 4:00 PM from Microsoft MVP award which read

Dear Ashwani Roy,
Congratulations! We are pleased to present you with the 2009 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in SQL Server technical communities during the past year

I would like to thank UK SQL Server Community , my blog readers my peers and friends and I hope I will keep adding value to community.

SSAS 2008 has improved Dimension and Aggregation Designer, new Attribute Relationship Designer, Optimize performance with block computations mode and dynamic management views for enhanced resource monitoring.SSRS 2008 reports has the unique data format of Tablix which allows writing reports with combined advantage of Table and Matrix formats. It can integrate with Microsoft Office SharePoint Server 2007 for central delivery and management of business insight. It also enables users to quickly gain insight into complex sets of data by displaying data graphically with enhanced visualization capabilities. Performance has improved drastically for situations where you are generating large reports.

SSIS Pipeline is optimized to enable more parallel loading of data. You can write script components in C# now. It comes with improved scalability with thread pooling and enhanced lookup transformations. It also performs more functional and scalable data transfers with the improved SQL Server Import and Export Wizard.

Other engine features that you need to be aware of are, improvements to partitioning, change data capture to enable easy extraction of changed data from a production system, backup and table compression which enables better performance and less storage space utilisation. It also comes with optimized Star-Join, improved lock escalation handling, Merge statements and other advanced T SQL enhancements.

 

The slides and demos are available here for download.

Have a look at this query

WITH

MEMBER [Measures].[REFUNDS] AS [Measures].[Internet Sales Amount] – [Measures].[Internet Sales Amount] * .4

MEMBER [Measures].[PROFIT] AS [Measures].[Internet Sales Amount] – [Measures].[REFUNDS]

MEMBER [Product].[Product].[Top10] AS

sum(

TOPCOUNT([Product].[Product].[Product].MEMBERS

, 10

, [Measures].[Internet Sales Amount]

)

,

([Measures].CurrentMember)

)

MEMBER [Product].[Product].[Top10PercOfTotal] AS [Product].[Product].[Top10]/[Product].[Product].[All Products]

SELECT

{

[Measures].[Internet Sales Amount],

[Measures].[REFUNDS],

[Measures].[PROFIT]

} ON COLUMNS,

{

[Product].[Product].[Top10]

,[Product].[Product].[Top10PercOfTotal]

,[Product].[Product].[All Products]

}

ON ROWS

FROM [Adventure Works]

This yeilds same result as yours ie. not a flat cell by cell division

	Internet Sales Amount	REFUNDS	PROFIT
Top10	$10,355,525.92	$6,213,315.55	$4,142,210.37
Top10PercOfTotal	0.35272454	0.211634724	0.141089816
All Products	$29,358,677.22	$17,615,206.33	$11,743,470.89

 

I don’t expect it to produce this result . If the computations was going as planned the output should be 0.35272454  (35 %) for each.

I have altered the SOLVE ORDER (this is the property which determines the order of cell computation)

Here is modified query

WITH

MEMBER [Measures].[REFUNDS] AS [Measures].[Internet Sales Amount] - [Measures].[Internet Sales Amount] * .4,SOLVE_ORDER = 1

MEMBER [Measures].[PROFIT] AS   [Measures].[Internet Sales Amount] - [Measures].[REFUNDS],SOLVE_ORDER = 2

MEMBER [Product].[Product].[Top10] AS

 sum(

  TOPCOUNT([Product].[Product].[Product].MEMBERS

  , 10

  , [Measures].[Internet Sales Amount]

  )

  ,

  ([Measures].CurrentMember)

  )

,SOLVE_ORDER = 3

MEMBER [Product].[Product].[Top10PercOfTotal] AS 

[Product].[Product].[Top10]/[Product].[Product].[All Products]

, FORMAT_STRING = "Percent"

 , SOLVE_ORDER = 4

SELECT 

 {

  [Measures].[Internet Sales Amount],

  [Measures].[REFUNDS],

  [Measures].[PROFIT]

  } ON COLUMNS,

 {

[Product].[Product].[Top10]

,[Product].[Product].[Top10PercOfTotal]

,[Product].[Product].[All Products]

  } 

  ON ROWS

FROM [Adventure Works]

·

	Internet Sales Amount	REFUNDS	PROFIT
Top10	$10,355,525.92	6213315.552	4142210.368
Top10PercOfTotal	35.27%	35.27%	35.27%
All Products	$29,358,677.22	$17,615,206.33	$11,743,470.89

 

There you go. SOLVE_ORDER determines the series of how cell computation  will be formed. One with smallest SOLVE_ORDER will be evaluated first.

Hope this explains SOLVE_ORDER and how it works.

The evening’s agenda includes the following presentations (see Events section for more details):

What’s new in SQL Server 2008 for BI

Presented by Ashwani Roy, MCTS MCITP MCAD MCP

We’re also pleased to let you know that SQL Server MVP, Simon Sabin will be presenting. Details about this presentation will be made available shortly.

 

Location:
Bank Of America 
5 Canada Square
London E14 5AQ
United Kingdom

Register @ http://www.sqlpass.org.uk/

Excel 2007 provides many APIs which are quite powerful from analytics point of view.Not all of them are exposed via the UI though ( for some reason … ). SQL Server MVP Greg Galloway has developed this very cool tool which is EXCEL 2007 add-in and lets you do very cool stuff.

You know that calculated members are evaluated on the fly.But the only way you can have this calculated member available from browsing is by doing this physically in the cube. There is a way that you can do it in the excel itself .These are called Private Calculated Members

Simple ratios or differences and stuff that are very specific to a (or small group) of cube users can be put in here , rather than cluttering the cube.

Limitation: If you run "OLAP Tools… Convert to Formulas" on a PivotTable with private calculated members, the private calculated members will show N/A. There is no known workaround at this point other than having your OLAP administrator define these calculated members in the cube itself.

There is much more to this too and the download and more information is available here http://www.codeplex.com/OlapPivotTableExtend.

Some time back I took a small session for a group of SQL Server users about some cool stuff in SQL Server 2008 from the T SQL point of view. Some of the students wanted to have access to the scripts , so here they are

MERGE STATEMENT and OUTPUT CLAUSE

drop table Source
drop table Destination

go

CREATE TABLE Source (id int,val varchar(56));

CREATE TABLE Destination (id int,val varchar(56));

GO

INSERT Source VALUES (1,’A');
INSERT Source VALUES (2,’B');– This will be inserted

INSERT Destination VALUES (1,’q');–this will be updated to A
INSERT Destination VALUES (6,’w');
INSERT Destination VALUES (8,’r');

GO

MERGE Destination D — target table

        USING Source S — source table

        ON S.ID = D.ID

        WHEN MATCHED  THEN

                UPDATE set D.val = S.val

          WHEN NOT MATCHED THEN

                — insert a row if the stock is newly acquired

                INSERT VALUES (S.ID, S.VAL)

        — output details of INSERT/UPDATE/DELETE operations

        — made on the target table

        OUTPUT $action, inserted.*, deleted.*; —this is not required in Merge statement I have kept it here just to demostrate that from SQL 2005 onwards
                                                       — OUTOUT clause can be used along with INSERTED , DELELED to get the what has been done as a result to DML action
                                                       — Might help in auditing the DML actions
/*output of the OUTPUT CLAUSE IS

$action    id    val        id        val— I can see what is Inserted and what is Deleted
INSERT    2    B        NULL    NULL
UPDATE    1    A        1        q

*/                                                      

SELECT * FROM Destination;–See the result

GO

 

GROUPING SETS

GROUPING SETS are a new feature of SQL Server 2008. Using them will allow multiple groupings to be returned in one record set.

We will grouping on City and StateProvice in the same query

use AdventureWorksLT
go
—old way —————-
SELECT NULL, City, COUNT(StateProvince) AS Nbr
FROM SalesLT.Address
GROUP BY  City
UNION ALL
SELECT StateProvince, NULL, COUNT(StateProvince) AS Nbr
FROM SalesLT.Address
GROUP BY StateProvince

–useing GROUPING SETS in SQL 2008
SELECT StateProvince, City, COUNT(StateProvince) AS Nbr
FROM SalesLT.Address
GROUP BY
GROUPING SETS
(
  (City),
(StateProvince)
–()
  );

TABLE VALUED PARAMTERS

In this example I will pass a table valued parameter in the stored procedure

–USING Table valued input Parameters
CREATE TYPE myTableType AS TABLE (id INT, name NVARCHAR(100),qty INT);

ALTER PROCEDURE myProc (@tvp myTableType READONLY) AS

declare @table TABLE (id int)

    UPDATE Inventory SET
    qty += s.qty
    FROM Inventory AS i INNER JOIN     @tvp AS tvp
    ON i.id = tvp.id
GO

 

NEW DATE TIME DATA TYPES

– IN SQL 2008 INSTANCE
declare @datetime datetime,@DATETIME2 DATETIME2
select @datetime = ‘1500-03-04 15:43:26.857′–this will give out of range exception asyou cannot have anything before 1900
select @datetime2 = ‘1500-03-04 15:43:26.857′– there you can do it
SELECT @DATETIME2

DECLARE @DATE DATE , @TIME TIME
SELECT @DATE =’1900-01-01′,@TIME = ‘15:43:26.857′
SELECT @DATE ,@TIME

DECLARE @DATETIMEOFFSET datetimeoffset
SELECT @DATETIMEOFFSET = ‘2005-09-08 12:20:19.345 -08:00′    — stored datetime with OFFSET Lets say you want datetime datetime captured in LONDON server transfrom to US and lets assume the offset is 8 hrs
SELECT @DATETIMEOFFSET

————————————————————————————————————————–

Thats all for now!! I though it will be easier to get the scripts from here rather than download from my sky drive. It is not a very extensive list , it just covers the 1 hours session which I took for beginner sql users.

 

What is CDC :-

Change data capture (CDC) is a set of software design pattern used to determine (and track) the data that has changed so that action can be taken using the changed data. Also, Change data capture (CDC) is an approach to data integration that is based on the identification, capture and delivery of the changes made to enterprise data sources.

It is a very common requirement for a data warehouse load package is to determine what has changed in the source systems and load this data into the warehouse.

All I wanted to give was a script that you can use along with adventure works Database to see what are basic stuff in CDC and how it works. off course you can use it in your Data warehouse SSIS packages.

—————————————————————

USE AdventureWorks
GO
–Enable Change Data Capture on the Database–
EXEC sp_cdc_enable_db
GO

–Enable Change Tracking on a table–
EXEC sp_cdc_enable_table
    @source_schema = ‘HumanResources’ ,@source_name = ‘Employee’,@supports_net_changes = 1,@role_name = null
go
–Simulate a Data Change —
UPDATE HumanResources.Employee
SET   ManagerID = 10 WHERE Title = ‘Production Technician – WC60′
go

select * from HumanResources.Employee where Title = ‘Production Technician – WC60′

–Get the Data —
select sys.fn_cdc_get_min_lsn(‘HumanResources_Employee’),sys.fn_cdc_get_max_lsn() 
go

select *  from cdc.fn_cdc_get_all_changes_HumanResources_Employee
(0×00000036000017400078,0×0000003600001EC1006C,’all’)

declare @minLSN varbinary(max),@maxLsn varbinary(max)

select @minLSN = sys.fn_cdc_get_min_lsn(‘HumanResources_Employee’)
,@maxlsn=sys.fn_cdc_get_max_lsn() 

select *  from cdc.fn_cdc_get_all_changes_HumanResources_Employee
(@minLSN ,@maxLsn ,’all’)

–Disable CDC as I dont want this running on my Laptop–
EXEC sp_cdc_disable_db
GO
/*
    WHEN 1 THEN ‘delete’
    WHEN 2 THEN ‘insert’
    WHEN 4 THEN ‘update’

*/

–exec sp_configure

—————————————————————————————————–

With SQL Server 2005 onwards we are provided with a set of DMVs. These DMVs allow us to monitor facts like which index is being used and which are not , where are IO bottlenecks , what is cache hit ratio and other stuff. But what about analysis service.

What if I want to know which of my hierarchies are being used , which are the aggregations that are not being used etc etc.

Well from SQL 2008 onwards you can have this capability.

AS 2008 has

–>  4 DBSCHEMA (Database Schema DMVs)

1. $SYSTEM.DBSCHEMA_CATALOGS

2. $SYSTEM.DBSCHEMA_COLUMNS

3. $SYSTEM.DBSCHEMA_PROVIDER_TYPES

4. $SYSTEM.DBSCHEMA_TABLES

–> 10 DMSCHEMA DMVs

1. $SYSTEM.DMSCHEMA_MINING_COLUMNS

2. $SYSTEM.DMSCHEMA_MINING_FUNCTIONS

3. $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT

4. $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT_PMML

5. $SYSTEM.DMSCHEMA_MINING_MODEL_XML

6. $SYSTEM.DMSCHEMA_MINING_MODELS

7. $SYSTEM.DMSCHEMA_MINING_SERVICE_PARAMETERS

8. $SYSTEM.DMSCHEMA_MINING_SERVICES

9. $SYSTEM.DMSCHEMA_MINING_STRUCTURE_COLUMNS

10. $SYSTEM.DMSCHEMA_MINING_STRUCTURES

These DMVs describe data mining models in the Analysis Services database.

–> There are 13 DMVs which describe the Meta Data of Analysis service data base (cube,partitions,hierarchies etc)

1. $SYSTEM.MDSCHEMA_CUBES

2. $SYSTEM.MDSCHEMA_DIMENSIONS

3. $SYSTEM.MDSCHEMA_FUNCTIONS

4. $SYSTEM.MDSCHEMA_HIERARCHIES

5. $SYSTEM.MDSCHEMA_INPUT_DATASOURCES

6. $SYSTEM.MDSCHEMA_KPIS

7. $SYSTEM.MDSCHEMA_LEVELS

8. $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS

9. $SYSTEM.MDSCHEMA_MEASUREGROUPS

10. $SYSTEM.MDSCHEMA_MEASURES

11. $SYSTEM.MDSCHEMA_MEMBERS

12. $SYSTEM.MDSCHEMA_PROPERTIES

13. $SYSTEM.MDSCHEMA_SETS

These DMVs expose a Whole New gold mine for Analysis and monitoring of SSAS. Have a look at some of the DMX queries below which use these DMVs to expose some very useful information.

1. Open SQL Server Management Studio

2. Open a DMX query Window and connect to Adventure Works Cube .(if you dont have adventure works cube you can download it from codeplex.com)

3. paste this query

SELECT *
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME = ‘Adventure Works’
   AND MEASUREGROUP_NAME = ‘Internet Sales’
ORDER BY [MEASUREGROUP_NAME]

You will see that you have information about all the measures in the Measure Group

here is sample output (Only selected few column due to space constraint)

CATALOG_NAME	SCHEMA_NAME	CUBE_NAME	MEASURE_NAME	MEASURE_UNIQUE_NAME	DEFAULT_FORMAT_STRING
Adventure Works DW		Adventure Works	Internet Order Quantity	[Measures].[Internet Order Quantity]	#,#
Adventure Works DW		Adventure Works	Internet Extended Amount	[Measures].[Internet Extended Amount]	Currency
Adventure Works DW		Adventure Works	Internet Tax Amount	[Measures].[Internet Tax Amount]	Currency
Adventure Works DW		Adventure Works	Internet Freight Cost	[Measures].[Internet Freight Cost]	Currency
Adventure Works DW		Adventure Works	Internet Unit Price	[Measures].[Internet Unit Price]	Currency
Adventure Works DW		Adventure Works	Internet Total Product Cost	[Measures].[Internet Total Product Cost]	Currency
Adventure Works DW		Adventure Works	Internet Standard Product Cost	[Measures].[Internet Standard Product Cost]	Currency

Now that we know how useful can this DMV be here are some scripts that can help you play around with

select * from $system.dbschema_tables
select * from $system.dbschema_columns
select * from $system.dbschema_catalogs

select * from $system.mdschema_cubes
select * from $system.mdschema_hierarchies

select * from [Adventure Works].[$Product] –database dimension

select * from $system.discover_commands
select * from $system.discover_connections
select * from $system.discover_memoryusage
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity where object_reads > 0
select * from $system.discover_partition_stat

SQL Server MVP Darren Gosbell has blogged about it and I suggest you look at his blog http://geekswithblogs.net/darrengosbell/Default.aspx 

(even otherwise it is a very good blog)