Packing intervals

Solution to the "packing intervals" problem by Itzik Ben-Gan:
http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx

Scroll to code listing 4 for the SQL code.

Registration is required to copy and paste the code. It can only be copy and pasted from the PDF that can be downloaded after registration.

Execute query under cursor

There is no way in SQL Server Management Studio (SSMS) to execute the query under the cursor. There is the option to execute the entire script with F5 or CTRL+E, but the script might contain multiple SQL statements.

The quickest solution is to install a freeware add-in:

• Download dbForge SQL Complete:
  Note: registration is required
• Install the add-in

The option for auto-capitalization and intellisense is enabled by default. This can be disabled, so only the "execute query under the cursor" (CTRL+SHIFT+E) feature remains:

• Start SMSS
• SQL Complete -> Options -> General -> uncheck "Enable SQL Complete"

Count of weekend days in date range

Use the formula in "calc_name" or "calc_df" to calculate the number of inclusive weekend dates in a date range. Inclusive means that if e.g. the start date is a Saturday, it is counted as 1. The code is pure SQL Server T-SQL, with no need for helper tables or cursors.

Note:

• "Set datefirst" is not needed, but can be used to check the correct outcome of the "calc_df" calculation
• The "calc_df" function is more robust, since it does not rely on localized day names. But it is a bit more verbose and obfuse then "calc_name"
• Column "correct" contains the correct number of weekend dates that can be used as a reference for the calculation
• Subquery X contains some random test values for start- and enddates

SET DATEFIRST 7

SELECT
x.*,
(DATEDIFF(wk, sd, ed) * 2)
  +(CASE WHEN DATENAME(dw, sd) = 'Sunday' THEN 1 ELSE 0 END)
  +(CASE WHEN DATENAME(dw, ed) = 'Saturday' THEN 1 ELSE 0 END) as calc_name,
DATEDIFF(wk, sd, ed) * 2
  +CASE DATEPART(dw, sd)+@@datefirst WHEN 8 THEN 1 ELSE 0 END
  +CASE DATEPART(dw, ed)+@@datefirst WHEN 7 THEN 1 WHEN 14 THEN 1 ELSE 0 END as calc_df,
@@DATEFIRST as datefirst,
(DATEDIFF(wk, sd, ed) * 2) as weeks,
CASE WHEN DATENAME(dw, sd) = 'Sunday' THEN 1 ELSE 0 END as sun_start,
CASE DATEPART(dw, sd)+@@datefirst WHEN 8 THEN 1 ELSE 0 END as sun_start_df,
CASE WHEN DATENAME(dw, ed) = 'Saturday' THEN 1 ELSE 0 END as sat_end,
CASE DATEPART(dw, ed)+@@datefirst WHEN 7 THEN 1 WHEN 14 THEN 1 ELSE 0 END as sat_end_df,
DATEPART(dw, sd)+@@datefirst as sun_df,
DATEPART(dw, ed)+@@datefirst as sat_df
from
(
select 1 as correct, '2013/3/17' as sd, '2013/3/22' as ed union
select 2 as correct, '2013/3/16' as sd, '2013/3/22' as ed union
select 2 as correct, '2013/3/16' as sd, '2013/3/17' as ed union
select 3 as correct, '2013/3/16' as sd, '2013/3/23' as ed union
select 3 as correct, '2013/3/10' as sd, '2013/3/22' as ed
) x

Quipu JDBC connection to MS Access

There are several ways to connect to a MS Access database from Java, like the low-level MS Access library called Jackcess: http://jackcess.sourceforge.net/

The downside is that most Java applications that support heterogeneous connections have a default built-in procedure to connect and open the database. Most of the time, the "getSchemanames" function is called and this one doesn't function as expected with MS Access. Quipu uses this method also.

Therefore, a connection can be made in the following way, but results in an error when retrieving the schema names during reverse engineering with Quipu from MS Access

1) JDBC to ODBC bridge

Create a connection to the MS Access database d:\test.mdb using a native and DSN-less JDBC to ODBC bridge:

