Monday, December 28, 2015

Analysis with SQL Server 2012

Hello Readers,

Today I would like to share some information regarding analysis with SQL Server 2012.

We all have huge data inside our application. Like an e-commerce website will have orders, customer viewing history, etc. For a normal admin of your website this is just data and not much usable for him. However when you go to a higher level management, you will need analysis.

Suppose a managing director of the e-commerce website, customer would like to compare his product's comparison current vs previous month or year to identify the trend.

As a developer, your job is to provide meaningful data to the customer. We can always write traditional queries and then tweak them to our needs. Now if there are functions which will give you basic data required for the analysis, it will give you lot more time to work on actual tasks instead fumbling on tweaking your queries.

Considering above challenges Microsoft has introduced a few Analytic functions into SQL Server 2012.

Below are the list of few such functions.

  • CUME_DIST
  • FIRST_VALUE
  • LAST_VALUE
  • LEAD
  • LAG
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • PERCENT_RANK
We will look into these functions in my future post.

Thanks for reading,
Nirav

Thursday, March 26, 2015

How To: Convert Flat List to Hierarchical data with LINQ

Hello Readers,

Its been a long time, I have posted something.

Today I bring to you a way to generate Hierarchical data from a flat list. This method can be useful when you want to retrieve whole data with a single Stored Procedure call and while binding you need to Bind it Hierarchy based on a parent child relationship.

I have created a Generic Method, which will accept Source and Key.

public static List<TSource> AsHierarchy<TSource, TKey>(this List<TSource> collection,
Func<TSource, TKey> parentSelector, Func<TSource, TKey> childSelector, Expression<Func<TSource, List<TSource>>> childrenPropertySelector, TKey selectedKey = default(TKey))
    where TKey : struct
{
    if (collection != null)
    {
        Func<TSource, bool> whereClause = x => childSelector(x).Equals(selectedKey);
        var items = collection.Where(whereClause).ToList();
        foreach (var item in items)
        {
            var childrenProperty = (childrenPropertySelector.Body as MemberExpression).Member as System.Reflection.PropertyInfo;
            childrenProperty.SetValue(item, collection.AsHierarchy<TSource, TKey>(parentSelector, childSelector, childrenPropertySelector, parentSelector(item)), null);
        }
        return items;
    }
    else
    {
        return collection;
    }
}

This method will be available as Extension Method to you List Collection. As parent selector, you need to identify what will be the key to select as parent. For childSelector you need to specify how to identify the child records. For Child Property Selector, you need to specify the property in which your child records will be saved.

For Example,

public class Employee
{
    public int Id {get;set;}
    public string Name {get;set;}
    public int ManagerID {get;set;}
    public List<Employee> SubOrdinates{get;set;}
}

Now to use the method I will write as below.

// Write your logic to get data
List<Employee> employees = GetEmployees();

//Once you have the data
List<Employee> hierarchy = employees.AsHierarchy(parent=>parent.Id, child=>child.ManagerID, parent=>parent.SubOrdinates);

 

Bingo!! your Hierarchical data is ready.

Try out this method and let me know if you face any issue. Post your feedback in the comments.