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

SkyDrive photos in Flipboard

Flipboard is a great app for the iPad. It displays a collections of RSS feeds as a magazine. The latest version (1.1.1) offers the ability to add a Google Reader account, which can contain a multitude of feeds. These feeds can even be categorized. Flipboard can easily display all feeds, the feeds in a category or individual feeds.

It has the ability to add a Flickr account to show the photos in that account. Unfortunately, Flickr is rather limited in its features: no more than 300MB per month upload.

There are alternatives for Flickr, like Google Picasa, Mediafire or Microsoft Live SkyDrive. Picasa has a storage limit of 1GB, which is even worse than Flickr. SkyDrive has a storage limit of 25GB, but no monthly upload quotum. The best thing about SkyDrive is that it has a RSS feed capability, so you can add it to your Google Reader account and have a magazine of photos. Mediafire has no upload or storage limitations, but no RSS feed capability, which is too bad, otherwise it would be the perfect photo-in-flipboard solution.

Because there is no dedicated Microsoft Live SkyDrive iPad app, this is a nice alternative.

Unfortunately, the RSS XML that SkyDrive returns is not interpreted by flipboard, like it is interpreted by, say, Google Reader, where the photos are displayed directly in the feed. Flipboard displays an empty article, with a link, that, when clicked, redirects to the photo. This is not useful. It turns out that the <link> element is the culprit.

Workaround

• Optionally, create a SkyDrive account. Create a Windows Live / SkyDrive account and create a new folder. Set it as a "photo" folder. Upload some photos to this account. Make a note of the "cid-" code in the URL (or on the SkyDrive webpage). It is the unique identifier for that account. Also make a note of the folder name.
  
  
• Create a conversion script that corrects the XML supplied by SkyDrive. Flipboard only displays the photos in the feed items, when the combination of the <link> tags of the <channel> and <item> tags form a valid URL. SkyDrive supplies the complete URL in the <link> tag of each item, so the <link> tag of the <channel> should be empty or not present at all.
  
  
• Host the script online. I have created a PHP script called default.php and am hosting it on freehostingnoads.net. An account can be freely created and their PHP configuration support the functionality needed to get HTML code from another website, in this case, SkyDrive. The complete URL to the website is:
  http://www.livestreem.freehostingnoads.net/default.php
  
  
• Optionally, make the script dynamic. I added the posibility to supply a SkyDrive account id (CID) and folder to the script. Example for CID "cid-dba80f3be68c2301" and folder "genericweb" :
  http://www.livestreem.freehostingnoads.net/default.php?cid=dba80f3be68c2301&folder=genericweb
  
  
• Add script URL to the Google Reader: add the URL of the conversion script to the Google Reader account. Use the complete URL, including the PHP script name.
  So:
  http://www.livestreem.freehostingnoads.net/default.php?cid=dba80f3be68c2301&folder=genericweb
  and not:
  http://www.livestreem.freehostingnoads.net/?cid=dba80f3be68c2301&folder=genericweb
  
  
• Optionally, test the feed. Google Reader updates the results from feeds at set intervals, that cannot be controlled. Pressing the "refresh" button is only useful after this interval has passed and Google has updated the feed internally. I use Chrome and, to test the feed realtime, I use the extention/add-on "Slick RSS"
  
  
• Add a Google Reader section to Flipboard: login with your username and password. This only needs to be done once. The domain name part of the link tag is used by Flipboard to prefix the text of an item with a bold text containing this text and a small icon. If there is no text, just a photo, it does not do this.

Some downsides:

• History explosion. Google stores the history of a feed. This can be annoying when testing a script with a lot of photos, because they get added again and again. Even worse, after deleting the subscription and adding it later, Google restores the full history! Google remembers the URL's and content of feeds that were added in the past.
• Low resolution. There is no easy way to get the full resolution photo from SkyDrive. It supplies a encoded path to a preview version - most of the time 600x400 pixels - and, when clicked from within the SkyDrive website, redirects to another encoded path for the full resolution.
• Photos are public. The photos need to be in a publicly available SkyDrive folder, unlike Flickr. With the Flickr photostream of Flipboard it is possible to login in with a username and password to a private folder.

SkyDrive ASPX pages

A list of .Net scripts to access SkyDrive:

• self.aspx: returns SkyDrive page for the specified document/photo
• embedphoto.aspx: returns a downsized version of the photo
• embedalbum.aspx
• feed.aspx: returns a XML document describing the RSS feed
• browse.aspx