Manage connection types:
Add a new connection type:

name= ms access jdbc
type= jdbc
database or file delimiter=
database or file required= false
jdbc port delimiter= :
jdbc default port=
jdbc driver= sun.jdbc.odbc.JdbcOdbcDriver
url type= jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=

Manage connections:
Add a new connection:

name: ms access jdbc
database= test.mdb
hostname= d
port= /

Notes:

• The url type above is for Java x64 and MS Access x64. If Java x32 is used, then the url type= jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=
• No additional jdbc driver (jar file) needs to be installed, because the driver is embedded in the Java JRE runtime

There is an alternative method:

2) UCanAccess

It is a open source database driver written by Marco Amadei that behaves identical to the JDBC to ODBC bridge, but
- doesn't use ODBC
- handles the getschemanames call, using a default "PUBLIC" schema

Download it here: http://sourceforge.net/projects/ucanaccess

To use it with Quipu:
- Extract the jar files from the \lib folder in the zip file to Quipu's \webapps\qp\WEB-INF\lib folder
- Sort files by name and make sure no duplicates exist, e.g. hsqldb and hsqldb-1.8.0 is not allowed.
- Remove the duplicate with the lowest versionnumber. In the case of hsqldb, which has no versionnumber: keep the one from the UCanAccess zipfile

Manage connection types:

Add a new connection type:
name= ucanaccess
type= jdbc
database or file delimiter=
database or file required= false
jdbc port delimiter= :
jdbc default port=
jdbc driver= net.ucanaccess.jdbc.UcanaccessDriver
url type= jdbc:ucanaccess://

Manage connections:

Add a new connection:
name: ucanaccess
database= test.mdb;showschema=true
hostname= d
port= /

Notes:

- The "showschema=true" property bypasses the getSchemanames error

Uninstall Microsoft Security Essentials

Microsoft Security Essentials can be uninstalled from Windows 7 as follows:

