SQL Server Analysis Services (SSAS)

SQL Server Analysis Services  introduces many enhancements for tabular models. These include:

  • Tabular mode as the default installation option for Analysis Services. (CTP 2.0)
  • Object-level security to secure the metadata of tabular models. (CTP 2.0)
  • Date relationships to easily create relationships based on date fields. (CTP 2.0)
  • New Get Data (Power Query) data sources, and existing DirectQuery data sources support for M queries. (CTP 2.0)
  • DAX Editor for SSDT. (CTP 2.0)
  • Encoding hints, an advanced feature for optimizing data refresh of large in-memory tabular models. (CTP 1.3)
  • Support for the 1400 Compatibility level for tabular models. To create new or upgrade existing tabular model projects to the 1400 compatibility level,
  • A modern Get Data experience for tabular models at the 1400 compatibility level.
  • Hide Members property to hide blank members in ragged hierarchies. (CTP 1.1)
  • New Detail Rows end-user action to Show Details for aggregated information. SELECTCOLUMNS and DETAILROWS functions for creating Detail Rows expressions. (CTP 1.1)
  • DAX IN operator for specifying multiple values. (CTP 1.1)

SQL SERVER – Learning new multipurpose FORMAT function

In versions prior to 2012, you need to use the CONVERT function with different styles to convert the date values into a different format. Let us learn about the FORMAT function here.

Let us create this simple dataset

CREATE TABLE #date_test(dates DATETIME)

INSERT INTO #date_test(dates)

SELECT ‘2016-10-19 10:22:34’ AS DateTest

Executing above query will return no result, but it will create a temporary table with one row.

Suppose you want to format the dates in either dd/mm/yyyy or mm/dd/yyyy format. You can do it by

SELECT dates,CONVERT(VARCHAR(10),dates,101) AS [dates_mm/dd//yyyy]

FROM #date_test

SELECT dates,CONVERT(VARCHAR(10),dates,103) AS [dates_dd/mm/yyyy]

FROM #date_test

Executing above query will return following result:

If you want to show only month name, you can use

SELECT DATENAME(month,dates) AS [month_name] FROM #date_test

Executing above query will return following result:

If you want to show only day name, you can use

SELECT DATENAME(weekday,dates) AS [day_name] FROM #date_test

Executing above query will return following result:

As you can see, you need to use either CONVERT or different functions based on what you want to do. This requires the usage of many different functions.

But with version 2012 it is very easy. All you need to do is just make use of FORMAT function

SELECT dates,FORMAT(dates,’dd/MM/yyyy’) AS [dates_dd/mm/yyyy] FROM #date_test

 

SELECT dates,FORMAT(dates,’MM/dd/yyyy’) AS [dates_mm/dd/yyyy] FROM #date_test

Executing above query will return following result:

Just use change the second parameter accordingly

To display month name, or day name

SELECT dates,FORMAT(dates,’MMMM’) AS [month_name] FROM #date_test

 

SELECT dates,FORMAT(dates,’dddd’) AS [day_name] FROM #date_test

Executing above query will return following result:

You can also display long formatted dates with customized text included

SELECT dates,FORMAT(dates,'”The given date is ” MMMM d, yyyy’) AS long_format

FROM #date_test

Executing above query will return following result:

Now let us run following script

SELECT dates,FORMAT(dates,'”This is generated on” M/d/y “at” hh:mm:ss’) AS long_format FROM #date_test

Executing above query will return following result:

Not only you can do date time formatting, you can also do number formatting using the FORMAT function.

Suppose you want to always make 6 digits number and if the length is less than 6, you want to prefix leading zeroes to make the total length as 6, you can do it by

SELECT 935 AS [number], FORMAT(935,’000000′) AS length_6

Executing above query will return following result:

I believe this new function FORMAT is very handy function and after using this, you may not have to remember many different functions to the same task.

Multilevel Grid in MVC using Jquery

Steps To build Multilevel Grid in MVC

Step1:- Create Empty MVC application in visual studio

Step-2: – Create a MDF file inside App_Data folder.

Step-3:- Now create a table for retrieving the data here i took employees as table name.

CREATE TABLE [dbo].[Employees] (

   [EmployeeID]     INT           NOT NULL,

   [LastName]        NVARCHAR (50) NULL,

   [FirstName]       NVARCHAR (50) NULL,

   [Title]           NVARCHAR (50) NULL,

   [TitleOfCourtesy] NVARCHAR (50) NULL,

   [BirthDate]       DATETIME      NULL,

   [HomePhone]       NVARCHAR (50) NULL,

   [ReportsTo]       INT           NULL,

   CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([EmployeeID] ASC)

);

 After creating the table insert some data in that table like this

If you observe the above table, we have “ReportsTo” column this column contains EmployeeID values and based on this column we can assign employees to CEO, Project manager and Team Leader.

Step-4:- Create one ADO.Net Entity Model to Employees table. In solution you can see my entity model in below snippet.

 

Step-6:- Right click on controllers folder, create Controller.

public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
List<Employee> list = new List<Employee>();
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
list = dc.Employees.OrderBy(a => a.EmployeeID).ToList();
}
return View(list);

}
}

