www.BI Bits.co      

      A blog for discussing Business Intelligence and issues related to the Microsoft BI stack.


Convert DATETIME From UTC to Local Time

Recently I had to convert date time data that was recorded as a UTC datetime and display it as local time. My solution is somewhat similar to Aaron Bertran's solution here, but I thought I would provide a slightly different solution anyway. I start out by creating a calendar of dates between January 1 and the year of the @StartDate and December 31 and the year of the @EndDate plus 1. From the calendar table I then create another table with the UTC begin and end datetimes for the DST period and the UTC begin and end datetimes for the standard time period. In each row I also include the local datetime for the begin and end periods. I end up with 2 rows for every year (except for the last year, which has only 1 row.) Once I have the DST table created I use logic in my code where, for example, I take the UTCCreatedDateTime and convert that to Local_DateTime by finding the row where UTCCreatedDateTime >= dst.UTCPeriodStart and UTCCreatedDateTime < dst.UTCNextPeriodStart. For each row found, I use the following logic to determine the local time. SELECT DATEADD(hh, dst.OffsetUTCToLocalCentralTime, c.UTCCreatedDateTime) AS LocalDateTime. I live in the Central time zone so the offset from UTC is 6 hours during Central Standard Time and 5 hours during Daylight Saving Time. Here is how I created the DST table. DECLARE @StartDate smalldatetime = '20100101'        , @EndDate smalldatetime = CAST(YEAR(GETDATE() + 1) AS char(4))... [More]

Should I Use a Float Data Type?

My approach is to only use a float data type when I encounter calculations (and/or numerical values) where the decimal data type cannot handle the task. The decimal data type can handle calculations that use 38 digits or less. So in most instances, I use the decimal data type.I recently entered into a discussion about using the float data type in SQL Server. Most third-party products that are purchased use the float data type for numeric columns. My co-worker was convinced that floats are a good option because they perform calculations faster and they use less space than other data types that are used to store numerical values. I agreed on those points, but I mentioned that in a typical commercial organization, the float data type can cause many more unexpected results than a decimal data type. The reason is that in several instances it only stores an approximation of the actual value. My friend pointed out that Microsoft Dynamics AX uses float as the data type for amounts and quantities. Again, I agreed. But I pointed out that the main issue with floats is that it uses a binary representation of the number. And in SQL Server there are many instances where a float data type will not provide the correct result. As an example, when the value in question is 1.5 or 1.25 or 1/3 or pi, float stores the exact value or a much better approximation than decimal. But there are other values that appear to be very simple, yet when you store the value as a float, float cannot return an exa... [More]

Saving a Power View Report

Power View is built into Excel 2013, which means users can create Power View reports without having to start in SharePoint. (Power Pivot is also a built-in component in Excel 2013.) A Power View report can be sourced from an Excel data range or table, an embedded Power Pivot model, or a tabular instance of SSAS. Power View reports will be saved as either an .xlsx or .rdlx file (before Office 2013 and SharePoint 2013, the only option available was to save as an .rdlx file.) If created in SharePoint using the Power View tools, it will be an .rdlx file; if created using Excel, it will be an .xlsx file.When an Excel 2013 workbook with Power View sheets is created, it can be saved in Excel 2013 or in SharePoint 2013. After saving, it can also be uploaded to a SharePoint folder or a Power BI site. A workbook reader can view and interact with Power View sheets in that workbook in any of the above locations.To publish the Power View Report to SharePoint.In Excel, on the File tab, click Save As.In the Save As window, under Places, click SharePoint, and then click Browse.In the Save As dialog, click Browser View Options.In Browser View Options, click Sheets in drop down.In list of sheets, click the sheet that contains the Power View report, and then click OK.In Save As dialog, navigate to the Reports Gallery, if not selected by default.Click Save to upload the workbook from Excel to the SharePoint report gallery.If a Power Pivot file with a Power View report is stored in a computer whi... [More]

Converting A String Datatype Into DateTime in SSIS