• Download the installer from the Microsoft website (currently: http://windows.microsoft.com/en-US/windows/security-essentials-download)
• Save the resulting file as "c:\mseinstall.exe"
• Win + R, "cmd", enter, type in "c:\mseinstall.exe mssefullinstall-amd64fre-en-us-vista-win7.exe /U"
• To reinstall, just start mseinstall.exe

Flash 11.4 in SRWare IronPortable v22

To get Flash support in the portable version of Iron v22:

• Start IronPortable and type "about:plugins" in the URL bar
• Scroll to the section titled "Flash"
• If there is one enabled item within that section, then Flash should already work. Test it here: http://www.adobe.com/software/flash/about/
• If the section does not exist or there are no items in it, then 
• Create a folder called "iron\plugins" in the IronPortable folder
• Download the file NPSWF32_11_4_402_278.dll from the internet or copy it from the system Flash installation folder and put it in the previously created folder.
  Example system Flash installation folder: C:\Windows\SysWOW64\Macromed\Flash\NPSWF32_11_4_402_287.dll
• If there are more items, then 
• Disable all items but one for Flash v11.4
• Restart iron and type "about:plugins"
• If all Flash items are disabled, then enable one Flash item

Note: putting file gcswf32.dll in the root of the IronPortable folder does no longer work!

Stop Outlook from reformatting telephone numbers

A telephone number gets reformatted when adding it to a contact in Outlook, e.g. "+31.." becomes "31".

This behaviour can be stopped as follows:

• Start, Search: "phone" and hit enter. If this doesn't work, then start the Telephony service:
• Start, Search: "services" and hit enter - Double click Telephony (TapiSvr)
• Startup type: automatic
• Click Start
• In the Phone and Modem screen, select a location or, if required, make a new location and use anything for the area code
• Click Edit..
• Country/region: Internation Freephone Service
• (Stop and) start Outlook

Note: it doesn't stop reformatting completely.

E.g. "+31-12345678" still gets reformatted to "+31 12345678", but at least the starting "+" sign remains untouched. And "+31612345678" gets reformatted to "+31612345678 "

Epson AL-C900 with empty color toner

The Epson AL-C900 Aculaser has a default setting that makes it impossible to continue printing if one of the color toners/consumables is empty. Even in black and white.

This can be fixed as follows:

• Connect the printer directly, i.e. via USB to the PC or laptop and turn the printer on
• Install "Status Monitor 3 1.1b" from the Epson website:
  http://esupport.epson-europe.com/FileDetails.aspx?lng=en-GB&id=31761
  The name of the downloaded file is: epson31761eu.exe
  Note:  there is no separate Windows 7 x64 version, so use the Windows Vista x64 version for that platform
• Right-click the "Status monitor 3" icon in the tray and:
• Epson AL-C900 -> Printer Setting...
• Check "Continue printing when consumables are empy"

MS Access DSN-less connection

It is possible to create a DSN less linked table and/or pass-through query from Microsoft Access to SQL Server Express without deleting those objects first, as is often suggested, e.g. here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

This is safer, because the linked objects will be gone if the code fails after deleting the objects, but before re-adding has completed.

One of the downsides of this approach is that MS Access has to be re-started for the new connections to be effected. In more detail: Access caches ODBC connections. For example, when a working ODBC connection of a linked table is replaced with one that has a wrong username, it will still work, because the previous one is retrieved from cache. It is not possible to clear this cache programmatically or manually, unless Access is restarted. Furthermore, Access only adds new connections to the cache when the driver, server or database property changes. Adding a new and correct ODBC connection to a linked table that only as a different login than the old one has no effect! The old connection from the cache will be used.

Another downside is that the database grows significantly after each execution of the code. A compact/repair resolves this. Set the "compact on close" property on the database.

Use this code to loop over all tables and queries to set the connection string:

Dim db As Database
Dim td As TableDef
Dim qd As QueryDef
Dim strODBC As String

  Set db = DBEngine(0)(0)
  strODBC = "ODBC;DRIVER=SQL Server;SERVER=REMOTESERVER\SQLEXPRESS;DATABASE=TestDB;UID=sa;PWD=sa;Trusted_Connection=No;"

  ' reconnect pass-through queries
  For Each qd In db.QueryDefs
    With qd
      If Left$(.Connect, 4) = "ODBC" Then
        ' note: for a passthrough query the uid and pwd are always stored
        .Connect = strODBC 
        .Close
      End If
    End With
Next qd

  ' reconnect linked tables
  For Each td In db.TableDefs
    With td
      If Left$(.Connect, 4) = "ODBC" Then
        ' note: for a linked table the uid and pwd are only stored when this property is set!
        .Attributes = dbAttachSavePWD
        .Connect = strODBC
        .RefreshLink
      End If
    End With
  Next td

  db.Close
  Set db = Nothing

Notes:

• Use DBEngine(0)(0) in stead of CurrentDB(); the implementation is 1000x faster
• Use Left$ in stead of Left; the implementation is faster
• This particular code assumes that DAO is used, not ADO
• The connection uses a SQL Server Authentication instead of Windows Authentication
• The connection string value is assigned to variable strODBC
• The connection string has to start with the text "ODBC;" both for the linked table as the pass-through query
• If the connection is to a default instance of SQL Server, the string "\SQLEXPRESS" can be removed
• By default, the username and password are stored for a pass-through query, but not for the linked table. This appears to be a bug, it either should be set for both or for neither. Setting the uid/pwd parameters for a linked table does, however, not result in an error
• ... but if the code td.Attributes = dbAttachSavePWD is used, then the uid/pwd are also stored for a linked table. At least for MS Access 2010 on Windows 7 x64, this is enough. In some cases two registry settings need to be set for this to work:
  REGEDIT4
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\Network]
  "DisablePwdCaching"=dword:00000000
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Common\Security]
  "DisablePwdCaching"=dword:00000000
• After the code has run, the connection string has been set for all the objects, but is not yet effective! For this, MS Access needs to be closed and reopened
• I found that the reconnecting loop using DRIVER=SQL Server in stead of
  DRIVER={SQL Server}or
  PROVIDER={SQL Server Native Client 10.0 ODBC Driver}
  was much faster
