Friday, July 29, 2016

Cannot connect to WMI - SQL Server Configuration Manager


SQL Server Configuration Manager (an msc plug-in) will not start, with the following message:


 "Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]"

According to MSFT support (support - kb956013), it boils down to a problem with the WMI configuration - makes sense. To fix the problem, in a command prompt, you need to run this command:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"

Where \number\ is a function of the latest version install on the machine. As follows:


Microsoft SQL Server 2016 130
Microsoft SQL Server 2014 120
Microsoft SQL Server 2012 110
Microsoft SQL Server 2012 110
Microsoft SQL Server 2008 R2 100
Microsoft SQL Server 2008 100
Microsoft SQL Server 2005 90


Here are the results after running:




Power BI 2.0 celebrates one year!

It's been a year since we posted our first look write-up on the newly released Power BI 2.0 [First Look - Power BI 2.0], and since that time Power BI keeps getting better. According to the Power BI team, they have made a total of 535 updates to Power BI last year [Happy First Birthday to Power BI!].

If you have not used Power BI, or even taken a look - you should.  For self-service, dashboards, or more advanced BI, analytics or just basic reports, it is probably the best tool available today. You can find out more here:  https://powerbi.microsoft.com/

Tuesday, July 26, 2016

EXASOL from SQL Server: Data types & Conversion

We are working with EXASOL and that requires loading data using SSIS (SQL Server Integration Services). For this test, we are using the current version of SQL Server Data Tools 2016 along with Visual Studio 2015. EXASOL only has a limited set of data types, so creating destination tables requires a bit of planning.  And with the tables created in EXASOL, importing data from SQL Server requires some transformations.

EXASOL Data Types

From the EXAsolution user manual, section 2.3.1, Table 2.2 Overview of EXASolution data types:

Data Type
Notes
BOOLEAN

CHAR(n)
1 ≤ n ≤ 2,000;   ascii and utf8
DATE

DECIMAL(p,s)
 s ≤ p ≤ 36
 p ≥ 1; s ≥ 0
DOUBLE PRECISION

GEOMETRY[(srid)]
 srid defines the coordinate system (see also EXA_SPATIAL_REF_SYS)
INTERVAL DAY [(p)] TO SECOND [(fp)]
 1 ≤ p ≤ 9, 0 ≤ fp ≤ 9, accuracy precise to a millisecond
INTERVAL YEAR [(p)] TO MONTH  
1 ≤ p ≤ 9
TIMESTAMP
Timestamp with accuracy precise to a millisecond
TIMESTAMP WITH LOCAL TIME ZONE
Timestamp which considers the session time zone
VARCHAR(n)
1 ≤ n ≤ 2,000,000;   ascii and utf8

It's a short list, but DECIMAL, CHAR and VARCHAR end up handling the missing data types we need. At least for now. And EXASOL is an analytic engine - not storage for blobs or pictures. The following table outlines a few of the SQL Server data types, and the corresponding EXASOL data types.  Plus, a note about whether EXASOL will handle the implicit conversion, and what T-SQL or SSIS Data Conversions we needed, or used to load the data.

All and all, the overall process was fairly logical, except for dates. As we noted, we are using SSIS, and the EXASOL ADO.NET driver with the SSIS ADO.NET Destination task.

 Data Types:  SQL Server and EXASOL:  Equivalents and Conversion Steps



SQL Server
EXASOL
Implicit (1)
T-SQL
( view or SQL Command)
SSIS
Data Conversion Task
bit
BOOLEAN
yes
Not reviewed
DT_BOOL
int
DECIMAL(18,0)
yes
cast( mycol as Decimal(18,0))
[DT_NUMERIC] 
Precision 18; Scale 0, or DT_DECIMAL, with scale of 0

smallint
DECIMAL(9,0)
yes
cast( mycol as Decimal(9,0))
[DT_NUMERIC] 
Precision 9; Scale 0, or DT_DECIMAL, with scale of 0

tinyint
DECIMAL (3,0)
no (2)
cast( mycol as Decimal(3,0))
[DT_NUMERIC]
 Precision 3; Scale 0  (1), or DT_DECIMAL, with scale of 0

float
DOUBLE PRECISION
yes
Not required
Not required





