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.

Configuration Files for Class Library Projects

While working with Windows Forms and web form applications in .NET 1.1, we realize that we can easily load application settings from configuration files. .NET configuration architecture made it very easy to load these files and read them in the application at runtime. But there are times when we develop complex business components and those must have their own configuration related data. Since these library components are independent of applications in which they are loaded, it makes sense that these components must have their own ConfigurationManager. This article explains how we can develop Configuration Manager for a DLL...Read More

MultiSelect Dropdown Control

For an Ajax-based Multiselect dropdown, please visit this link

It’s an easy to use and lightweight control. The code is also fairly simple to understand. I have developed it using .NET and C# on the server side and JavaScript for client side scripting. Although it’s not a full blown web server control, it does provide with some useful features that help users in displaying and managing information on the page easily. Following is a small list of those features... Read More