• Close and destroy the reference to the database object to prevent memory leaks

Related errors might include:

"login failed. The login is from untrusted domain and cannot be used with windows authentication"

PALO backend is unavailable

You might get an "backend is unavailable" error when connecting to the PALO server from an internet page.

Check the %SYSTEMDRIVE%\Program Files (x86)\Jedox\Palo Suite\log\core.log file for a probable cause. In my case the fonts folder did not exist:

[2012/05/02 10:22:28] ERROR [core] palo web core server version boost::filesystem::basic_directory_iterator constructor: The system cannot find the path specified: "C:\WINDOWS\Fonts"

The reason for this error is that Windows and the program files folder are installed on my G drive and PALO apparently hardcodedly searches the C drive for these fonts. The service PaloWebCoreServerService cannot start because of this error.

Resolution:

• Create a folder called c:\windows\fonts
• Copy at least the following fonts from the g:\windows\fonts folder:
  andalemo.ttf
  arial.ttf
  arialbd.ttf
  comic.ttf
  courier.ttf
  georgie.ttf
  impact.ttf
  times.ttf
  trebuc.ttf
  verdana.ttf
  webdings.ttf
  If any of these fonts is not available in the folder, then download them from Microsoft:
  http://sourceforge.net/projects/corefonts/files/the%20fonts/final/
  
• Start the service PaloWebCoreServerService

Bamboo Pen & Touch supported driver not found

If you try to start the Wacom Bamboo Preferences application, but get an error message displaying "a supported tablet could not be found", then check if the service "Wacom Consumer Touch Service" is enabled and running.

Oracle OLE DB source in SSIS

An OLE DB source to Oracle can be added to a SSIS package.

Example data

Server: locksnlatches.mydomain.com
Oracle instance/SID: dwh
Username/schema: scott
Password: tiger

An OLE DB source to Oracle can be added as follows:

• From the menubar, select SSIS -> New connection... -> OLEDB -> Add -> New
  Provider: Oracle Provider for OLE DB
  Server or filename: locksnlatches/dwh
  User name: scott
  Password: tiger

Notes: 

• The "Oracle Provider for OLE DB" provider always exists in SSIS. It is a wrapper around the Oracle Client driver that is installed when installing Integration Services. However, the Oracle Client has to be installed separately. It can be downloaded from the Oracle website: instantclient-basiclite-nt-11.2.0.2.0. Install the 32 bit version of the driver to be able to use it in the design environment. The Visual Studio / SSIS IDE does not support 64 bit components
• It is possible to use the 64 bit Oracle Client driver during execution of the package, e.g. via dtexec, but it needs to be installed additionally. The SSIS package has to be configured to use this different version during runtime.
• If the server cannot be found, then use the fully qualified name. One reason might be that the Oracle server is located in a different (Windows) domain as the SSIS server.

Reference SQL Server table with spaces from Oracle

It is possible to select data from a SQL Server table that contains spaces by using a database link from Oracle.

The syntax is as follows:

SELECT * FROM "DBO$TABLE WITH SPACES"@MYDBLINK

with

SQL Server login/user: dbo
SQL Server table name: table with spaces
DB link: mydblink

Query Oracle from SQL Server

Start with the following steps:

• Install the Oracle Instant Client (OIC) on the machine where SQL Server is running and add the "Oracle Provider for OLE DB" driver, e.g. to folder c:\oracle11g.
• For a SQL Server x64 version running on a Windows x64 OS download the instantclient-basiclite-windows.x64-11.2.0.2.0.zip. This is a 22 MB download.
• Then add the Oracle Data Access components, to get the "OLE DB for Oracle" driver:  http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
  At the time of writing (March 2012): select the "64-bit ODAC 11.2 Release 4 (11.2.0.3.0) Xcopy for Windows x64". This is a 54 MB download