MyDatabaseEntities is class which inherited by DbContext. using “dc” we retrieve all employees details and assign to the list.

Employee.cs:-

public string HomePhone { get; set; }

public Nullable<int> ReportsTo { get; set; }

}

Step7:- Now Right click on Index method of HomeController and add view.

 

Index.cshtml:-

 

@model List<MultilevelGrid.Employee>

 

<h2>Display Multilevel Grid</h2>

 

@* Jquery for Collapse/Expend *@

<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js"></script>

<script type="text/javascript">

$(document).ready(function () {

$('.icon').on('click', function () {

var $pRow = $(this).parents('tr');

var $nextRow = $pRow.next('tr');

$nextRow.toggle();

$(this).toggleClass('icon-s icon-e');

});

})

</script>

 

@* CSS for dispalay the grid *@

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />

<style>

.container {

min-width: 500px;

overflow: hidden;

}

.innerTable table {

margin: 0px auto;

border-left-width: 4px;

}

td.innerTable {

padding-left: 20px !important;

}

.icon {

background-repeat: no-repeat;

width: 16px;

height: 16px;

display: inline-block;

float: left;

color: navy;

}

.icon-e {

background-position: -32px -16px;

}

.icon-s {

background-position: -64px -16px;

}

</style>

 

@*Call NetedData.cshtml Page for displaying the grid*@

<div class="container">

@NestedData.GetHtml(Model, 0)

</div>

 

 

       

In above view we add Employee.cs file as model at top of Index.cshtml. End of the coding we call the NestedData.cshtml page.

 

Step8:- Create NestedData.cshtml page for displaying the multilevel grid.

 

@helper GetHtml(List<MultilevelGrid.Employee> employeeList, int parentID)

{

 <table class=”table table-bordered”>

   @{

     int currentID = 0;

     int rowIndex = 0;

     foreach (var i in employeeList.Where(a => a.ReportsTo.Equals(parentID)))

      {

        if (i.EmployeeID == currentID)

         {

           continue;

         }

        else

         {

          if (rowIndex == 0)

            {

              <thead>

                <tr>

                   <th>Employee ID</th>

                   <th>Employee Name</th>

                   <th>Designation</th>

                   <th>Contact Number</th>

                </tr>

               </thead>

             }

        rowIndex++;

        currentID = i.EmployeeID;

         var Sub = employeeList.Where(a =>                                                                                                                                                                                                    a.ReportsTo.Equals(i.EmployeeID)).ToList();

        var newEmployeeList = employeeList.

 Where(a => !a.EmployeeID.Equals(i.EmployeeID)).ToList();

        <tbody>

          <tr>

            <td>

               @if (Sub.Count > 0)

                {

                 <a class=”icon icon-e” style=”float:left; cursor:pointer;”>>></a>

                }

               else

                {

                 <span style=”display:inline-block;width:14px”>&nbsp;</span>

                }

                 @i.EmployeeID

                 </td>

                 <td>@i.TitleOfCourtesy @i.FirstName @i.LastName</td>

                 <td>@i.Title</td>

                 <td>@i.HomePhone</td>

            </tr>

            <tr style=”display:none;”>

                 @if (Sub.Count > 0)

                  {

                    <td colspan=”4″ class=”innerTable”>

                      @NestedData.GetHtml(newEmployeeList, i.EmployeeID)

                    </td>

                   }

                  else

                   {

                     <td colspan=”4″></td>

                   }

              </tr>

         </tbody>

        }

      }

    }

</table>

}

 