Update 20110727:

Skydrive has been redesigned. The current feed works correctly with Flipboard, so the workaround above is no longer needed!

Combine two DVD's into one

When you want to combine two DVD's onto one disc, but keep the chapters, then try the following steps. I assume the video_ts folders of both DVD's are already available on your harddrive.

In most cases there is no vob file per chapter, but each vob file is filled to its maximum (1GB) and then a new one is created. Sometimes, however, there is a vob file per chapter. In this case, you only need to follow the steps following DVDStyler:

• Download and install DVDShrink (freeware)
• Start DVDShrink and click "Reauthor"
• In the right pane under "dvd browser", navigate to the video_ts folder of the first DVD
• Drag the desired chapters to the left
• In the right pane under "dvd browser", navigate to the video_ts folder of the second DVD
• Drag the desired chapters to the left
• Under File, click "Backup" and save to folder c:\shrink. This set automagically creates a vob file per chapter!
• Download and install DVDStyler (freeware)
• Start DVDStyler and add all .vob files from c:\shrink to the current project. The DVD main- and chapter menu's are created automatically 
• Optionally: add the names of the chapters to the chapter menus
• Burn the project to an iso file or directly to disc

Ofcourse, you can only do this with homemade DVD's or to DVD's you own legally and when local law permits it.

Use 32-bits Excel Source with 64-bits SSIS

The following is still work in progress and more of a braindump than anything else, but I've put it here, because someone might already find some use for it

Straight out of the box, you cannot use the Excel Datasource in SSIS on a 64-bits Windows platform , because the Excel Datasource uses a 32-bits OLEDB library. Microsoft has not released a 64-bits version of this library.

0) Use MADE 2010
There is 64 bits version for Office 2010, so for Excel 2010. It is called "Microsoft Access Database Engine  2010 Redistributable" of which there is a 32- and 64-bits version. We're interested in the 64-bits version, but this package can only be installed after removing all 32 bits Office applications!

a) Install MADE 2010 while NOT removing 32 bits Office applications:

AccessDatabaseEngine_x64.exe /passive

Notes:

• THE SYSTEM WILL AUTOMATICALLY REBOOT, SO SAVE WORK BEFOREHAND!
• Start Excel once to go through the configuration process. Start it a second time. It the configuration process starts again, then disable it by renaming this file to "setup_disabled.exe":
  c:\Program Files (x86)\Common Files\microsoft shared\OFFICE14\Office Setup Controller\setup.exe
• run the repair option for the Microsoft Access database engine 2010 program inside "add/remove programs" 

b) If that doesn't work, then also install the Data Connectivity Components:
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

1) Trick Windows to use 32-bits ODBC data sources
Try adding the SysWow64 folder to the beginning of your path:

Path=%systemroot%\SysWow64;...old values here...

When done via the command prompt via the SET-command, it will be undone when that window is closed.

2) Run SSIS in 32-bits mode
Use the 32-bit DTEXEC.EXE to execute the package containing the Excel Datasource. It is located in "Program Files (x86)\Microsoft Sql Server\90\Dts\Binn" (or for SSIS 2008: "Program Files (x86)\Microsoft Sql Server\10\Dts\Binn")

You can schedule this using "task scheduler" or SQL Agent.

3) Compile package as 32-bits
Package configuration properties -> Debugging -> Debugging Options -> Run64BtRuntime -> set to False.

4) Even dirtier trick: convert Excel spreadsheet to CSV beforehand
This can be done in several ways, with minimal coding required:

• If Excel is installed, then create a VBS script that uses CreateObject("Excel.Application") to create an Excel object. Then open the XLS file and save it as a CSV using this object.
• ... or a perl script using the "Spreadsheet::ParseExcel" module
• ... or a powershell script using "comobject Excel.Application"
• Install OpenOffice and program a small macro in it's OpenOffice basic language. Then create a VBS or DOS script that opens OpenOffice and calls the macro with parameters, one being the source XLS, and one being the target CSV.
• Without installing anything on the server running SSIS: write a small C# application that utilizes the Excel OLEDB data provider built into Windows to read data from the Excel sheet. Then write the results to a CSV file.
• ... or write a small C# application that utilizes the opensource Excel Data Reader library to read data from the Excel sheet. Then write the results to a CSV file. This library appears to be buggy however.
• Freeware commandline tool: XLS2CSV by Zoom Technology. Parses only the first sheet. The column separator and text quotes are configurable throught an ini file
• Freeware commandline tool: XLS2CSV by Tom Crow. Parses all sheets to separate CSV files