• Inside SQL Server Management Studio:
  Linked Servers -> Providers -> OraOLEDB.Oracle -> Allow inprocess = true
• Optional: to enable adhoc querying (querying without a linked server), then enable this:
• SQL Server 2005 via surface area configuration tool
• SQL Server 2008 via facets: inside SQL Server Management Studio, right click the server -> facets -> facet: surface area configuration -> AdHocRemoteQueries = true
• Optional: if the connection to Oracle should be based on a TNS names entry, then add an "admin" folder to the OIC folder. Put or create an tnsnames.ora file there, e.g. c:\oracle11g\admin\tnsnames.ora
• Optional: use "tnsping" to test the entry from the command prompt, e.g. "tnsping dwh.world". If it doesn't recognize the entry, then create an environment variable called "TNS_ADMIN" and let it point to this "admin" folder, e.g. c:\oracle11g\admin
• Optional: if the following steps to get data from an Oracle instance don't work, then edit this registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI.
  Set the following values:
  OracleXaLib=oraclient11.dll
  OracleSqlLib=orasql11.dll
  OracleOciLib=oci.dll

Note: these DDLs can be found in the c:\oracle11g\product\11.2.0\client_1\BIN folder. The specific version depends on the Oracle Client and might be for example oraclient10.dll en orasql10.dll. Use the names of the DDLs as found in this folder.

Three ways to get the data from an Oracle instance

Example:

Server: locksnlatches
Oracle instance (SID): dwh
Port: 1521
Schema/user: scott
Password: tiger
tnsnames.ora entry:

dwh.world =
(DESCRIPTION=
  (CID=DWH_APP)
  (ADDRESS_LIST=
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=locksnlatches)(PORT=1521)
     )
   )
  (CONNECT_DATA=
    (SID=dwh)
  )
)

1) Using a linked server

• Linked Servers -> New linked server... ->  General ->
• linked server: lnk_locksnlatches (this name can be anything)
• provider: Oracle provider for OLE DB
• product name: oracle (this name can be anything)
• data source: (DESCRIPTION=(CID=DWH_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=locksnlatches)(PORT=1521)))(CONNECT_DATA=(SID=dwh)))
• provider string: leave empty!
• location: leave empty!
• catalog: leave empty!
• Linked Servers -> New linked server... ->  Security ->
• Be made using this security context
• Remote login: scott
• With password: tiger

Use the four-point notation to retrieve data from Oracle:

SELECT 1 FROM lnk_locksnlatches..scott.dual

Note: the "security" tab specifies the Oracle schema/user and password used by the connection!

2) Without a linked server (TNS-less)

SELECT * FROM OPENROWSET('OraOLEDB.Oracle', '(DESCRIPTION=(CID=DWH_APP)(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=locksnlatches)(PORT=1521)))(CONNECT_DATA=(SID=dwh)))';'scott';'tiger','SELECT 1 FROM DUAL') x

3) Without a linked server (TNS-full)

SELECT * FROM OPENROWSET('OraOLEDB.Oracle', 'dwh.world';'scott';'tiger','SELECT 1 FROM DUAL') x

Power view on analysis services tabular

How to create a Power View report using Sharepoint 2010 and Analysis Services 2012 Tabular as the data source?

0) Prerequisites
A server called "bisql01" with the following services installed:

•   sql server 2012 (mssqlserver = default instance)
•   analysis services 2012 (mssqlserver = default instance)
•   analysis services 2012 tabular mode / vertipaq (astabular = named instance)
•   reporting services (mssqlserver = default instance)
•   sharepoint 2010 installed at https://powerview.locksnlatches.com

All services run under user "cloud\bi01_sql". Work is done locally on this server and refered to as "localhost". Data is loaded from the SQL Server database "CPDV" into the Analysis Services database "CPDM".

INSIDE VISUAL STUDIO 2012

1) create project
new project -> analysis services tabular project
  name: cp_windows7

2) set deploy target of model
rightclick project "cp_windows7" -> properties ->
  server: localhost\astabular
  database: cpdm
  impersonation settings: default