Converting a string data type into a Datetime data type in SSIS can be done using SQL such as CONVERT(DATETIME, [stringcolumn], 112). But if the source is a flat file or an Excel file, the conversion is usually made using a Derived Column Transformation.An 8-digit string usually does not present any problem. In SQL Server the most common recommendation is not to use the YYYY-MM-DD format for date literals; always use YYYYMMDD. For example:SELECT CAST([NumericStringColumn] AS datetime) where [NumericStringColumn] might have a value such as ‘20131231’In SQL Server this will never fail, regardless of locale, dateformat settings, language settings, regional settings, etc. However, date and time format is relevant when the date and time value is a string. The dateformat and language of the database or server decides how to parse the string value to internally represent and store the datetime value. You can identify the language and dateformat configured in your SQL Server by executing the following command. DBCC USEROPTIONS WITH NO_INFOMSGS (You may want to read read Tibor Karazsi's article The ultimate guide to the datetime datatypes which discusses the handling of Dates in SQL Server.) Just keep in mind that SSIS handles Dates a little differently. In SSIS Dates represented as a numeric string value are handled differently. SSIS will not convert a numeric string to a Datetime data type; But, if you have an 8-digit string, just add a dash or slash in the appropriate pla... [More]

Book - Learning SQL Server Reporting Services 2012

I received a copy of Learning SQL Server Reporting Services 2012 written by Jayaram Krishnaswamy. This is a pretty good book for getting up and running with SSRS 2012. It is well-organized and addresses just about everything an SSRS developer needs to know. It takes the reader from the basics, from installation, up through some simple reports, and on to some advanced features. SSRS reports can be built using SQL Server Data Tools (the replacement of BIDS which is used in earlier versions), and Report Builder. It addresses some of the programming tools that can be used with SSRS, such as Power Shell, and also includes samples on using the Report Viewer control with .NET applications. It covers SharePoint installation and how to integrate SSRS reports and SharePoint. It addresses the new features in SQL Server 2012 that can be used with SSRS such as Power View and Data Alert. It even gets into Windows Azure SQL Reporting.

Converting Oracle Varchar2 Data Type to SQL Server

Have you ever had to import data from Oracle and store it in a SQL Server database? Normally, I don't think too hard about what is required, but I ran into a situation where I had a table with a row size that would exceed the 8000-byte row size limit if converted to SQL Server. I was using SSIS (SQL Server Integration Services.) The big issue was that I had were the columns defined as varchar2 in Oracle and my initial plan was to convert them as nvarchar in SQL Server. But for certain operations, the SQL Server buffer size limit was exceeded. By defining those columns as nvarchar, I would exceed the SQL Server buffer size limit. An easy solution is to define the columns as varchar in SQL Server and right away I am under the buffer size limit. But now my question becomes, "Did I mess up the data? Will there be any character loss? What am I affecting by moving the data from an Oracle varchar2 column into a SQL Server varchar column?" To know whether I messed up the data or not all depends on the character set used in Oracle and the collation used in SQL Server. In Oracle, the character set is chosen when the database is created. In Oracle, varchar2 (10) means it can store 10 bytes. The maximum length is 4000 bytes. In a single-byte character set a varchar2 (10) column can store up to 10 characters; the number of bytes and the number of characters are basically the same. In a multi-byte character set things are different. If the database is setup to use the Unicode character d... [More]

Installing SQL Server 2012

There are several blogs that describe the steps necessary to install SQL Server 2012. My two favorites are Step by Step: Installing SQL Server 2012 RC 0 written by blakhani and How to install SQL Server 2012 written by Alberto Morillo. But installing SQL Server 2012 can be a little confusing. For example, when installing SSAS (SQL Server Analysis Services), when you get to the Analysis Services Configuration step, you need to choos either Multidimensional and Data Mining Mode or Tabular Mode. You can only pick one. What you should know is that if you want them both installed, pick one, then after its successful installation, install SQL Server 2012 again, but the second time, when you get to the Instance Configuration step, you need to select Named Instance and install it as a separate instance. Then, during the second installation, when you get to the Analysis Services Configuration step, select the other option. The other issue that can sometimes be confusing is the installation of SSDT (SQL Server Data Tools.) SSDT is the replacement for BIDS (Business Intelligence Development Studio.) It is a subset of Visual Studio 2010 and it has a set of Business Intelligence tools. But, depending on how you install SQL Server 2012, you may or may not install SSDT at the same time that the other components of SQL Server 2012 are installed. If Visual Studio 2010 is not installed at the time you install SQL Server 2012 and, if you choose "SQL Server Data Tools" on the Feature Selection... [More]

SSIS - SQL Server 2012 Project Deployment

With SQL Server 2012 there are several ways to deploy SSIS packages. The legacy methods are still available – SQL Server Deployment, File System Deployment, and Package Store Deployment. But, with SQL Server 2012, we are encouraged to use the new method – Project Deployment. When you use Project Deployment you will notice several nice features. With project deployment we do not use configuration files and we do not have to worry about the package protection level. And the project connection managers are a big plus. On top of that, deployment is straightforward. But, before we can deploy anything, we need to create an Integration Services catalog (see Creating the Integration Services Catalog-(SSISDB)). Here are the steps for Project Deployment of an SSIS project in SQL Server 2012 the first time. In SQL Server Data Tools (SSDT), click on Project in the menu line. In the drop down box, click Deploy. The Overview page for the Deployment Wizard will be displayed. Click Next and the Select Destination window will be displayed. Enter the server name. In the Path area, click Browse and select the folder in which we wish to store the deployed project. If, by clicking the Browse button, we do not see any Catalog information, that may be because the catalog has not been created (see Creating the Integration Services Catalog-(SSISDB).) If the Catalog has been created, we should see a window such as the following pop up. Select the desired location and click OK. ... [More]

Creating the Integration Services Catalog (SSISDB)

SQL Server 2012 Integration Services has a new Integration Services Catalog (SSISDB) that is used for monitoring and managing SSIS projects. The Catalog will store all the integration services objects in SQL Server and will automatically log all the package execution activities when the SSIS project is using the new Project Deployment Model. The new deployment model takes advantage of the new SSIS Database, which is a new centralized repository for managing deployment projects. With the new database and catalog, SSIS provides better manageability over the packages. It also includes better performance tracking, and performance monitoring. It incorporates better logging freatures as well. And with the Catalog and the Project Deployment Model, versioning is automatically incoporated. In order to use the new Project Deployment Model (click here to see how to deploy an SSIS project), you need to first create the Integration Services Catalog. In SQL Server Management Studio (SSMS) locate the Integration Services Catalogs node. Right-click on it. Click on Create Catalog. The Create Catalog window is displayed. If not already enabled, Enable CLR Integration. The catalog name is SSISDB. Enter the password for encryption then hit the OK button. The new SSISDB will be created and it will show in two places – a Databases node and an Integration Services Catalogs node. You can review and adjust the properties by right-clicking on the SSISDB folder and selecting Properti... [More]

Consuming Web Services in SSIS Script Component

I recently was given an assignment where the enterprise decided to access their legacy databases by using Windows Communication Foundation (WCF). So in populating the data warehouse I had to retrieve the legacy data using the web service. If WSHttpBinding or BasicHttpBinding had been selected, using the web service with SSIS would not have been a problem. I could have used a Web Service Task, or a Script Task, or a Script Component. As a matter of fact, here is a blog that provides more details on "How to Configure an SSIS Package to Access a Web Service using WCF" written by David Browne. But I had to use NetTcpBinding. With NetTcpBinding, I could not get the Web Service Task to work and I had a lot of problems in getting the SSIS Script Component to work. After struggling with this issue for a while (I kept getting errors indicating that there was a problem with the endpoint address), one of the .NET developers modified the code in my SSIS Script Component so that I could use the web service. Here is what I ended up with. I setup a Data Flow Task and added a Script Component with 7 outputs. In the Script Component I added a service reference to the WCF service (named DataWarehouse.svc). The .NET developer modified the PreExecute and PostExecute methods in the Script Component (Namespaces was also modified) so that it looks like the following. #region Namespacesusing System;using System.Configuration;using System.Data;using Microsoft.SqlServer.Dts.Pipeline.Wrapper;using... [More]