In above code we retrieve the two categories of employees based “ReportsTo” column.i.e:

 

1)var Sub = employeeList.Where

(a => a.ReportsTo.Equals(i.EmployeeID)).ToList();

2) var newEmployeeList = employeeList.Where

(a =>!a.EmployeeID.Equals(i.EmployeeID)).ToList();

 

Step9:- Now run the application, by default the application load like this.

If you click on “>>” button, the next row will be expanded like this.

 

The above output indicates, the Project manager and Team Leader are working under CEO. Again if you click on second EmployeeID it will be expanded.

 

 

It’s indicates the Jr.Developer works under Project Manager.

 

This all explains how multilevel grid worked on employees table. Thanks!

Passing XML as a parameter in stored procedure and inserting using ADO.NET code with Example In MVC

XML_Stored_Proc

if we have number of parameters. We can append all parameters to xml parameter and passing as a single parameter in xml. This if helpful for more performance to a stored procedure.

Follow the Below steps 

Step-1: Create Table

Table

Step-2:  Create Procedure like below

CREATE PROCEDURE  [dbo].[USP_XMLInsert]
(
@xmlx xml
)
AS
BEGIN
SET NOCOUNT ON;
      INSERT INTO Tbl_Customer
      SELECT
      Customer.value(‘(Name/text())[1]’,’NVARCHAR(50)‘) as Name , –TAG
      Customer.value(‘(City/text())[1]’,’NVARCHAR(50)‘) as City, –TAG
      Customer.value(‘(IndustryType/text())[1]’,’NVARCHAR(50)‘) as IndustryType –TAG
      FROM
      @xmlx.nodes(‘/Customer‘)AS TEMPTABLE(Customer)

END

GO

Step 3: Create sample MVC application and create model class in models folder and create properties like below;

namespace Insert_XMLPROC.Models
{
    public class Customer
    {
        public string Name { get; set; }
        public string City { get; set; }
        public string IndustryType { get; set; }
    }
}

Step 4: Create controller in controller folder and write insert logic

    public class CustomerController : Controller
    {
        // GET: Customer
        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Index(Customer objcustomer)
        {
            StringBuilder objstring = new StringBuilder();
            objstring.Append(“<Customer>“);
            objstring.Append(“<Name>“);
            objstring.Append(objcustomer.Name);
            objstring.Append(“</Name>“);
            objstring.Append(“<City>“);
            objstring.Append(objcustomer.City);
            objstring.Append(“</City>“);
            objstring.Append(“<IndustryType>“);
            objstring.Append(objcustomer.IndustryType);
            objstring.Append(“</IndustryType>“);
            objstring.Append(“</Customer>“);

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(objstring.ToString());

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“MyConnection“].ToString());
            con.Open();
            SqlCommand cmd = new SqlCommand(“USP_XMLInsert“, con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue(“@xmlx“, xmlDoc.InnerXml);
            cmd.ExecuteNonQuery();
            return View();
        }
    }


Step 5: Create index view and write insert logic

@model Insert_XMLPROC.Models.Customer

@{
    ViewBag.Title = “Index“;
}

<h2>Index</h2>

<script src=“~/Scripts/jquery-1.10.2.js”></script>
<script src=”~/Scripts/jquery-1.10.2.min.js“></script>
<div>
    <table>
        <tr>
            <td>Name</td>
            <td>
                <input type=”text” id=”txtName” />
            </td>
        </tr>
        <tr>
            <td>City</td>
            <td>
                <input type=”text” id=”txtCity” />
            </td>
        </tr>
        <tr>
            <td>IndustryType</td>
            <td>
                <input type=”text” id=”txtIndustryType” />
            </td>
        </tr>
        <tr>
            <td>
                <input type=”button” id=”btnsubmit” value=”Save” />
            </td>
        </tr>
    </table>

    <script type=“text/javascript”>
        $(document).ready(function () {
            $(‘#btnsubmit‘).click(function () {
                $.ajax(
                {
                    type: “POST“,
                    url: ‘/Customer/Index‘,
                    data: {
                        Name: $(‘#txtName‘).val(),
                        City: $(‘#txtCity‘).val(),
                        IndustryType: $(‘#txtIndustryType‘).val(),
                    }

                });

            });
        });
    </script>
</div>