note: there is probably also a default instance of analysis services running; this is the regular multidimensional cube instance like the previous versions of sql server, not the named tabular model instance

note: the cpdm tabular database will be created on deploy

note: there will also be a work tabular database for the current project named after the project, first data source and a guid, e.g. CPDV_BI01_SQL_01e0810e-c8ab-46fd-afe6-098348336a9a. Do not delete this database from the analysis server server!

3) add tables to model
model -> import from datasource -> microsoft sql server:
  server name: bisql01 (or "localhost")
  log on to the server: use windows authentication
  database: cpdv
  use service account,
  select tables/views,
  finish

edit the bism model: model -> model view -> diagram view. If there are no relationships in the source, then they can be added here by dragging and dropping fields from a child to a parent table or vice versa.

change connection type: model -> existing connections -> edit
  use sql server authentication
  name: sa
  password: ***

note: use model -> existing connections, to add tables later on from the same connection

4) set date dimension
view the data: model -> model view -> data view

click the date column, table -> date -> mark as date table

note: mark as date table does not work in the diagram view, only in the data view

note: make sure the date column is unique and continuous

note: a fact table can join to the date dimension on this date field, but also on a unique number field. A date field also needs to be selected in the latter case for "mark as date table" to work.

5) set authorization
models -> roles -> new
  name: reader
  permissions: read

models -> roles -> members -> add
  cloud\bi01_sql
  cloud\bi01_admin (=sharepoint account from which powerview is started)

note: if members are added to this role from management studio to the tabular model instance database then they will be overwritten at the next deploy of the project! So, specify the members in the project.

6) build -> deploy

INSIDE SHAREPOINT 2010

7) create semantic model connection
documents -> new document -> bi semantic model connection ->
  filename: model.bism (this is the name of the document in sharepoint; it can be anything)
  servername: bisql01\astabular
  database: cpdm

note: to fix error "database cannot be found", double check that the reader role has read permissions and that the windows accounts used by Sharepoint are added to that group

8) create report data source to semantic model connection
documents -> new document -> report data source ->
  data source type: microsoft bi semantic model for power vier
  connection string: datasource='https://powerview. locksnlatches.com/Shared%20Documents/model.bism'
  credentials: stored credentials
    username: cloud\bi01_sql
    password: ***
  availability: enable

9) create powerview report
click dropdown arrow of report data source -> create power view report

note: to fix error "User 'DOMAIN\user' does not have required permissions": start IE in "run as administrator" mode and connect using "localhost" iso. the server name.

note: the reporting services logfiles are located here C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\LogFiles

Annoying search engine abroad question

Google Chrome asks you if you would like to use another omnibox search engine when you move from one country to another. When you move across the border a lot, then this becomes quite an annoying message:

"Would you like to search with google.country1 instead of google.country2?"

If you always want to use e.g. google.nl, then make the following change:

• Wrench -> Options -> Basics -> Manage search engines
• Scroll to the bottom and fill in the columns:
• In the first column: GoogleNEW
• In the second: Google
• In the third: http://google.nl/search?{google:RLZ}{google:acceptedSuggestion}{google:originalQueryForSuggestion}{google:searchFieldtrialParameter}{google:instantFieldTrialGroupParameter}sourceid=chrome&ie={inputEncoding}&q=%s
• Hit Enter/Return, else the results will no be saved
• Hover over the third column and click "make default"

Quipu connections

Several ways to create a database connection in the open source data warehouse generator Quipu.

SQL Express

• SQL Server configuration manager -> 
• Protocols for SQLExpress -> TCP/IP: enabled
• TCP/IP -> IP Adresses -> IP All -> TCP Dynamic Ports: empty
• TCP/IP -> IP Adresses -> IP All -> TCP Port: 666
• SQL Server management studio -> SQL Express server properties -> Security -> Server Authentication: SQL Server and Windows Authentication mode
• Create a SQL Server authentication login on the database server and assign it to the database that will be used by the connection, e.g. login: quipu, password: quipu
• If the database runs on the same machine as Quipu, use "localhost" as the host name. Otherwise use the remote host name

