Ashwani Roy's BI Blog

It was lunch time was I was thinking of stepping out for a good lunch at Nando’s when a friend of mine sent me an email and he was taking about some issue he has come up with where in he has to order something . I said use generics . Well then I came across this puzzle on Eric’s blog (he is a Senior Dev in C# compiler team). The problem is mentioned here http://blogs.msdn.com/ericlippert/archive/2009/04/15/comma-quibbling.aspx

I decided to solve it and some how used generics , LINQ , Lambda expression and I thought my this blog can serve as a small pointer on how to use it in day to day programming. (For details on these please go to MSDN or somewhere else. This entry is just a demo of what these stuff look like and how can it be used in very simple terms)

So now the PROBLEM

Given a sequence of strings, concatenate them in a way similar to natural language.  For example the sequence "ABC", "DEF", "G", and "H" should be output as: {ABC, DEF, G and H}  "ABC" and "DEF" should be output as...   {ABC and DEF} and finally an empty sequence should yield the following output: {} . Now I am not going to handle the last bit that is the empty string part so just leave it.

So Here is the code (it is a console app and should work from VS 2008 onwards)

So some explanation now. I created a class(type) mySortedList which has 2 properties str and pos and I am using this class as the generic type. Generic list<T> needs a type so I can use my class. Handy here as I can store a list of my class type and enumerate over the collection and so on.

Now I have written a small LINQ query as well to do the ordering. It does the job. but a even better way will be to use the lambda expression to order this List<mySorterList>().

IMPROVEMET 1 : I will use a delegate to the ordering and not created another list but rather order the same list

So Here is the code . Just Comment the LINQ query and replace it with the delegate code till the end

Nice. But as I can use delegate here why not use LAMBDA Expression instead.

Just a small syntax explanation . Lambda expression are a functional programming construct and has been added to C# 3.0. The way it is written is

Sqr X => X*X meaning a function Sqr is a function of X which “goes to” X*X

ok so lets replace the above ordering by using lambda expression. The code becomes

I can replace the delegate by this

There are many other ways . I can use anonymous method to this sort return etc etc. So IN sort C#3.0 ‘s functional programming features like generics , LINQ , Lambda expressions , anonymous method are amazing . So do pay some attention to them to clean up your code written in frameworks 1.0 and 1.1 (2.0 has generics and anonymous method).

happy coding. and ya the output is (many other have used other way but I used ASCII encoding to have some more fun)

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 was doing some work around AMO and SSAS and I need to load a partition from database and read some of its properties . I was able to do most of them but the <QueryDefinition> was a bit of issue. I thought I will write a small blog on it. It might be useful for someone.

But if this Partition uses a dsvTable binding than this wont work. Anyway this is a small console application that does that plus it also serves a intro to how to use AMO to connect to Analysis service Server and use the Object model to program against the SSAS Objects.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.AnalysisServices;
using System.IO;

namespace TestBed
{
    class Program
    {
        static void Main(string[] args)
        {
            Server srv = new Server();
            srv.Connect("Data source=IMGLT119;Initial catalog=Adventure Works DW");
            Database db = srv.Databases.FindByName("Adventure Works DW");
            Console.WriteLine("Connected to Database {0} on server {1}", db.Name, srv.Name);
            foreach (Cube cb in db.Cubes)
            {
                if (cb.Name == "Adventure Works")
                {
                    foreach (MeasureGroup mg in cb.MeasureGroups)
                    {
                        foreach (Partition pt in mg.Partitions)
                        {
                            
                                //You can access the partition properties . Well Most of it.
                                Console.Write(" partition name is {0} partition slice before is {1}", pt.Name, pt.Slice);
                                QueryBinding qb = new QueryBinding();
                                //I had a bit of glitch with finding the Query Definition . If you have table binding than this wont work . 
                                qb = (QueryBinding)pt.Source;
                                Console.WriteLine("Query Definition {0}", qb.QueryDefinition.ToString()  );
                                break;
                            }

                        }
                    }

                    break;
            }
            Console.Read();
        }
    }
}
   

I could not resist to put this on my blog. A bunch of amazing videos on channel 9 and definitely recommended.

http://channel9.msdn.com/posts/MSCOMSWE/All-you-need-to-know-about-Microsoft-Business-Intelligence-14/

http://channel9.msdn.com/posts/MSCOMSWE/All-you-need-to-know-about-Microsoft-Business-Intelligence-24/

http://channel9.msdn.com/posts/MSCOMSWE/All-you-need-to-know-about-Microsoft-Business-Intelligence-34/

http://channel9.msdn.com/posts/MSCOMSWE/All-you-need-to-know-about-Microsoft-Business-Intelligence-44/

A few days back I had one of my SQL Dev friend asked me a question. “I fired a DML on one of my tables and a strange thing (to me) is that it generated ~180GB of transaction logs!!! . I did not expect it to grow so much so what happened.”

Well I though I would blog about what I told him , which might be useful to some.

Transaction Log explosion is a common occurrence when there is such a mammoth size DML operation issued against a large table of this size and the DML are not atomic transactions.

T-Log growth is a factor or size or rows, size of indexes, atomicity of transactions, if there is any trigger on the table, database recovery model and a few more.

There is one more thing. If the T-Log is set to expand in % it is not good. Here is an example.

Let’s say our initial size is 100 MB with 10%. The growth will be a non-linear pattern causing a sudden expansion like

If we plot it will soon become a exponential series

How to avoid this explosion to happen

You need to delete data in sized batches that will commit upon completion, thus keeping the log file to a reasonable size.

1. One easy way to achieve this is using TOP with DELETE statement  -- I would do this

2. Or SET ROWCOUNT

DECLARE @i INT

SET @i = 1

SET ROWCOUNT 10000

WHILE @i <> 0

BEGIN

    DELETE FROM HumanResources.Employee    WHERE Employee.HireDate < '01-01-1900'

    SELECT @i = @@ROWCOUNT

END

SET ROWCOUNT 0

3. Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.

4. Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.

More here http://support.microsoft.com/kb/873235

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/