ODBC/DSN configuration can be found here:

• 32 bits ODBC connections: %windir%\syswow64\odbcad32.exe
• 64 bits ODBC connections: %windir%\system32\odbcad32.exe

Notes:

• ADO also uses the OLEDB library.
• The OLEDB library is a piece of code that gets loaded and compiled into the calling application at runtime.
• If you use the MADE 2010 Redistributable package, then be aware of a small bug when connecting to Office 2010. You have to use "MICROSOFT.ACE.OLEDB.12.0" as the driver name in stead of the specified "MICROSOFT.ACE.OLEDB.14.0".
• Although previous Office versions need to be de-installed before installing MADE 2010, they can be reinstalled afterwards.

Increase maximum volume of the iPad

If you find that the maximum volume on the iPad is not loud enough, then you can fix that with the following adjustment:

• Start Cydia
• If you haven't installed OpenSSH, then search for it and install the app, restart the iPad
• Install WinSCP on your Windows environment and make a connection to the iPad. The iPad IP adress can be found on the iPad in Settings -> Wi-Fi. Click on the blue arrow on your current wifi connection. By default the username is "root" and the password "alpine".
• Download and install iPodRobot for Windows
• Open a connection to the iPad via WinSCP
• Copy the following file to your Windows environment: System/Library/PrivateFrameworks/Celestial.framework/RegionalVolumeLimits.plist
• Just in case anything goes wrong: make a copy of this file on your Windows environment
• Start iPodRobot and load RegionalVolumeLimits.plist
• Find and replace 0.xx000000000000004 with 1, where x=79 or 84, or some other number under 100
• Save the file and copy it back to the iPad using WinSCP
• Reboot the iPad.

The volume will be much louder, so tune it down before starting any music or video on the iPad.

Note:

• The iPad needs to be jailbroken, i.e. needs to have Cydia installed
• The Cydia/Winterboard apps Volume Boost X3 and Volume Boost 4.0 have no effect on the iPad.
  

Alternatively, using iTunes, you can boost the volume of each individual video/song:

• Start iTunes
• Select all songs under iPad -> Music
• Right click and select "Get info"
• Tab "options", drag the slider to +100%

The song volumes will be adjusted song by song. This can take a long time!

Keywords: setting the maximum volume limit on iPod

Internet on iPad via adhoc wifi hotspot

Scroll down for the solution, read on for some trial-and-error background info.

When no direct wifi internet connection is available, then it is possible to use the internet connection of wired Windows PC. The only requirement is that the PC needs to also have a wireless (wifi) network card.

It is possible to use internet connection sharing (ICS) on Windows to setup an adhoc wireless network and connect to this network from the iPad:

• Control Panel -> Network and sharing center -> Setup a new network or connection -> Set up wireless adhoc (computer to computer) network -> Next
• Give the network a name
• Security type: WPA2-Personal
• Give the network a password (security key) of at least 8 characters
• Click "Turn on internet connection sharing"
• Select the LAN internet connection when asked "Select the internet connection that you want to share"
• iPad -> Settings -> Wi-Fi -> Select the network, type in the password

I couldn't get this to work properly. I tried all of the following:

• "Turn on internet connection sharing" does not appear. Try a solution posted by sinnerman on windows7forums.com:
  
  • Open Network and Sharing Center
  • Go to Change Adapter Settings
  • Find the adapter for the default internet connection (it was a PPPOE in my case)
  • Right click on that -> Cancel as Default Connection
  • Right click -> Properties -> Sharing tab
  • Uncheck "Allow other network users to connect ..." if it is not
  • Setup the ad-hoc as explained above, the Turn on Internet connection sharing should appear.
• Disable Windows Firewall. If you have another one, for example Bitdefender, then there is/might be a direct option to allow ICS.
• Allow other users on internet connection:
• Control Panel -> Network and sharing center -> change adapter settings -> select internet connection -> Properties -> Sharing -> Check "Allow other network users to connect ..."
• Set a policy that allows you to use ICS: 
• Start gpedit.msc.
• Local Computer Policy -> Computer Configuration -> Administrative Templates -> Network -> Network Connection
• Set "Prohibit use of Internet Connection Sharing on your DNS domain network." to false