Note:

• Do not use the instance name, e.g. using "AdventureWorks;instance=sqlexpress" as database works, but will, depending on the SQL Server version, result in an "unable to get information" error.

Live calendar displays incorrectly in Chrome

At last the Windows Live calendar displays appointments correctly again in the latest Google Chrome beta release, 14.0.835.2. They fixed the improper displaying of events.

Up until now you had to use a workaround to have the bars visible in the month view:

• Download the extention Stylish
• Download the style "Hotmail calendar events fix for Chrome browser"

Working with dates in OBIEE

Here are several options to set date formats in OBIEE. Option #1 is the best way to work with dates and comparison of dates to strings containing dates in a particular format.

See also: http://gerardnico.com/wiki/dat/obiee/cast_as_date

The most important settings (with example values) are:

• DATE_DISPLAY_FORMAT parameter in nqsconfig.ini: YYYY/MM/DD
• DATESHORTFORMAT parameter in localedefinitions.xml (based on the locale set in nqsconfig.ini): M/D/YYYY
• NLS_DATE_FORMAT parameter of the session (or database default): DD-MON-RR

Do not confuse these with parameters in dbfeatures.ini. There this format is the default for almost all database platforms in parameter DATE_FORMAT parameter.

Method 1 is used for implicit casting only if physical -> Database -> Features: CAST_SUPPORTED is available and enabled. Otherwise the settings described in method 4 (nqsconfig.ini) are used.

1) Tell Oracle how to interpret the casting of a string to date or vice versa (preferred method)

Explicitly set the Oracle NLS property for this to work unambiguously. This property can be set on a database level, affecting all connections made to the database. Alternatively, the property can also be set on the OBI connection in the physical layer of the Admin tool to only affect the current session:

• Double-click "Connection pool" for the Oracle connection
• Select "Connection Scripts" tab
• Add "Execute on Connect" script and type:
  ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD'

Then, use the following function in Answers formulae:

CAST('20110215' TO DATE)

Note: to check the value of the NLS_DATE_FORMAT parameter at the database level, e.g. from SQL Developer, execute the following statement:

SELECT * FROM (

SELECT 'instance' as domain, parameter, value FROM NLS_INSTANCE_PARAMETERS UNION

SELECT 'database' as domain, parameter, value FROM NLS_DATABASE_PARAMETERS UNION

SELECT 'session' as domain, parameter, value FROM NLS_SESSION_PARAMETERS

) x WHERE PARAMETER ='NLS_DATE_FORMAT'

2) Convert a date to string or vice versa using an Oracle database function

Use the OBIEE function EVALUATE to make use of an Oracle database function. In this case, TO_DATE:

Convert string to date:
EVALUATE('TO_DATE(%1,%2)' AS DATE,'20111231','YYYYMMDD')

Examples of what doesn't work:
EVALUATE('TO_DATE(%1)' AS DATE,'20100101')

--> does not work, unless the date happens to be in the nls parameter format

CAST('20110215' TO DATE) 

--> does not work, unless the string happens to be in the nls parameter format

Convert date to string:
EVALUATE('TO_CHAR(%1,%2)' AS CHAR,DIM_DATE.TODAY,'YYYYMMDD')

EVALUATE('TO_CHAR(%1,''YYYYMMDD'')' AS CHAR,DIM_DATE.TODAY)

Examples of what doesn't work:
Conversions may fail with "date format not recognized" or "datetime value does not match the specified format".

EVALUATE('TO_CHAR(%1,%2)',DIM_DATE.TODAY,'YYYYMMDD')
--> does not work, because the result needs to be explicitly cast to a text datatype

EVALUATE('TO_CHAR(%1)',DIM_DATE.TODAY)
--> does not work, unless the date happens to be in the nls parameter format

3) Convert a date to string using different OBIEE functions

An alternative method is to convert the date to a string in stead of converting a testing string to a date. The difference is this:

