Ashwani Roy's BI Blog

I am preparing a demo for User Group in UK of how F# can used with Business Intelligence. Frankly speaking I don’t know the end result but as I go along I am going to keep blogging about what I am learning in F# with simple examples. The aim is to have a series of blog post on this new language (something like I have done for ADO.NET entity Framework on http://csentities.wordpress.com ) which will serve as a quick start guide and possibly will open up areas of discussion where I will be using F# with BI to add the power of functional programming into Business Intelligence.

I will be using VS 2010 (I have a Beta 2 Ultimate as of now but the code should work with VS 2008 and fsi.exe).

So Lets go ahead and start a new Blank Solution

Now lets go ahead and add a new F# console application.

Now Open the program.fs and paste the code given below in it.

open System
let a = 2
Console.WriteLine(a)
Console.Read()

Now if you hover over the Console.Read() you will see an error saying “The expression should have type ‘uint’, but has type ‘int’. Use ‘ignore’ to discard the result of the expression , or let to bind to a name.”

If you write Console.Read() in C# you will not get any diagnostic warning like this. This reminds you that you want to either pipe the result to ignore (e.g. if just calling a function for its side-effects) or else use the result.  There are a handful of minor diagnostic improvements like this (though there is still plenty of room for us to continue to improve).

If you want to get rid of this warning message then use the code below.

open System
let a = 2
Console.WriteLine(a)
ignore(Console.Read())

Run the application and your output will be look like this

Console.Read is just to hold the screen for us to see it and the screen exits when you hit any key after this.

So by now we know how to create a simple F# console application writes something to the console. Watch out for the coming posts.

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.

[Old post reposted as I was not very happy with the formatting]

Have a look at this query

   1:  WITH

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

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

   4:  MEMBER [Product].[Product].[Top10] AS

   5:  sum(

   6:  TOPCOUNT([Product].[Product].[Product].MEMBERS

   7:  , 10

   8:  , [Measures].[Internet Sales Amount]

   9:  )

  10:  ,

  11:  ([Measures].CurrentMember)

  12:  )

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

  14:  SELECT 

  15:  {

  16:  [Measures].[Internet Sales Amount],

  17:  [Measures].[REFUNDS],

  18:  [Measures].[PROFIT]

  19:  } ON COLUMNS,

  20:  {

  21:  [Product].[Product].[Top10]

  22:  ,[Product].[Product].[Top10PercOfTotal]

  23:  ,[Product].[Product].[All Products]

  24:  } 

  25:  ON ROWS

  26:  FROM [Adventure Works]

This yields same result as yours i.e. not a flat cell by cell division

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

   1:  WITH

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

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

   4:   

   5:  MEMBER [Product].[Product].[Top10] AS

   6:   sum(

   7:    TOPCOUNT([Product].[Product].[Product].MEMBERS

   8:    , 10

   9:    , [Measures].[Internet Sales Amount]

  10:    )

  11:    ,

  12:    ([Measures].CurrentMember)

  13:    )

  14:  ,SOLVE_ORDER = 3

  15:   

  16:  MEMBER [Product].[Product].[Top10PercOfTotal] AS 

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

  18:  , FORMAT_STRING = "Percent"

  19:   , SOLVE_ORDER = 4

  20:   

  21:   

  22:   

  23:  SELECT 

  24:   {

  25:    [Measures].[Internet Sales Amount],

  26:    [Measures].[REFUNDS],

  27:    [Measures].[PROFIT]

  28:    } ON COLUMNS,

  29:   {

  30:  [Product].[Product].[Top10]

  31:  ,[Product].[Product].[Top10PercOfTotal]

  32:  ,[Product].[Product].[All Products]

  33:   

  34:    } 

  35:    ON ROWS

  36:  FROM [Adventure Works]

  37:  

Here is the output

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. For more have a look at MDSN @ http://msdn.microsoft.com/en-us/library/ms145539.aspx

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.

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
(0x00000036000017400078,0x0000003600001EC1006C,'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)

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)

We are pleased to announce SQLBits goes West, the 5th instalment of SQLBits conferences. We are making it even bigger and better than last time, now spanning 3 days, and still keeping everything that has worked so well at previous events.

The event will be held at the Celtic Manor Resort in Newport South Wales, just off the M4 motorway.

This is the biggest event yet, with 3 days of top quality SQL Server content. It starts with the pre-conference training day on Thursday 19th, more details coming soon. We have had a lot of feedback about weekday versus weekend during previous events so this time we have added a paid conference day on the Friday 20th, with a SQL 2008 and R2 theme. Finally we have the free Saturday community day, with speakers from around the world covering all manner of SQL Server topics.

For more go to www.sqlbits.com