money
DECIMAL(18,4)
yes
cast( mycol as Decimal(18,4))
[DT_NUMERIC] 
Precision 18; Scale 4 , or DT_DECIMAL, with scale of 4
Decimal(x,y)
DECIMAL(x,y)
n/a
None required
None required
Nvarchar(x)
VARCHAR(x) utf8
n/a
n/a
n/a
Varchar(x)
VARCHAR(x) ascii
n/a
n/a
n/a
datetime
DATE
no (3)
convert(varchar, mycol, 101)
(6)
datetime
TIMESTAMP
no (4)
convert(varchar, mycol, 101)
[Time defaults to 00:00:00.000]
(6)
datetime
TIMESTAMP
no (4)
convert(varchar, mycolumn, 101) +  ' ' +   STUFF (convert(varchar, mycolumn, 114), 9,1,'.')

(6)
Varbinary(max)
No support
no




Notes

(1)  No Cast or Convert required, but the optional T-SQL or SSIS Data Conversions will minimize conversion risk.

(2) tinyint conversion SSIS error message:  [ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: No conversion for parameters with this type System.Byte

(3) datetime implicit conversion SSIS error message:
[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: data exception - invalid character value for cast; Value: '01.06.1996 00:00:00' Format: 'MM/DD/YYYY' in write of column FACTTESTSALES.ORDERDATE (Session: 1540926262556380170)

(4) datetime implicit conversion SSIS error message:
[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: data exception - invalid character value for cast; Value: '01.06.1996 00:00:00' Format: 'MM/DD/YYYY HH24:MI:SS.FF6' in write of column FACTTESTSALES.ORDERDATETS (Session: 1540926166351754250)

(5) VARCHAR(x) ascii equates to a conventional varchar().  VARCHAR(x) utf8 equates to nvarchar(x).

(6) Consider using a Derived task.  But, doing the conversion in the database engine using T-SQL will be far more efficient. You can do this in the OLE DB Source task by using a SQL Command, rather than using a "Table or View" data access mode. If you do use the "SQL Command" option, and you later modify your SELECT such that is changes a data type [ for example: convert(varchar, mycol, 101) ], expect problems and see our note below: SQL Command Data Type Mismatch - Error.


Dates

Importing date data into EXASOL using SSIS is a bit more challenging.  And so far, we have found only a few approaches that work, and many that do not. If we look over the error messages returned back from SSIS from some failed import attempts, we see that the EXASOL ADO.NET driver is looking for this format:  



                'MM/DD/YYYY HH24:MI:SS.FF6'


To replicate the MM/DD/YYYY, in SQL Server use T-SQL:

  convert(varchar, mycolumn, 101) 

This does not return any time value, but will be accepted both by the DATE and the TIMESTAMP data types in EXASOL. To add the time value, it requires this T-SQL: 


  convert(varchar, mycolumn, 101) + ' ' +               STUFF(convert(varchar, mycolumn, 114), 9,1,'.')


STUFF?

Look closely at the time section of the required format.  "...MI:SS.FF6'"  Notice the period, and not the colon between SS and FF6.  The T-SQL CONVERT( , ,114) statement returns a colon between SS:mmmm, while the desired value requires a period. Very unfortunate. Also notice that one of the T-SQL CONVERT() ODBC formats (121) and the ISO formats (126, 127) have the desired millisecond format. Unfortunately, the date format may not work. Possible reasons to consider an ODBC connection rather than an ADO.NET connection - at least for processes with a small number of records.


SQL Command Data Type Mismatch - Error

You are using the SQL Command option in the OLE DB source task,  And the error message still tells us our date is formatted incorrectly.  Check the Metadata in the Data Flow Path Editor (see below).

When you use the SQL Command option, and you CHANGE the date type in your SELECT, the source task does not change the outgoing data type (bug).  Say, when you need to add a CONVERT statement to a datetime value - as we outlined above. Let's call it an SSIS bug. (note, we are using SQL Server Data Tools from SQL 2016 - earlier versions may not have this problem).

Fix: delete the OLE DB Source Task, create a new task, add back your updated SELECT, with the corrected data types, and connect it to your work flow. And verify that the outgoing data types are what you need.





Implicit (or direct) Date conversions that do not work, at least so far:

Naturally, you will be using one of the above CONVERT statements to convert your SQL Server date data into EXASOL. Or perhaps you have worked out one of your own. 

The following just lists our the various direct or implicit conversions that we have tried and do not seem to work. For reference, here are the date conversions that we have found that do not work, at least with SSDT/SSIS 2016 and EXASOL 5.0.15.


  
SQL Server
EXASOL
SSIS Conversion/Import
Datetime
DATE
No
Datetime
TIMESTAMP
No
Date
DATE
No
Date
TIMESTAMP
No
Datetime2
DATE
No
Datetime2
TIMESTAMP
No
SmallDatetime
DATE
No
SmallDatetime
TIMESTAMP
No
Datetimeoffset
DATE
No
Datetimeoffset
TIMESTAMP
No

And, these CONVERT statements did not work either.

ISO:         convert(varchar, OrderDate, 112)
ANSI:        convert(varchar, OrderDate, 102)
Others:      convert(varchar, OrderDate, 104)
             convert(varchar, OrderDate, 100)
             convert(varchar, OrderDate, 113)


Next Steps: EXASOL - Using SSIS to load our Data Warehouse





Monday, July 25, 2016

Date Dimension - DDL/DML to create and maintain

For any data warehouse or business intelligence work, you are going to need a stock process to create and then periodically maintain a date dimension table. The emphasis here is on maintenance.

Periodically, you'll need to add to the date dimension.  Over the years, I've worked out a fairly simple, but sound date dimension create and update process.   Below are the details:

DateKey


Dates never change, so the datekey can have intelligence. It is one of the few dimensions that can break the rule where a dimension key should never have intelligence. The datakey uses the ISO format:  YYYYMMDD. This allows you to assign a datekey to business data using only the date, without having to reference the date dimension.

The scripts


create schema rds;
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[Date_dim]') AND type in (N'U'))
DROP TABLE [rds].[Date_dim]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [rds].[Date_dim](
 [DateKey] [int] NOT NULL,
 [FullDateAltKey] [date] NULL,
 [DayOfWeekNum] [tinyint] NULL,
 [DayOfWeekName] [nvarchar](10) NULL,
 [DayOfMonthNum] [tinyint] NULL,
 [DayOfYearNum] [smallint] NULL,
 [WeekOfYearNum] [tinyint] NULL,
 [MonthOfYearName] [nvarchar](10) NULL,
 [MonthOfYearNum] [tinyint] NULL,
 [CalendarQuarter] [tinyint] NULL,
 [CalendarYear] [smallint] NULL,
 [CalendarSemester] [tinyint] NULL,
 [FiscalPeriod] [tinyint] NULL,
 [FiscalQuarter] [tinyint] NULL,
 [FiscalYear] [smallint] NULL,
 [FiscalSemester] [tinyint] NULL,
 [FiscalDayOfPeriodNum] [tinyint] NULL,
 [FiscalDayOfYearNum] [smallint] NULL,
 [FiscalWeekOfYearNum] [tinyint] NULL,
 [FiscalPeriodOfYearName] [nvarchar](10) NULL,
 [SimpleCalendarDate] [varchar](20) NULL,
 [SimpleFiscalDate] [varchar](20) NULL,
 [SimpleCalendarMonth] [varchar](20) NULL,
 [SimpleCalendarQtr] [char](5) NULL,
 [SimpleFiscalQtr] [char](5) NULL,
 [SimpleFiscalYear] [char](7) NULL,
 [SimpleCalendarYear] [char](7) NULL,
 [CalendarQuarterDesc] [char](10) NULL,
 [FiscalQuarterDesc] [char](10) NULL,
 [CalendarSemesterDesc] [char](10) NULL,
 [FiscalSemesterDesc] [char](10) NULL,
 [SimpleFiscalPeriod] [varchar](20) NULL,
 [SimpleFiscalPeriodNo] [int] NULL,
 [FiscalPeriodLabel] [nchar](2) NULL,

) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[rds].[Date_dim]') AND name = N'PK_rds_Date_dim_DateKey')
DROP INDEX [PK_rds_Date_dim_DateKey] ON [rds].[Date_dim] WITH ( ONLINE = OFF )
GO

CREATE UNIQUE CLUSTERED INDEX [PK_rds_Date_dim_DateKey] ON [rds].[Date_dim] 
(
 [DateKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

The following section, adds the core date data, Typically, this is added one year at a time.
But, it can be extended to insert as many years as needed.


/* -----------------------------------------------------
   -----------------------------------------------------
   
 Process to add records to the rds.Date_dim
 In production, dates are added one year at a time. 
    Alternatively, you could front load the table with 
    20-30 years into future.

 @intLOOPend= 370     -- added as simple infinite loop break, 
             
 @dtStartDate = 'jan 1, 2014'  -- calendar year start
 @dtEndDate = 'december 31, 2014'; -- calendar year end

 Fiscal dates vary, so they are not included
   -----------------------------------------------------  */

set nocount on;
SET DATEFIRST 7;  -- 7=default=sunday, monday=1
go


declare @intLOOPcnt int, @intLOOPend int , @dtStartDate datetime, @dtEndDate datetime, @dtProcessDate datetime;

-- KEY SETTINGS / INPUTS
Select @intLOOPcnt = 0,@intLOOPend= 4000
, @dtStartDate = 'Jan 1, 2000', @dtEndDate = 'December 31, 2004';

-- init date to be processed
select @dtProcessDate = @dtStartDate;

while @intLOOPcnt < @intLOOPend AND @dtProcessDate <= @dtEndDate
  begin

   /*debug*/ --print 'test' + cast(@dtProcessDate as varchar)
  BEGIN TRY
    -- insert 
        
  insert into rds.Date_dim
   ( dateKey, fullDateAltKey, dayOfWeekNum, DayOfWeekName, dayOfMonthNum, dayOfYearNum
   , weekOfYearNum
   , MonthOfYearName, monthOfYearNum
   , calendarQuarter, calendarYear, calendarSemester
   --, fiscalYear, FiscalDayOfYearNum,FiscalPeriodLabel
   --, fiscalQuarter, fiscalSemester
   )

  select
    dateKey   = cast(CONVERT( varchar(8),  @dtProcessDate , 112) as int)
   , fullDateAltKey = CONVERT( varchar(8),  @dtProcessDate , 112)
   , dayOfWeekNum  = datepart(weekday,@dtProcessDate) 
   , DayOfWeekName  = datename(weekday,@dtProcessDate)
   , dayOfMonthNum  = datepart(day,@dtProcessDate)
   , dayOfYearNum  = datepart(dayofyear,@dtProcessDate)
   , weekOfYearNum  = datepart(week,@dtProcessDate)
   , MonthOfYearName = datename(month,@dtProcessDate)
   , monthOfYearNum = datepart(month,@dtProcessDate)
   , calendarQuarter = datepart(quarter,@dtProcessDate)
   , calendarYear  = datepart(year,@dtProcessDate)
   , calendarSemester  = case when datepart(quarter,@dtProcessDate) in (1,2) then 1 else 2 end 
   --, fiscalYear        = datepart(year,@dtProcessDate)
   --, FiscalDayOfYearNum = datepart(dayofyear,@dtProcessDate) 
   --, FiscalPeriodLabel  = '  '
   ;

  END TRY
  BEGIN CATCH

   print 'CATCH - error: ' + cast(@@error as varchar) + ',  ProcessDate:  '+ cast(@dtProcessDate as varchar);
  END CATCH
      
  -- next item
  select @intLOOPcnt = @intLOOPcnt + 1;
  select @dtProcessDate = dateadd(day,1,@dtProcessDate );

  end
go

This final section simply updates the few remaining values. To keep the build script simple, I separated this out of the main insert. You could include it, but this is run infrequently, and simplicity is more important than having an impressive, but complex insert statement.

-------------------------------------------------------------------------------------------------------------------------
-- OTHER CALENDAR VALUES FOR UPDATE 
-------------------------------------------------------------------------------------------------------------------------
     
update d set 
    -- select top 50 
  SimpleCalendarDate = MonthOfYearName + ' ' + CAST(DayOfMonthNum as varchar) + ', ' + cast(CalendarYear as varchar)
 ,SimpleCalendarMonth = MonthOfYearName + ' ' + cast(CalendarYear as varchar)
 ,SimpleCalendarQtr = 'CY Q' + CAST(CalendarQuarter as CHAR(1))
 ,SimpleCalendarYear = 'CY ' +  cast(CalendarYear as varchar)
 ,CalendarQuarterDesc = 'Q' + CAST(CalendarQuarter as CHAR(1)) + ' CY ' + cast(CalendarYear as varchar)
 ,CalendarSemesterDesc = 'H' + cast(CalendarSemester as CHAR(1)) + ' CY ' + cast(CalendarYear as varchar)

    from rds.date_dim as d 
 WHERE d.DateKey between 20050101 and 20051231;


Friday, July 22, 2016

EXASOL and SQuirreL SQL - Install JDBC Drivers to make the connection

Before you can use SQuirreL SQL to connect to EXASOL, you first have to install the EXASOL JDBC drivers. And a key part of adding a new JDBC driver in SQuirreL SQL is that name of the class name.


Summary:



  1. Click on Drivers Tab on the far left (typically below Aliases tab)
  2. Either:
    1. click on the Blue Plus, or
    2. Using the menu chose:  Drivers | New Driver
  3. This brings up an empty Add Driver window
  4. Extra Class Path - tab
    1. In the Extra Class Path tab, use the Add button, and navigate to the folder where the EXASOL jdbc jar is located. Choose the exajdbc.jar file, and click on Open.
  5. Back in the Add Driver window, add some details:
    1. Name:                     I used EXASOL exajdbc, but this is user defined.
    2. Example URL:    Optional, but suggest this:  jdbc:exa:<URL attribute>:<port>
    3. Website URL:     Optional.
    4. Class Name:         com.exasol.jdbc.EXADriver  (required)
  6. OK, click on OK, and your new driver is all set.



Detailed Steps


Click on the Drivers tab on the far left:



An empty Add Driver window:




In the Extra Class Path tab, click on the Add button. This brings up a file navigator.  Locate the EXASOL JDBC driver file: exajdbc.jar.



Choose Open.
Back in the Add Driver window, add some details:

  • Name:                  I used EXASOL exajdbc, but this is user defined.
  • Example URL:    Optional, but suggest this:  jdbc:exa:<URL attribute>:<port>
  • Website URL:     Optional.
  • Class Name:        com.exasol.jdbc.EXADriver  (required)




OK:  click on OK, and your new driver should be all set. Typically, in the bottom window, you will see the following:



That's it.  Now you can make a connection to your EXASOL database.To do this, first setup an Alias.

Add Alias:  


Use the Add Alias Blue Plus, or from the menu:  Aliases | New Alias...
In the popup Add Alias window. Provide a name for your Alias, select the EXASOL driver (the one you just added), add in the URL, the user name and password.

URL:  use the format:  jdbc:exa:<URL attribute>:<port>.  The default port for EXASOL is 8563.


This creates a permanent Alias for you. When you first set the Alias up, you get a popup asking if you want to connect.



With that, you have a permanent alias setup in SQuirreL SQL that makes the connection to your EXASOL database.






EXASOL - Query Tool Options

Tools to see inside EXASOL -  query and data


From what we have discovered, EXASOL has a split management approach.  General administrative functions are managed using a web portal, while data related functions are handled by a SQL query tool.  For this post our focus covers traditional DDL/DML tasks using a query styled tool.  In a future post, we'll look deeper into the web portal  'EXAoperation'  that supports general administration and operations.

If you are a SQL Server shop, and you use SQL Server Management Studio you already know that it is focused on SQL Server.  For EXASOL, SSMS is not an option.  So, while EXASOL provides the EXAplus query tool, we wanted to find out if other tools could be used to query EXASOL - from our experience, developers like to stay with the tools they have.  Plus, before moving into a new data framework, we like to know our options.  This is not by any means an exhaustive list. Just a short list of tools we have worked with in the past that we have found useful. Plus, the other tools are free.  

Summary

    Connecting to EXASOL:
Tool
Ubuntu
Windows 10
EXAplus, v 5.0.15
Success
Success
DBeaver, v3.7.1
Success (1)
Success (1)
SQuirreL SQL, v3.7.1
Success (2)
Success (2)
Oracle SQL Developer, v 4.1.3.20
n/a
Not Successful (3)

n/a:  at this time, we don't have these tools installed on the listed OS.

(1)  DBeaver had the EXASOL jdbc driver installed and/or recognized the existing driver.
(2)  We had to install the EXASOL jdbc driver manually
        see - 
SQuirreL SQL - Install JDBC Drivers to make the connection to EXASOL
(3)   SQL Developer is Java based, and supports third party JDBC drivers, so we are not sure
        why it could not make a connection.   (see the SQL Developer menu item:  
          Tools | Preferences | Database | Third Party JDBC Drivers)


Consider


For general query building, data review and general DDL/DML tasks, both EXAplus or DBeaver are good choices. And EXASOL's EXAplus tool has some nice data import and export tools, and it's good to spend some time with it since it is the provided tool. But, if you want to get a deeper look inside EXASOL, also consider SQuirreL SQL. It has a lot going on, so it might be confusing at first, but its object explorer gives you insight into the EXASOL system that EXAplus just does not have. The downside of SQuirreL SQL is that it's not user friendly when building complex SQL. Realistically, expect to use more than one tool.

EXASOL does have a metadata viewer, called the "Data Provider Metadata Viewer." According to the manual, it is installed when you install the ADO.NET driver.  This tool, rather than SQuirrel SQL might be the tool for you to explore the internals.

Other Tools

The three additional tools we looked at are Java based and use JDBC. EXASOL supports ADO.NET, ODBC and JDBC to connect to the database.  Based on this limited review, we suspect that if your tool is configured to support multiple databases using one of these protocols, there is a good chance it will work with EXASOL.