DATE = ConvertToDate(STRING) 

vs 

ConvertToString(DATE) = STRING

Convert date to number:

year(FACT_ORDERLINE.DATE) * 10000 + month(FACT_ORDERLINE.DATE) * 100 + dayofmonth(FACT_ORDERLINE.DATE) = 20110231

Note: here the lefthand and righthand side are numbers, not strings, but the idea is the same.

4) Convert a string to date using the OBIEE function "DATE"

In OBIEE, an equivalent of the Oracle function TO_DATE is the DATE function which has a fixed syntax:

YYYY-MM-DD. 

For example:

DATE '2011-01-15'

Note:

• This syntax cannot be configured and does not depend on locale or nls settings
• The function cannot be found in the list of functions when adding a formula in Answers. 
• It does not use ( or ) signs, but only single-quotes

5*) Set uniform datatypes in the Physical Layer

Set the datatype property in the physical layer on the fields/attributes. Allowed date/time datatypes are:

• DATE
• TIMESTAMP

Try to use one, e.g. DATE, as the default to achieve a more universal way of working with dates. TIMESTAMP is required when there is an essential time part. If it is always "00:00:00", then use DATE.

6*) Locale definitions in configuration files

• [bipath]\web\config\localedefinitions.xml
  -- displays possible formats to choose from
  -- locale "us-en" is based on locale: us
  -- locale "us" contains all possible attributes to choose from, so is independend of the regional settings
  -- of the Windows server
  locale: us-en
• [bipath]\server\config\nqsconfig.ini
  -- for displaying messages
  -- this maps to "us-en" in localedefinitions.xml
  LOCALE = "English-usa";
  -- for input and output of dates
  DATE_TIME_DISPLAY_FORMAT = "yyyy/mm/dd hh:mi:ss";
  DATE_DISPLAY_FORMAT = "yyyy/mm/dd";
  TIME_DISPLAY_FORMAT = "hh:mi:ss";
  -- when on, obi does not guess what a string containing a date means
  STRONG_DATETIME_TYPE_CHECKING = ON;

7*) Locale definitions in Answers webinterface

Set the locale, e.g. "English - United states" on the connection window or in your account settings: Settings -> Account

8*) Save display format

The default display format of a date is set in dbfeatures.ini (?)

It can be overriden for all date datatypes or specific attributes in Answers:

Criteria -> Column properties -> Save -> As the system-wide...

Test casting of date to string

Example: database or session NLS parameter = 'YYYYMMDD'

CAST(DIM_DATE.TODAY AS CHAR)
CAST('20110211' AS DATE)
CAST('01-FEB-11' AS DATE) --> will fail if nls parameter is set to YYYYMMDD

Notes:

• Column properties -> date format (i.e.date or text) is set automatically after the result of a conversion. It does not steer, in any way, the input of functions.

No login prompt for Exchange

It is possible to suppress the login prompt that appears in Outlook, when trying to connect to an Exchange server via Outlook Web Access.

This prompt can be skipped:

• Close Outlook
• Get the Exchange server name: Control Panel -> Mail (32 bit) -> Email accounts... -> View or change existing e-mail accounts -> Double click the "Exchange" account
• The microsoft exchange server name, e.g. exmbx01.acmexchange.com, becomes the exchange server name: *.acmexchange.com
• Get the webserver name: -> More settings -> Connection -> Exchange proxy settings
• The connection settings URL, e.g. webmail.acmeweb.com, becomes the webserver name: *.acmeweb.com
• Set proxy authentication settings: NTLM authentication
• Start the Credential Manager: Control Panel -> Credential Manager
• Add a windows credential, e.g. for jim@acme.com
  a. internet or network address: *.acmeweb.com
  b. username: jim@acme.com
  c. password: pwd
• Add another windows credential
  a. internet or network address: *.acmexchange.com
  b. username: jim@acme.com
  c. password: pwd

Start Outlook and send/receive. The login popup should be gone.

Keywords: microsoft exchange, outlook 2003, outlook web access, repeating login prompt