Solution

• Download Connectify. It is freeware
• Install and allow the software to install drivers
• Start Connectify and start easy setup wizard
• Give the network a name, next
• Give the network a password, next
• Select the connection (wired/LAN) through which there is an internet connection
• iPad -> Settings -> Wi-Fi -> Select the network, type in the password.

The connection from the iPad is lost when unused for a couple of minutes. When using, for example, Safari on the iPad, the connection is quickly restored via a popup that lets you select from wifi networks. Sometimes this is not even necessary and safari restores the connection automatically.

Bluetooth device not found on Dell laptop

If you are sure you have a built-in bluetooth device on your Dell laptop, but you cannot find it in control panel or device manager, then try these steps:

• Determine bluetooth device type using the Dell device locator
  or
  Go to http://support.dell.com and type in the search bar: "Find Out Which Bluetooth Adapter Is in Your Dell Computer"
  or
  Go to the webtool "system configuration tool" directly: system configuration tool
  In all cases, use the service tag that can be found on the bottom of the laptop
• Search for "bluetooth" on the page that the SCT returns, it should have code beside it, i.e. "370"
• Go to http://support.dell.com and type in the search bar: "bluetooth 370"
• The results page contains drivers for bluetooth for various Windows OS'es:
  - Look for "Dell Wireless 370 Bluetooth Minicard, v.XXX" and search for the most recent version XXX.
  - Make sure "Win 7" is in the description, if the driver is needed for Windows 7.
• Download and install the driver.

Notes:

• The SCT only works from Internet Explorer 5.5+
• And only from the 32 bits version of Internet Explorer. Make sure you don't use the 64 bits one.

CTAS disregards number precision and scale

When using a "create table as select" (CTAS) to create a table based on a query, when that query also contains a window function/analytical function in the SELECT-clause, results in the precision and scale being omitted.

The precision/scale are neglected, when running the following example in Oracle 11g:

CREATE OR REPLACE FORCE VIEW VSA_X AS
SELECT
CAST(1 AS NUMERIC(10,2)) AS FLD1,
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) AS RN
FROM DUAL;

DROP TABLE X;

CREATE TABLE X AS SELECT * FROM VSA_X;

Table X will have no precision/scale for FLD1. It is bypassed and will simply have NUMBER for its datatype. This is a bug in Oracle.

Note:

• Using NUMBER in stead of NUMERIC makes no difference, the bug is still there.
• Explicitly casting the ROW_NUMBER() function also makes no difference.

Solution:

Put the window function inside a derived table:

CREATE OR REPLACE FORCE VIEW VSA_X AS
SELECT
CAST(FLD1 AS NUMERIC(10,2)) AS FLD1,
RN
FROM
(
  SELECT
  1 AS FLD1,
  ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) AS RN
  FROM DUAL
) x;

Keywords: CTAS omits precision, CTAS omits number precision, CTAS bypasses precision, CTAS bypasses number precision.

Subtitle Workshop blank screen with Matroska/MKV containing H.264

Scroll down to solution for the workaround to get video in stead of a black screen. Read on for a little bit more background information.

There are five components to video in Windows: container (like avi, mkv), video format (divx, h.264), splitter, decoder, and video player.

Nice post to get started:
http://www.hack7mc.com/2009/02/mkvs-for-minimalists-on-windows-7.html

But the scripts could be more robust. E.g. change the fixed system root "c:\windows" to %systemroot%

SPLITTERS

Haali Media Splitter: http://haali.su/mkv
Note: since december 2009, this contains also the x64 version.

Gabest: hard to get, but is part in some packs.
x32: http://www.hack7mc.com/wp-content/plugins/download-monitor/download.php?id=12
x64: http://www.hack7mc.com/wp-content/plugins/download-monitor/download.php?id=11

Advice: Haali splitter.

DECODERS

ffdshow (the original, but outdated): http://sourceforge.net/projects/ffdshow/files
ffdshow tryouts: http://sourceforge.net/projects/ffdshow-tryout/files

Get a 64 bits or 32 bit ("generic") in the folder "official releases" or get a more recent nightly build from the folder "SVN builds by clsid".

Advice: a recent 32 bit nightly build.

SPLITTER/DECODER packs

K-Lite Codec Pack: http://www.digital-digest.com/software/K-Lite_Codec_Pack_64-Bit_history.html

