RowFilter in presense of DataRelations

We know the role of DataRelation class in .NET − It helps create parent-child relationship between tables in a dataset. We also know that it is an an association which can be formed by declaring primary and foreign key columns while creating the relationship. I have seen many examples of using this class, specially the one in this article on MSDN:

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
           custDS.Tables["Customers"].Columns["CustomerID"],
           custDS.Tables["Orders"].Columns["CustomerID"]);
foreach (DataRow custRow in custDS.Tables["Customers"].Rows)
{
Console.WriteLine(custRow["CustomerID"]);
foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
  Console.WriteLine(orderRow["OrderID"]);
}

The above code is not doing anything but creating a relationship "CustOrders" and looping through parent and child rows to dump their contents to console. The GetChildRows() method works pretty well as long as no explicit filter is applied on any of the related tables in the dataset. Let's assume that the Customers table contains 3 customers and Orders table stores some orders of these customers. In the above mentioned situation, when no filter is applied, the output may look like:

    

Customer ID	Order ID
1	101
1	102
2	201
2	202
2	203
3	301
3	302

However, consider the situation where we might only be interested in list of orders that belong to CustomersID = 1, CreateChildView() is the method to go with. This method helps get rows from the child table that are associated with the filtered rows from the parent table.

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
           custDS.Tables["Customers"].Columns["CustomerID"],
           custDS.Tables["Orders"].Columns["CustomerID"]);

DataView dv = custDS.Tables["Customers"].DefaultView;
dv.RowFilter = "CustomerID = 1";

foreach (DataRowView drv in dv)
{
Console.WriteLine(drv["CustomerID"]);
for(DataRowView orderRow in drv.CreateChildView(custOrderRel))
  Console.WriteLine(orderRow["OrderID"]);
}

Customer ID	Order ID
1	101
1	102

 

Sort By Column Value

We do sorting by column names almost everyday while writing database queries. Below is one such simple query

SELECT customerid, employeeid, orderdate  FROM dbo.orders  ORDER BY customerid

But, today I faced a situation where I had to write a query that returns all the rows however some of the rows appeared on the top in the list if they match a specified criteria. Lets take the example of above query where we want to get a list of all the customers but customers having customerid 'VINET' should appear on top. I came up with the below query:

DECLARE @customerid AS VARCHAR(100)  SET @customerid='VINET'  SELECT customerid, employeeid, orderdate,  (CASE @customerid WHEN '' THEN  null WHEN customerid THEN customerid END) AS 'sortColumn'  FROM orders  ORDER BY sortColumn DESC, employeeid

In the above query, 'sortColumn'  is a temporary column that is being used just to sort the whole result set by its value. 

This might not be a perfect solution as I am not a SQL expert but, I am content with it for the time being as long as it servers the purpose. If you think there is a much better way of doing the same thing, then you are more than welcome to share it here.

SSAS Server Time Dimension

While working with SQL Server Analysis Services, I found server time dimension quite interesting because of the fact that the data for this dimension does not come from a dimension table in the data warehouse, but it is generated by Analysis Services and stored in a proprietary file structure on the server. We simply specify the beginning date and end date of the dimension, select the time periods to include such as year, quarter, month, or date, and choose the special calendars, if any, to add to the dimension. When we create a Server Time dimension, no such table is created but data available to this dimension type is maintained solely by Analysis Services. In order to use this dimension with a fact table, we will need to have a date/time column instead of a dimension key in the fact table.

It is important to understand that Analysis services will use only the date part of this column to join the fact table with Server Time dimension which means we need to remove the time part from the date/time column in our data warehouse table. This can easily be achieved by creating a Named Calculation in our data warehouse table. Using a Named Query or a Named Calculation gives us the ability to manipulate the data structures for use by Analysis Services even if we don’t have permissions to make similar changes at the database level.

Named Calculation can be created by following the below steps.

• In Solution Explorer, double-click SSAS Step by Step DW.dsv to open the Data SourceView Designer.
• Right click the table that will have the desired Named Calculation and select New Named Calculation.
• In the Column name write a name of your choice. Note that this Name Calculation will act as a column for the table and will be used instead of date/time column in the table.
• In the Expression column, we can to use SQL CONVERT function to get the date only part of the date/time column.
  
  
  CONVERT(varchar(11), CreatedDate, 20)
  
  
  
  
  
  
  N.B. in the above statement we are trying to convert a date/time column (CreatedDate) into a format that will only have date part. We must use 20 in the third parameter which represents yyy-mm-dd format. This is used by server time dimension which it generates dates to be stored in a file.  

That is it. We can now use Named Calculation (Calendar - in my case here), anywhere in our OLAP cube.