Shark007: Install Win7Codecs first (http://www.majorgeeks.com/Win7codecs_d5959.html), then x64Components (http://www.majorgeeks.com/download.php?det=5535)

Shark's Win7Codecs comes with a tool to select and configure the various splitters and decoders for the various media types (divx/xvid, mpeg2, h264/mpeg4) and containers (avi, mkv).

Install and use the "settings32.exe" application to configure, since most video players are currently still 32 bit (winamp, zoomplayer)

Advice: Shark007 packs

VIDEO PLAYERS

Zoomplayer 5.02 Standard (latest free version), Windows Media Player (build-in, WMP Classic/Guliverkli2 or WMP Classic Home Cinema) and Winamp all work with the system splitters and decoders. VLC Player comes with its own splitters/decoders and does not use the ones installed on the system.

Subtitle Workshop v2.51 does only work with "real" avi containers. The "rename trick", where the mkv extension is changed to avi, will work with most of the video players, but unfortunately not with Subtitle Workshop.

The more recent betas of v4 do work with mkv/h.264 files, but those are not very stable. The splitter has to be Haali, Gabest's does not work - "cannot render media!". It does not matter if the "rename trick" is used. This also applies to Windows Media Player. Gabest also doesn't work with Subtitle Workshop v2.51.

Winamp doesn't really like mkv files; it almost always halts on startup.

Zoomplayer does not work well with video format "Xvid dev 25-Mar-03". You have to use Haali splitter for avi, because Gabest and Microsoft's cause a stack overflow.

Zoomplayer doesn't work with mkv/H.264 when the ffdshow codec is used.

CHECK TOOLS

Tools to check video containers, e.g. get video format or the the processing workflow (splitting, decoding):

GSpot: http://www.headbands.com/gspot

MediaInfo: http://sourceforge.net/projects/mediainfo/files

GraphStudio: http://blog.monogram.sk/janos/2008/12/13/monogram-graphstudio-0310
GraphEdit: http://www.digital-digest.com/software/download.php?sid=82&ssid=0&did=1

SOLUTION

To get the video preview to work in Subtitle Workshop 2.51 with mkv files, that contain the h.264 video format:

• Install both Shark007 packs mentioned above. Check the box to install ffdshow.
• To get the latest ffdshow version working with Shark007 (this step is not necessary):
• In \program files (x86)\win7codecs\filters: regsvr32 /u "ffdshow.ax". Uninstall this older version
• Download most recent 32 bit nightly build mentioned above and install with default settings
• Copy "ffdshow.ax" from the nightly build install folder to the \win7codecs\filters folder
• Start \program files (x86)\win7codecs\tools\settings32.exe
• Click "reset all" to get the recommended configuration. 
• To be sure, check the following settings:
  H264: use ffdshow's codec/ffdshow with multithreading
  MKV: use haali splitter
• Set H264 codec in directshow players to "use ffdshow's codec"
• Finally, install Vobsub 2.23

Notes:

• The final step is very important. You have to install Vobsub (or "vsfilter") as the final step, even if it is already installed. Just install it again. (VobSub is a directshow filter that overlays subtitles)
• Test mkv and avi video formats in Winamp and Zoomplayer. If they fail to start, then uninstall Vobsub 2.23 again. Use the subtitle option from ffdshow to have subtitles in those players. WMPC and VLC have their own subtitle overlay filters.
• The version has to be 2.23, more recent versions will not work. E.g. 2.39 does not work.
• Do not rename the container from .mkv to .avi. Subtitle Workshop 2.51 will load the .mkv extension without problems.

TNS service names ODBC dropdown box garbled, messed up, gibberish

Most likely cause is that there are two or more different Oracle clients installed, with two or more home folders and tnsnames.ora files.

For example, an Oracle 10 client is installed or Oracle XE is installed. Both contain a home folder with tnsnames.ora. Later, the Oracle 11g instant client + ODBC compontents is added. Now there are two home folders.

In this case, the TNS_ADMIN environment variable needs to exist:

• Create TNS_ADMIN variable via control panel -> system -> advanced -> environment variables -> system variables
• Set is to one of the admin folders, eg.:
  c:\oracle\ic_x32_11.2.0.1.0_basiclite\NETWORK\ADMIN
  Note: make sure is contains the tnsnames.ora, sqlnet.ora and listener.ora files.
• Create a ODBC connection using the Oracle driver and check if the "tns service name" dropdown box contains readable entries
• Just to be sure, replace the body of all other tnsnames.ora files with:
  IFILE=c:\oracle\ic_x32_11.2.0.1.0_basiclite\NETWORK\ADMIN

CDBurnerXP: drive not recognized

CDBurnerXP is a freeware tool for burning CD's and DVD's, including blueray. However, it sometimes does not recognize the drive to burn with.

First, check the FAQ on the CDBurnerXP website:
http://cdburnerxp.se/help/appendices/troubleshooting

In my specific case, I then did the following:

• Start CDBurnerXP in "audio mode"; the drive was recognized. Only in "data mode" it is not.
• Check the chipset. Download the freeware tool "system info": http://www.gtopala.com/siw-download.html.
• Extract and start it - it does not have to be installed; it is a standalone executable. Check hardware -> motherboard -> southbridge. Note the chipset.
• Download "intel matrix storage manager" for your chipset: IMSM.
• Install IMSM and restart.

Now, CDBurnerXP should work in "data mode".

Note: make sure the drive is on the "compatible drives" list: http://cdburnerxp.se/en/testeddrives

The nearby-future of storage

The ioDrive Duo by FusionIO out-performs even multiple SSD's placed in parallel. The 320GB version of the ioDrive has a read/write speed of ~1.5GB/s:

http://www.fusionio.com/products/iodriveduo

Difference with traditional SSD:

• The flash memory is integrated in the PCI card.
• The flash memory has been optimized using a proprietary technology.

Downside:

• Cannot be used as a boot drive.
• Requires a 64-bit operating system.
• The price is about 15,000 euro for 320GB version (ssdeurope.com). Ofcourse, this has to be compared against the cost of buying and operating a SAN.
  

SQL Server statistics

Statistics are information about the distribution of data in a column, maintained internally by the database engine.

Rules and behaviour with regard to statistics, when automatic creation/updating is turned on:

• Statistics are automatically created when creating an index, but only for the first column of the (composite) index.
• Statistics can be created manually on any column or combination of columns.
• Statistics are automatically created/updated when the query optimizer finds need for them.
• Statistics are not automatically created/updated when the server is too busy, unless the "update statistics asynchronously" option is turned on, because then the are created in parallel.
• Asynchronously created/updated statistics have effect the next time the (causing) query is run.

Links:
http://www.sql-server-performance.com/tips/update_statistics_p1.aspx
http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx
http://technet.microsoft.com/nl-nl/library/cc966419(en-us).aspx

Set cscript as default script engine

To set cscript as the default scripting engine on the system:

cscript //h:cscript //s

This way, the "wscript.echo" method always spools to the command line in stead of to message boxes. And without the need to start each script with the "cscript.exe" application.

Derived table vs subquery

There are several ways to combine datasets:

• Subquery: SELECT clause, correlation
• Join (cross/left/right/inner/full): FROM clause, no correlation
• Cross apply: FROM clause, correlation

Datasets retrieved in the FROM clause are called "derived tables".

The "cross apply" combines the best features of the subquery and join, but is performance-wise not always the best choice.

SQL Developer thick/OCI driver

The following error messages may appear when using SQL Developer to select data from a SQL Server table via a (heterogeneous or DG4ODBC) database link on an Oracle 11g DB:

Bigger type length than Maximum
OALL8 is in an inconsistent state

Solution: switch to the thick driver to work around this.

In SQL Developer: tools -> preferences -> database -> advanced -> use oci/thick driver = checked.

If this does not work or when getting the error messages:

no ocijdbc11 in java.library.path
vendor code 0

then:

- Download Oracle Instant Client 11.1.0.7.0 basic lite (easily found with Google, register with Oracle to download)

Note: version 11.2 does not work with SQL Developer!

Note: take the 64-bits version on a 64-bits OS, like Windows7 x64.

- Extract files to c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Add to environment variable PATH: c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Create a batch file called sd.bat in the rootfolder of the SQL Developer installation, with the following body:

set ORACLE_HOME="c:\ora\instantclient_x64_11.1.0.7.0_basiclite"
start sqldeveloper.exe

- Double-click sd.bat to start SQL Developer
- Add a connection with Connection type = basic

To make a connection with Connection type = TNS:

- Create folder c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Put these files in the admin folder:

sqlnet.ora
tnsnames.ora

- In SQL Developer: tools -> preferences -> database -> advanced -> tnsnames folder: c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Add a connection with Connection type = TNS

Note the following:

• The solution above even (or especially) works when another Oracle client is already installed. For example, if the OracleXE DB (which, at the time of writing, is 10g) is installed locally, then the Oracle Client 10 is also installed. But this client might not work when trying to connect to a remote Oracle 11g DB
• There is no need to set the environment variables ORACLE_HOME or TNS_ADMIN variable on the system. It does not matter what their values are
• The order of folders in the variable PATH does not matter
• Most likely Help -> About -> Properties -> oracle.home does not point to the correct Oracle home folder. So not to c:\ora\instantclient_x64_11.1.0.7.0_basiclite. It does not matter what the value is
• Unfortunately, the ORACLE_HOME trick does not work with the PATH variable; it is no use adding that to the batch file
• The error "Bigger type length than Maximum" is related to a bug in the implementation of the thin part of the JDBC driver. Not to heterogeneous services, Oracle DB, SQL Server, SQL Developer or the thick part of the JDBC driver. (Note: the same driver, e.g. ojdbc6.jar, contains the thin and the thick classes)

Oracle JDBC connections

The following JDBC drivers exist for Oracle:

• classes111.jar: Java 1.1
• classes12.jar: 1.2 and 1.3
• ojdbc14.jar: 1.4 and 1.5 (no JDBC3 and 4 features)
• ojdbc5.jar: 1.5
• ojdbc6.jar: 1.6

Java 1.5 = Java JRE/SE 5 and Java 1.6 = Java JRE/SE 6. Additional jar files are required for some features.

All the classes to support basic functionality for the Thin and OCI drivers are inside the same jar. In short, for simplification, the same file contains both the thin and the thick (=OCI) classes.

Joe Weinstein on forums.oracle.com:

The jar is all of the thin driver and the java portion of the OCI driver. In order to
run in OCI mode, you would use this same jar, giving it the OCI URL instead of the thin
URL, and you would have to have the whole Oracle client installed on your machine,
and your OS environment set up to point to the OCI DLLs as well as the standard C
libraries. Note that the version of the OCI libraries that works with one driver jar
may not work with another driver jar, so if you go the OCI route, you may have to
install a whole new OCI client just to get a particular driver fix.

Thick URL: jdbc:oracle:oci8:@<database_name>
The database_name is the Net Service name of a mapping in tnsnames.ora.

Thin URL: jdbc:oracle:thin:@<server>:1521:<database_name>
The database_name is the database name part of the database service on the (remote) server. E.g. Windows service "OracleServiceORCL", then the database_name is "ORCL".

Oracle released different versions for each driver, eg. 11.1.0.6.0 or 11.1.0.7.0.

To get the different versions, download the Oracle Instant Client of choice from the Oracle website and extract the ojdbc.jar files to one folder. Rename them using the version. This makes testing or switching easy:

ojdbc6_11gR1_11.1.0.6.0.jar
ojdbc6_11gR1_11.1.0.7.0.jar
ojdbc6_11gR1_11.2.0.1.0.jar
etc

Databases

Row-oriented:
Oracle DB
SQL Server
SQL Server CE
MySql: InnoDB
Sybase Advantage Database Server (ADS)
MS Access

Column-oriented:
Extreme-DB
InfiniDB Enterprise Edition
Infobright
MonetDB
ParAccel Analytic Database
Sybase IQ
Sybase Adaptive Server Enterprise (RDBMS & column-oriented)
Vectorwise
Vertica

Appliance:
Aster Data (MPP, MapReduce)
Greenplum (MPP, MapReduce)
Netezza (MPP)
Teradata (MPP)

In-process:
SQLite

No login prompt for website

Suppose you want to login with Internet Explorer to an intranet website, that uses Windows Authentication as the authentication mechanism. When you do this, a prompt is displayed, that requires you to enter the account's username and password.

This prompt can be skipped:

• Note the webserver name in the title bar of the prompt, e.g. wbsvr01.acme.lan
• Start the Credential Manager: Control Panel -> Credential Manager
• Add a windows credential:
  a. internet or network address: wbsvr01.acme.lan
  b. username: domain\usr
  c. password: pwd

Note: if the website is on the internet, the webserver name has to be added to the trusted sites of the intranet zone:

• Internet Explorer -> Tools -> Internet Options -> Security -> Select "Local Intranet" -> Sites -> Advanced
• Add this website to the zone: http://wbsvr01.acme.lan