Bens Blog

SQL Server, Subqueries and data types

2011-07-22T16:55:00.000-04:00

Today I had to deal with an issue on SQL Server 2005 that I thought might be worth sharing. Maybe someone else runs into a similar issue and can't figure out why SQL Server is playing stupid.

I was trying to run a query similar to the one below, it was generated by a reporting tool:

select sum(a11.COGS_ORDERED),

sum(a11.LINE_RECEIVED)

from
(SELECT
ID
, COGS_ORDERED

, QTY_SHORTED
, 'LINE_RECEIVED' =
CASE
WHEN QTY_SHORTED = 0 THEN 1
ELSE 0
END
FROM myTable a11)

SQL Server always returned the error message "Arithmetic overflow error converting expression to data type int." . I figured out that the issue has to do with my case statement so I tested to run a sum right on the case statement and it worked fine:

SELECT

SUM( CASE

WHEN QTY_SHORTED = 0 THEN 1
ELSE 0
END)
FROM myTable a11

After doing some research I found out that SQL Server stores the result of the subquery as int and is not able to convert it to bigint when doing the SUM. Forcing a convert already in the case statement fixes the issue:

select sum(a11.COGS_ORDERED),

sum(a11.LINE_RECEIVED)

from
(SELECT
ID
, COGS_ORDERED

, QTY_SHORTED
, 'LINE_RECEIVED' =
CASE
WHEN QTY_SHORTED = 0 THEN CONVERT(BIGINT,1)
ELSE CONVERT(BIGINT,0)
END
FROM myTable a11)

I think a database management system should be smart enough to do this conversion by itself.

Videos of new features in SQL Power Architect

2010-10-26T13:46:00.000-04:00

After I gave a short overview of the new features in Power Architect EE in my last blog post, I'll take the chance to post some videos that are available on YouTube and demonstrate the new features.

The trial version for SQL Power Architect comes with a 30 days free support in case you run into any issues: http://goo.gl/7SH1

New features in SQL Power Architect

2010-08-25T17:59:00.000-04:00

After several months of development SQL Power released a new version of the Power Architect which is now available in 2 different version, the "old" Community Edition (CE, also available on Google Code) and the brand new Enterprise Edition (EE). Today I want to give you an overview of the most exciting new functions in SQL Power Architect EE.

Central project repository & Security
In the Enterprise Edition all projects are stored on the server and you can create users and groups and give them different access rights to the projects depending on their role.

Data Types and Domains
This is one of the most important features in the new version. Prior to version 1.0 EE you could only work with a set of standard column types that all (most databases would understand). With Data Types and Domains you are finally able to define your own type and specify how this type should be forward engineered in the different database management systems. For example the type "Numeric" will be forward engineered to the type DECIMAL in HSQLDB but to NUMBER in Oracle.

Versioning
With version 1.0 EE you can easily revert changes you made in your project and compare different versions of your project.

Data Model Validation
With the new data model validation you don't have to wait until you forward engineer to see if you have any errors in your data model. You can just click on the validation button and you will see where you need to do some changes. You can also select the validation rules you want to use, a column name you are using might be valid in SQL Server but not in Oracle.

Real Time Collaboration
Unlike most versioning tools where you have checkout and check-in your project Power Architect EE allows real time collaboration. You work "live" on the server, all changes you make on a project are instantly stored at the server and you watch others working on the project.

There are more features that have been added or improved like the ETL mappings and the profiling results. I think it's worth giving it a try. With the trial version you get 30 days product support and if you decide to buy it before end of October you will get a 3 user licence for the price of a 1 user licence

Wabit finally supports Parameters!

2010-06-08T13:54:00.000-04:00

In the last days SQL Power released Wabit 1.3.1, you can download it here: SQL Power Download Website.

The most important change that came with Wabit 1.3 is the ability to parametrize reports. Below is a screenshot on how it looks like:

Report with parameters in SQL Power Wabit

You can choose a text entry field, date picker and a select box as parameter entry field. The values from a select box can be based on a query.

How to add parameters to your report:

Add parameters to your SQL query. You can add parameters either by using the parameter dialog (Ctrl + Space) or just by using the right syntax in the query, for example "continent =${Continent->Europe}".

${Continent} defines the parameters "Continent"
${Continent->Europe} defines the parameter "Continent" and sets the default value to "Europe"

Parameter in a query

Create a new Report

Drag the query into the report
Add a parameter in the parameter panel (Go to View->Docked Source List if you can't see the parameter panel) . Make sure the parameter has the same name as you defined in the query.

To add a text entry field add a free form text.
To add a select box add a drop down list.

If you want to use values from a query in your select box, add a query with the values first and then select the query using "Source" and "Pick a variable" in the parameter properties.

To add a date picker add a date selection widget.

You will see that the result changes depending on the parameter entered.

Using a query as a select box

This functionality is available in the open source version of Wabit. Give it a try and let me know what you think of it.

Open Source tool for Data cleansing and Master Data Management

2010-02-01T10:52:00.000-05:00

Last weekend SQL Power released an improved version of SQL Power DQguru (formerly known as SQL Power MatchMaker), one of the few open source tools for data cleansing and master data management (MDM) available. Version 0.96 brings a new feature that allows you to run SQL Power DQguru from command line. This allows you to integrate it into batch scripts and your ETL jobs.

As a BI consultant for SQL Power I have used SQL Power DQguru in different projects and it has made my job a lot easier. Some of the features I like the most are:

Easy connection to any database with JDBC drivers, incl. SQL Server, Oracle, MySQL, Postgres
Lets you create complex merge rules so your dependent data will always be updated when you merge records.
You can combine over 25 steps to find possible duplicate data with a match rule, for example:

Word Count
Regular Expressions
Substrings
Retain certain characters
Translate Words, you can create your own translation rules.

You can preview how your data will look like when you apply the match rules
Automatic Address correction (for Canadian addresses, Premium version)

Here is a example how a simple match rule could look like using some of the available steps:

Even the user interface is mostly straight forward, it might be useful to take advantage of the user guide which is available for a small fee. You will see SQL Power DQguru is very powerful if you know how to use it.

Using the Community Build Framework for Pentaho

2010-01-29T10:18:00.000-05:00

Recently I had to prepare a installation for the Pentaho BI Server (CE) and I decided to try the Community Build Framework (CBF) from Pedro Alves. I had to install the server on a test and a production environment so it seemed to fit perfectly for my requirements.

It is working fine now and helps a lot in applying changes to the installation having a clean structure but it took me quiet a few hours till I had it working (probably because I'm not an expert when it comes to using ant & Co.)

Here are some issues you should be aware of:

You'll need Java 1.6.
Make sure your path to ant, java but especially the project folder doesn't contain any spaces. Spaces will only cause problems.
Tomcat 6 is not supported yet.
I recommend setting the solution paths to the folder "C:/...../project-client/solution" until you figured out how CBF works in detail.

You will have your CBF ready to run a lot faster than I did if you keep these issues in mind. I'm sure I'll use CBF a lot more often in the future.

If need more information on how to setup your own Pentaho installation I highly recommend checking this website http://www.prashantraju.com/ (besides the Pentaho Wiki).

Stop annoying applications in your Facebook news feed

2010-01-22T11:39:00.000-05:00

With getting more and more friends on Facebook, I also get a whole bunch of them playing Mafia Wars, Farmville and all these other applications. And of course every day they have to share with me their latest score. I just found a good way how to stop getting these annoying updates in my news feed:

In the entry of the application in your news feed, click on the application name (highlighted in the screenshot). This will lead you to the application page.

On the application page you will see a link "Block Application" (Highlighted again). Click on it.

Facebook will ask you to confirm your decision. Confirm to block it.
Go back to your Facebook start page (news feed). All entries from this application will be gone!

SQL Power's Wabit - A feature overview

2009-12-10T12:05:00.003-05:00

Last week I had some time to create a short screencast to show some features of SQL Power's Wabit open source version. The video could be more professional (My headset didn't like me too much) but I was to busy with other projects to have more revisions. You are very welcome to share your ideas, critics and comments.

Here you go:

(Watch on Youtube: SQL Power's Wabit - Feature overview)

Real-Time Business Intelligence with Wabit & SQLstream

2009-10-19T20:21:00.004-04:00

The last week I got the chance to prepare a screencast of SQL Power's new real-time BI solution. It uses a SQLstream server as backend and Wabit as a BI reporting tool. Both the Open Source and the Enterprise Edition of Wabit can be used for it.

Here is the screencast (it's the best to watch it in full screen):

Comments are welcome!

UPDATE: The full offer is now available on SQL Power Real-Time BI solution.

Delta Generation with Kettle

2009-10-02T18:52:00.000-04:00

In one of my current project I have to do lots delta generation to figure out if any data changed and be able to work differently with the data depending if it's similar, new, changed, or deleted. I came up with the following transformation:

The file works as following

The current and old files are CSV inputs and both have the same format.
The "Merge Rows" does the main delta generation.
In "Filter rows" I take out the identical rows because they are not important to me.
Kettle uses long descriptions (deleted, new, updated) but I need I,U,D for my system to be compatible with another data source involved, so I map these values.
The delta finally gets saved in a text file and a table. One would be enough, but I put the text file in an archive in case I need it again. The table gets truncated every time the transformation runs and is used for the data load in the next step.

Do you have questions or do you see any possible improvements? Feel free to post your comments!

Products you don't expect to be 'Made in China' - Del Monte fruit cups

2009-09-23T08:42:00.000-04:00

Since I moved to Canada back in March I have started to realize how many products are actually made in China. Back in Germany you could also buy lots of stuff from China but you mostly had the choice between German or Europe products and Chinese products.
When I went to Food Basics in Oakville a couple weeks ago to get some apples I stood in front of a huge tray of Chinese apples! Aren't there enough apples in Ontario, Canada or the US? Even Mexico would probably be closer than China.
Another day my wife bought Del Monte fruit cups in the grocery store. I checked the label when I was going to eat it and i decided to leave it in the fridge. First of all it is 'Made in China' (again I guess no other country in this world has fruit) and second it contains artificial flavor. How bad must the fruit inside be that you need artificial flavor (and does anybody in China controls how it is made)?
For my part I'll check the labels more closely whenever I buy any kind of product, especially when it comes to food. My health but also the economy of our country (and the other western states) is too important too me to ruin them with something thats just a few cents cheaper.

(This blog entry will hopefully become part of a small series on 'Made in China' related subjects.)

My first impression of the Palo Worksheet Server 3

2009-09-17T17:34:00.000-04:00

Recently I got a change to download Palo Worksheet Server 3, I was planning to build a small test case and impress some people at work with what Palo can do. But I'm not sure if I should really show too much of the Worksheet Server during my presentation after I started playing with it a bit more. Here is why:

Pros:

The frontend of Worksheet Server looks nice.
The Charts and Micro Charts look nice.
Many people won't think of Palo as a full BI tool if it doesn't provide it's own frontend

Cons:

Why did Jedox release a software that is so unstable? I'm always worried to do too much in a worksheet because I don't know whats gonna happen next. Will i get lots of "value?" Will my session end and I loose my data? (Just happened)
If you are used to Excel & Google Docs you get impatient, some of the context menus are too slow.

After working with it for half a day I wouldn't let a client create his own reports, the frustration level would probably be too high. Am I alone with that opinion?

September issue of OSBR on Business Intelligence

2009-09-01T11:19:00.000-04:00

This morning the September issue of the Open Source Business Resource (OSBR) - a monthly publication of the Talent First Network got released. It is all on Open Source Business Intelligence.
Have a look: OSBR September 2009 - Business Intelligence

What is the OSBR?

The Open Source Business Resource (OSBR) is a free monthly publication of the Talent First Network. The OSBR is for Canadian business owners, company executives and employees, directors of open source foundations, leaders of open source projects, open source groups, individuals and organizations that contribute to open source projects, academics and students interested in open source, technology transfer professionals, and government employees who promote wealth creation through innovation.

[Quote from the OSBR website]

Wabit - Open Source BI - New features in version 0.98

2009-09-01T09:33:00.000-04:00

SQL Power Group, based in Toronto, Canada, released version 0.98 of it's open source business intelligence reporting tool Wabit. Here is a overview of the newest (and improved) features:

General

Improved UI
Most item are drag able
Multiple workspaces can be open at the same time
Search in workspaces

Reports

Report templates available
Extended grouping options

Chart

New more intuitive UI to create charts
Pie chart support added

OLAP queries

Filter added
Improved UI
More OLAP navigation features added (Drill replace etc.)

One of the features I like the most is the new grouping and sections setting in reports as you can see in the screenshot:

There are still many improvements possible but 0.98 brought Wabit a lot closer to all the other open source reporting tools available.
What feature do you think should be added next?

Creating Mondrian Star Schemas with Power*Architect - Part 1/2

2009-08-25T15:27:00.000-04:00

A few months ago I first mentioned that you can create Mondrian schemas with Power*Architect. Finally I get the time to create a step by step guide for a small Mondrian schema. This post covers the preparation of Power*Architect to create a new schema, the coming blog post will cover the schema creation and testing.

What you need to get started:

Nightly build from Power*Architect [EDIT: The latest stable version should do it too.]
A database, e.g. MySQL or PostgreSQL with the data you want to analyze. In this example I'll use the Pentaho sample data which is included in the Pentaho BI Server 3.x
Wabit 0.97 (includes Mondrian built in)
or a running Mondrian instance and a Mondrian client of your choice

Step 1 - Setting up the database connection

Start Power*Architect
Open the Connections menu and start the Database Connection Manager.
Click on New.
Add the database connection details. For the Pentaho sample data enter the data as shown in the screenshot (Click on it for a larger screenshot). Make sure you enter the right folder name.

Test the connection and click on Ok.
Close the Database Connection Manager.
Open the Connections menu -> Add source connection and select the created connection

Step 2 - Visualize the data model (Optional)

If you don't know the data model, I recommend using Power*Architect to visualize it. This step is not needed for the creation of Mondrian schemas.

Unfold the database connection and drag&drop the needed tables to the right hand PlayPen. In my example I use the tables orders, customers, dim_time and orderfact.
Add the relationships between the tables: Click on (Non identifying relationship), on the key in the dimension table and then on the related column in the fact table.
Add the end the model should look similar to the following screenshot.

You can find more details on how to use Power*Architect in the help and manual.

The second part tomorrow will cover the actual schema creation and testing. Comments are welcome.

Creating Mondrian Star Schemas with Power*Architect - Part 2/2

2009-08-24T19:00:00.001-04:00

This is part two of my step by step guide on creating a Mondrian Schema with Power*Architect.

Step 3 - Creating the Mondrian schema

Go to OLAP-> Edit Schema -> New Schema.
Enter a Schema name and select the database connection you created earlier.
Dimensions

Right click on the PlayPen, select New Dimension, and click on the PlayPen again
Enter the dimension name, select TimeDimension as the dimension type and click on ok.
Right click on the created dimension and add a hierarchy. Select the time table (public.dim_time) and select the correct primary key.
Now you can add levels. Right click on the hierarchy and select Add level. Enter the name, column and correct level type.
The procedure is the same for all dimensions.

Cube

Right click on the PlayPen, select New Cube, and click on the PlayPen again
Enter the Cube name and select the fact table (public.orderfact in my example)
Add the dimensions to the cube. Click on (Dimension Usage), then on dimension and finally on the cube. (This procedure is supposed to get easier in a later release of Power Architect).
Enter a caption and select correct foreign key out of the fact table.
Follow the instructions for the other dimensions.

Measures

To add measures to your cube, right click on the cube name, select the aggregator and the correct column from your fact table.

Your schema should look similar to the following screenshot.

Export the schema using the last icon (or by right clicking in the schema) to your hard drive.
Save the project and close Power*Architect

Step 4 - Testing the Mondrian schema

Install and start Wabit.
Create a new Workspace with a new OLAP database connection
Select "In-process Mondrian-Server", the database connection you already created in Power*Architect, and select your Mondrian Schema you exported earlier. You can see an example in the screenshot

Click on "Start"
Wabit automatically starts in the query mode.
Select the created cube in the right hand bar. All measures and Dimensions will be listed. underneath.
Drag&Drop the measures and dimensions into the OLAP query editor and see the results. Your result should look similar to mine (I'm using a slightly different version of Wabit).

Step 5 - Troubleshooting

If you run into problems check the following:

HSQLDB doesn't like multiple connections too much. Make sure you have only one open connection.
If you can't create a query with Wabit, load the Schema into the Mondrian Schema Workbench and check if it's working there. Maybe you missed something when you created the schema.
Can't fix it? Write a comment or use the SQL Power forum.

Fifty years of business intelligence

2009-08-21T11:09:00.000-04:00

I just found an interesting article on Peter Luhn's article he wrote more than 50 years ago. Many people think Howard Dresner is the father of BI, but they are wrong.

Here is the article: BI at 50 Turns Back to the Future

Open Source BI - Wabit now reports against Mondrian (and other XMLA sources)

2009-08-19T14:03:00.003-04:00

The open source business intelligence reporting tool Wabit added support for Mondrian in it's newest release 0.97. Users can now either connect to the integrated Mondrian server or to any available XMLA datasource.

In the OLAP query, measures and dimensions can easily dragged into the query which automatically updates the results. It is the first version including the OLAP functionality so there are still some features missing, including filters, calculated members, saving of manually created MDX queries but Wabit already shows some nice results.

With it's upcoming release 0.98 - scheduled for September 09 - most missing OLAP features will be added and Wabit should be a nice AND feature rich reporting application for Mondrian and all other OLAP engines supporting XMLA.

Dynamic cell references in spreadsheets with Google Docs

2009-08-11T16:09:00.001-04:00

During my former internships in a consulting company I had to work A LOT with Microsoft Excel and often had to use dynamic cell references over multiple worksheets. Recently I started using the spreadsheets of Google Docs to track my bank account balance and to figure out where all my money goes. I decided to have one sheet for every month + one sheet of the month I want to analyze. But how do I dynamically change the reference to the sheet (the monthly sheet) I want to analyze without editing every single formula? Here is my solution:

Create the target sheets and your overview sheet
- I gave my sheets the names Month + Year (July 09)
In the overview choose one cell that you want to contain the reference sheet and enter the sheet name
- cell D24 in the example
- Using the month names you might have to write 'July 09, otherwise Google will think it's a date.
In the overview you can now dynamically reference to a detail sheet using the following formula:=INDIRECT("'"&&"'"&"!O2")
- In my example =INDIRECT("'"&D24&"'"&"!O2")
- For more details on the formula syntax read the Google Docs Help.

Try out my example. I wish the income would be real ;-)

Neues aus Oakville

2009-06-23T08:24:00.004-04:00

Endlich komme ich wieder dazu, etwas in meinem Blog zu schreiben. Seit der Hochzeit und Hochzeitsreise sind schon einige Tage vergangen, wir haben hier einige Bilder davon. Von unserem Fotographen gibt es inzwischen auch erste Bilder, er hat einen hervoragenden Job gemacht.

Was gibt es sonst neues zu berichten? Sarah und ich haben uns an die langen Wege zur Arbeit und die dadurch meist nicht so langen Abende gewoehnt Der Sommer kommt so ganz langsam auch nach Kanada, sodass man die Klimaanlage zumindest nachts ganz gut gebrauchen kann. Wir arbeiten uns auch ganz langsam durch die Dankeskarten zur Hochzeit, aber das wird sicher noch ein paar Wochen dauern, bis es da etwas zum verschicken gibt. Ich habe auch eine lange Liste von Personen, die ich gerne anrufen oder wenigstens etwas schreiben moechte, aber ich komme einfach nicht dazu. Praktisch die einzige Moeglichkeit jemanden anzurufen ist morgens (aufgrund der Zeitverschiebung), aber da muss ich schon um 6 Uhr aufstehen um rechtzeitig auf der Arbeit zu sein. Ich werde mich aber bemuehen die Liste nicht laenger werden zu lassen.

Mondrian Schema design with Metadata Editor and Power*Architect

2009-05-02T08:18:00.006-04:00

After not working with Mondrian for a few months I had to design a schema for Mondrian again. As usual I first used the Schema Workbench. U can do everything with it but the user interface is a pain I think, especially if you are used to the other Open Source BI tools that are around for other tasks.
I remembered that at least version 2 of the Pentaho Metadata Editor has a hidden Schema Editor, you have to press CTRL-ALT-O to activate it. If you check out the newest version (Version 3 RC1) you will be surprised how much you can do with it already! After building your relational model in the Metadata editor you can quickly design your cube and reuse your relational model which can save you a lot of time. But remember, it's not supported by Pentaho yet and you have to copy and paste your schema to test it. I'm sure in the near future Pentaho will have a solution for it.
Another open source tool I found (I started working for the company recently) is Power*Architect from SQLPower. Power*Architect is mainly a data modelling and profiling tool but it also comes with a small schema designer, as you can see in the screenshot.
So far Power*Architect doesn't come with Mondrian included so you'll have to copy your schema to another tool first if you want to test and upload it.
If you know any other tools and ways to create to easily create a schema feel free to comment.

Click Fraud Detection

2009-04-29T22:04:00.002-04:00

Mal etwas das nicht mit Kanada zu tun hat: Im Forschungsbericht der Hochschule Karlsruhe wurde ein Artikel zum Them Click Fraud Detection veröffentlicht, den einige Studenten und ich erstellt haben. Ein Interview mit dem betreuenden Professor Karl Dübon kann man sich im Radiomagazin der Hochschule anhören: Sendung vom 7. und 14. April.

Wieviele Wochen? Ein kurzes Update

2009-04-25T11:03:00.003-04:00

Diese Woche kommt mein Blogeintrag mit etwas Verspätung da ich in unserer neuen Wohnung noch kein Internet habe.
Neue Wohnung? Ja, nachdem ich letzte Woche eine Jobzusage in Toronto bekommen habe, sind Sarah und ich direkt auf Wohnungssuche gegangen und sind in Oakville fündig geworden. Die Wohnung ist 5-10 Gehminuten vom Bahnhof (Go Train) entfernt, sodass ich ohne Auto zur Arbeit fahren kann. Ein paar Bilder gibt es in meinen Alben auf Picasa.
Mit der Arbeit und der Wohnung hat mich auch der Alltag wieder etwas mehr eingeholt. Morgens um 6 Uhr aufstehen, 8-9 Stunden arbeiten, da genießt man die Abende und Wochenenden anders als bisher. Aber die Arbeit bei meinem neuen Arbeitgeber ist interessant und spannend und ich habe lauter gute Kollegen.
So, das war es wieder für diese Woche, ich hoffe nächstes Wochenende komme ich wieder dazu etwas zu schreiben.

Kanada ist ist nicht das Paradies - und trotzdem schön

2009-04-13T11:03:00.002-04:00

Morgen werden es nun schon vier Wochen, die ich in Kanada bin. Wenn man weiss, dass man nun für immer hier ist, sieht man Kanada schon kritischer als es die meisten Touristen sehen. Zum Beispiel Niagara Falls: Im Urlaub sieht man die schönen Wasserfälle, Casinos und Hotels. Es lohnt sich aber auch mal ein Blick hinter die Kulissen, die vielen Gebäude die herunterkommen sind, die Verarmung in manchen Vierteln und man sollte bedenken, dass viele der (saisonalen) Angstellten für CAD 9,50 die Stunde arbeiten, ohne Lohnfortzahlung im Krankheitsfall, ohne Krankenversicherung (abgesehen von der staatlichen Mindestversicherung), ohne bezahlten Urlaub, fast täglich kündbar.
Ein anderes Beispiel: In Deutschland jammern Studenten für die ca. 1200 Euro die sie im Jahr fürs Studium zahlen müssen, hier kostet das Jahr gerne mal CAD 5.000 und mehr. Schüler fangen hier häufig mit 14 Jahren an, neben der Schule zu arbeiten, um sich später College und Uni leisten zu können, bzw. um am Ende nicht allzutief in den Schulden zu stecken.
Ich möchte Kanada nicht schlechtreden, man sollte nur die Realität nicht aus den Augen verlieren.

Zu den persönlichen Ereignissen in der letzten Woche: Ich hatte nochmal ein Bewerbungsgespräch, war auf einer Jobmesse und hatte nun zwei Jobangebote. Heute morgen habe ich mich nun entschieden: Ab 20.04 fange ich in Toronto meine Stelle an. Ich bin so froh und dankbar, dass bisher alles so gut geklappt hat, Visum, Umzug, Auto, Versicherung, Job.... Als nächstes ist jetzt die Wohnungssuche angesagt, wir werden uns voraussichtlich was in Burlington / Oakville suchen. Hoffentlich klappt das auch so gut, bisher wurde unser Vertrauen nicht entäuscht.

3 Wochen in Kanada

2009-04-08T16:51:00.001-04:00

So nach 3 Wochen in Kanada ist es mal wieder Zeit für ein kurzes Update.
In den letzten Tagen habe ich mich weiterhin fleißig beworben und 4 Vorstellungsgespräche und eine Jobbörse hinter mich gebracht. Mindestens 2 der Vorstellungsgespräche verliefen positiv, sodass ich gute Chancen habe, bis Anfang nächsten Monats einen Job zu haben.

Ansonsten läuft alles nach Plan, die Hochzeitsvorbereitungen laufen bisher gut, in 51 Tagen wirds ernst. Das Wetter ist richtig typisches Aprilwetter, am Samstag waren es ca. 15 Grad und sonnig, am Montag abend hat es geschneit. Das Bild habe ich am Dienstag morgen gemacht.

Schon eine Woche in Kanada

2009-03-25T13:54:00.002-04:00

Mal wieder ein kleine Update...

Gestern war ich auf der Jobmesse, war ganz interessant zu anzusehen, allerdings kaum passende Unternehmen vor Ort. Es wurden meist Leute für den Verkauf gesucht (3-4 Banken & Versicherungen, Bell,...). Bei ner IT-Staffing Company musste man ein paar Minuten in der Schlange stehen nur um sein Lebenslauf abgeben zu dürfen.
Am Freitag habe ich ein Telefoninterview mit einer Firma aus Toronto und außerdem kommt demnächst ein Vorstellungsgespräch in Niagara Falls. Mal schauen was die nächsten Wochen bringen.

Bei der Autoversicherung gabs auch ne gute Überaschung. Bisher war nur meine Verlobte versichert. Gestern konnte sie mich als Secondary Driver ohne zusätzliche Kosten eintragen lassen. Die Versicherung wollte nichtmal den Auszug aus dem Verkehrsregister in Flensburg haben. In der Regel soll wohl die Versicherung für Frauen günstiger, der männliche Fahrer sollte dann als Zweitfahrer angemeldet werden.

Die Hochzeitsvorbereitungen gedeihen auch gut. Gestern habe ich den Anzug gekauft, am Freitag lässt sich Sarah ihr Kleid anpassen. Es müssen immer mehr Details geplant werden...

Als nächstes brauche ich jetzt ein Handy. Bin noch am überlegen ob ich mich mit nem Prepaid Handy rumschlagen soll oder einen Vertrag abschließen soll. Bei nem Vertrag wirds wohl entweder Bell oder Koodo werden.

Das wars mal wieder aus dem schönen Ontario

Kanada - Tag 3

2009-03-20T17:04:00.003-04:00

Nachdem ich am Dienstag Abend in Toronto angekommen bin, sind jetzt schon drei Tage vergangen in denen sich auch ein bisschen was getan hat und ich mich soweit sehr gut einleben konnte.
Am Dienstag konnte ich direkt am Flughafen ohne große Wartezeit mein Work&Travel Visum holen, mit dem ich jetzt ein Jahr ohne große Einschränkungen in Kanada arbeiten kann. Am Mittwoch habe ich meine Sozialversicherungsnummer beantragt und versucht, meinen deutschen Führerschein gegen einen Kanadischen umzutauschen, was leider nicht auf Anhieb ging. Dafür musste ich gestern meinen Führerschein zum Übersetzer bringen. Heute konnte ich ihn wieder abholen und habe jetzt meine vorübergehenden kanadischen Führerschein.
Mit unserem Auto bin ich inzwischen auch vertraut und es macht richtig Spaß bei gemächlichem Tempo (schnell fahren darf man leider nicht) durch die Gegend zu fahren und das schöne Wetter zu genießen.
Somit sind eigentlich alle wichtigen Vorbereitungen abgeschlossen und ich kann mich ganz darauf konzentrieren einen Job zu suchen und mich schlau zu machen, ob und wie ich mit einer temporären Arbeitserlaubnis selbständig machen kann.
Bei meinen Bildern gibt es übrigens auch schon erste Bilder aus Kanada. Ich werde versuchen diese von Zeit zu Zeit zu ergänzen.

12 days left - update on immigration process

2009-03-05T02:49:00.003-05:00

After not writing much lately I think it's getting time to give a short update about how my immigration process is doing.

I received my one year work permit already in December and I'm still waiting to become a permanent resident. I applied a couple months ago and at least since January they are working on it in the canadian embassy in Berlin. According to their website it can take over a year for them to decide, so I better don't expect an answer the next weeks and months.

During the last few weeks I started applying at some companies, but answering emails isn't a strengths of most of them. I hope they serve their customers better. I usually call them about a week after, but often you end up in their phone systems without a chance getting the right person. It might be a bit easier when I actually live in Canada and can drive by to hand the resumes out to the right people. We'll see. At least we have a car already, Sarah bought a 2001 Chevrolet Cavalier last month. It's a former police car and seems to be in good shape.

End of February I finished my studies with handing out my master's thesis to my prof and all I have to do now is waiting for my diploma. For the last days I have been working on our wedding invitiations and wedding website and I also have been trying to solve all existing issues with the websites, computers and network at my brothers company. I'm looking forward not to do these basic jobs like installing computers and software anymore soon.

Thanks Deutsche Bahn AG! Customer service, what is that?

2009-02-13T10:53:00.003-05:00

Last month I wrote already about my adventures with Deutsche Bahn AG, the german railway company. They made my fiancée missing her flight, spending the day at the airport and spending a night in the hotel, it cost over 250 Euros.
The customer service from Deutsche Bahn at the airport told me I should send them all my receipts and they would give me the money back. Thats what I did almost 4 weeks ago and today I got a letter back saying they only pay 20% of the cost of the ticket we were delayed with, so I got a gift certificate worth ONLY 11 Euros. I don't need a gift certificate, I'm moving to Canada next month and with 11 Euros I can't even get a one way ticket to the next city.
I was really upset and called them right away, after discussing and complaining what a crappy service that is and how much I spent because of them, the lady at phone finally offered me another gift certificate worth 20 Euros. I wonder if I'll ever get it. But it still doesn't help me at all, I'm sitting now on gift certifcates worth 31 Euros for something I'll never use again.

I can only recommend to NEVER use the german railway (Deutsche Bahn AG) if you have a appointment or flight you have to be in time for. Go to one of the car rental companies get a cheap car, it's not more expensive, you can drive 200 km/h at the autoband and you'll be not much slower but you won't have to deal with that kind of what they call customer service.

Connect Facebook & (almost) everything from Google

2009-01-30T07:35:00.005-05:00

Yesterday I tried to figure out how to post my blog posts on my Facebook profile. First I tried different application but all they could do was adding a box on your profile. After searching for on google for a couple minutes I found one video on youtube that lead me the way: Import Blog/RSS in Facebook profile.

You can do it even easier as in the video. When you are on your profile in Facebook, you just have to click on Import (as shown in the picture) and you can import not only a RSS feed from your blog, but also integrate lots of different websites like Youtube, Google Reader and most important for me: Picasa.

Finally you can use all the different great tools from Google and integrate them all into one Profile.

UPDATE: Finally there is also a blog post in the offical blog from blogger about the topic: Blogger Buzz: Facebook Your Blog

Formulare mit Google / Google Chrome

2009-01-29T05:58:00.005-05:00

Gerade habe ich mir die Formulare von Google Text & Tabellen angeschaut und bin richtig beeindruckt!
Innerhalb von 2 Minuten konnte ich meine eigene Umfrage erstellen, die ich nun in E-Mails, Webseiten verwenden kann oder direkt über Google ausfüllen kann. Das Ergebnis wird in einer Tabelle gespeichert. Ein kleines Beispiel gibt es direkt hier:

Mit Sicherheit werde ich in Zukunft mehr damit arbeiten.

Google Chrome habe ich mir diese Woche zum ausprobieren installiert, von der Geschwindigkeit bin ich richtig erschrocken, so schnell hat sich Google Mail noch nie bei mir geöffnet. Natürlich fehlen dem Broswer noch ein paar Features aber in einigen Monaten wird er bei mir wohl Firefox ablösen.

How to miss a flight (and spend a day at the airport)

2009-01-12T04:24:00.004-05:00

Last week on wednesday my fiancée had to fly back from Frankfurt to Toronto with the 10am flight with Air Canada. To be able to spend more time together we decided to take the train from Freiburg at 5:54am instead of going by car. Usually the train arrives at 8am which gives you plenty of time to check in and go to the gate - but not this time. A few minutes before we would of arrived at the Airport there was an announcement that they have problems with the second part of the train and we had to stop at a very small train station. First the made everyone from the second part of the train to the first part. After trying to disconnect the second part there was a second announcement that it wouldn't work and everyone should go out of the train (it was about -10 degrees outside) and wait for the next train that should stop in about 10 minutes. It was the first time in my life that I had to go out of a train jumping out of the train since the platform was ways too short for the train.
After standing in the freezing cold for about 10 minutes the next ICE arrived. It was booked out already so it took a while for everyone to fit in. We were just going to start again when there was an anouncment again, this time they called for a doctor or medic, someone had a stroke. It took at least 30 minutes till they would bring the person away and we could start again. We ended up arriving at the Frankfurt Airport with about 100 minutes delay on a 120 minutes drive, of course my fiancée missed her flight.
At the ticket-counter from Air Canada we found out that we weren't the only ones missing the flight. Because of bad weather other people missed there connection flights too so the next flight at 5pm was overbooked already and they could only give Sarah only a stand-by boarding pass for it so we had to wait and sit around at the airport for a couple hours. At the gate at 4pm Sarah found out that there were about 25 other people on the waiting list, trying to get one of the 3 free seats. Of course it turned out she wouldn't get one. Going back the couting we got served from the same person helping us already in the morning and again she could put Sarah only on a waiting list.
So we picked up the luggage again, stayed over night in a cheap hotel and went back to the airport again at 7am. At least this time we where lucky and after talking to different people they could give us a seat and Sarah could fly to Toronto. Whenever I went to take the train home it was cancelled - how ironic.

PS: I decided to write future blog post in english. It is too confusing if I keep switching between languages and it helps me practicing my english.

Work permit & flight

2008-12-19T17:40:00.001-05:00

Last week I received one of the best letters in my life so far - beside the ones from Sarah of course ;-) : My one year work permit called Work & Travel 2009! With this permit I can work for one year from the moment I first arrive in Canada in March.

Yesterday I also booked my flight to move to Canada. It will on March 17th so it's less than 3 months till I'll have to say "Good bye Germany"...

The picture has nothing to do with this post. I just felt like posting it, I took it in Switzerland this summer.

What is my thesis all about

2008-12-15T03:07:00.002-05:00

The last weekend I discussed in a forum what my thesis is about. I spent some time in the answer, so why not posting it in my blog to give people a better idea what I'm working on:

When users first see Palo and Pentaho, they don't really know there is such a big difference, both claim to be a BI suite. It was the same for me, when I first started my thesis.
In my thesis I'm working with Palo OLAP Server, Palo Worksheet Server and also with Palo ETL Server compared to the Pentaho BI Suite (without Weka), otherwise it wouldn't be a good comparison. I created a test scenario, and try to provide a solution with both Palo and Pentaho.

The focus of my thesis is not to say, Palo is good, Pentaho isn't or the other way around. the result might be "if you focus on planning, you better use Palo, if you do lots of reporting, Pentaho provides more options,...

The scenario is more the practical side of an implementation: How to get data into the system (ETL), how can you design your data (Modeller, Schema Workbench, Metadata Editor,...), how can you create reports, especially parameterized and time related ones, how can you manage users and groups and their rights, do the tools provide planning posibilities. Are there special features? Since there is so much stuff to look at I can't always go into all the details of course.

Pentaho BI Server: Using action sequences as a web service with PHP

2008-12-04T02:46:00.007-05:00

For my masterthesis I had to figure out, how to use the action sequences as webservice with PHP. According to the documentation you can receive soap messages but the action sequences don't offer a WSDL that would help you building your client. I also had problems with the http basic authentication, that Pentaho uses.
After a couple hours of research and try and error, I found a solution. I doubt thats the best way to go, but at least it works. All you need is the PEAR HTTP Request class.
Here is the code:

//PEAR Request
require_once 'Request.php';
$response = $req->sendRequest();

if (PEAR::isError($response)) {
echo $response->getMessage();
} else {
$req->clearPostData();
$req->setURL("localhost:8080/pentaho/ServiceAction");
$req->addQueryString("solution", "bi-developers");
$req->addQueryString("path", "reporting");
$req->addQueryString("action", "Testreport.xaction");

$response2 = $req->sendRequest();
//$xml_string now has the SOAP message
$xml_string = $req->getResponseBody();
}

The variable $xml_string contains the SOAP message.

If anonye has a better solutions to use Pentaho action sequences as a webservice in PHP is very welcome to post a comment.
I'm also looking for a good solution, how to handle the results in the SOAP message the easiest way.

Palo User Conference 2008

2008-11-10T16:14:00.005-05:00

UPDATE: Nach neusten Infos scheint das Release der neuesten Version des Palo OLAP Servers auf Januar 2009 verschoben.

Am Donnerstag & Freitag durfte ich dank einer Einladung der Jedox AG auf der Palo User Conference und dem Partner Tag sein, wo die neue Version des Palo OLAP Servers und der neue Palo Worksheet Server vorgestellt wurde.
Während es beim OLAP Server hauptsächlich Verbesserungen in der Perfomance (unter anderen dank Multiprozessorunterstützung) gibt, hat sich beim Worksheetserver enorm viel getan. Der Server wurde komplett neu entwickelt (die Core in C++) und das Look & Feel lässt einen fast vergessen, dass es sich um eine Webanwendung handelt und nicht um Excel handelt. In der neuen Version kann man sowohl die Modellierung der Palo OLAP Datenbank als auch die Reporterstellung durchgängig im Server durchführen, sodass Excel gar nicht mehr benötigt wird. Auch ansonsten wurde sich bemüht, alle relevanten Funktion von Excel zu übernehmen, von der Zellenformatierung bis zu einer Skriptsprache in Anlehnung an VBA (!), jedoch in PHP realisiert. Man wird die Möglichkeit haben, die Elemente auf dem Spreadsheet mit PHP in einer Syntax aufzurufen, wie man sie von VBA gewöhnt ist. Das Beste am Palo Worksheet Server ist, dass er wie auch der Palo OLAP Server zu großen Teilen Open Source sein wird.
Etwas Negatives zum Palo OLAP Server muss aber trotz aller Begeisterung angemerkt werden. So scheint es, dass das Excel-Addin nicht weiterentwickelt wurde und die Modellierung großer Dimensionen damit weiterhin sehr unübersichtlich bleiben wird (z.B. das Verschieben von Elementen). Auch eine Extra Zeitdimension wie man sie von SAP BI aber auch von Mondrian kennt, wird von Palo nicht unterstützt. Dadurch bleibt das Reporting über Zeiträume (z.B. 04.05.2008 - 09.05.2008) nur schwer mit Palo realisierbar.
Als weitere Software wurde noch der Palo Supervision Server, Palo ETL-Server sowie der vielversprchende ETL-Client von Tensegrity vorgestellt.

Voraussichtliche Releasetermine:
- Palo OLAP Server 3.0: Ende November 2008
- Palo Worksheet Server 3.0.1: 1. Quartal 2009

Zurück aus Kanada

2008-11-10T15:53:00.003-05:00

Seit einer Woche bin ich nun wieder von meinem 10-tägigen Aufenthalt in Kanada zurück. Sarah & ich konnten viele schöne Stunden zusammen verbringen und einiges erledigen, insbesondere die Hochzeitsplanungen weiter voranbringen. Der Besuch war übrigens der letzte. Wenn ich das nächste mal nach Kanada gehe, dann ist es kein Besuch mehr sondern mein Umzug. Die Vorfreude ist groß, auch wenn das zurücklassen meiner Familie, Freunde, Gemeinde, Heimat und vielem mehr auch gemischte Gefühle hervorruft.

Bis es soweit ist, muss aber noch einiges erledigt werden: Bei meine Masterthesis ist Ende des Monats Halbzeit und es gibt noch viel zu testen und zu schreiben. Für die Arbeitserlaubnis muss bis Ende des Monats der Antrag des Work & Travel Visas fertiggestellt werden und solangsam muss ich auch den Wohnungs- und Stellenmarkt in Kanada sondieren.
Von kanadischen Einwanderungsbehöre haben wir Anfang der Woche: Der Antrag fürs Sponsoring wurden akzeptiert und die Unterlagen weiter an die kanadische Botschaft in Berlin geleitet, wo der Antrag für die dauerhafte Aufenthaltserlaubnis bearbeitet wird. Mal schauen wie viel Zeit die sich lassen werden...
PS: Die Bilder sind in Beamsville, Ontario entstanden.

Stand der Auswanderung

2008-10-04T15:40:00.004-04:00

Da ich immer wieder gefragt werde, wann ich denn auswandern werde und wie das alles funktioniert, werde ich versuchen die Auswanderungsprozedur und den aktuellen Zeitplan hier im Blog immer mal wieder etwas zu erklären.
Aufgrund meines Studiums bin ich noch bis Ende Februar 2009 an Deutschland gebunden. Danach werde ich dann meine Sachen packen und hoffentlich bis Ende März nach Kanada ziehen. Auswandern nach Kanada sieht im Fernsehen zwar meist so leicht aus und da ich von meiner Verlobten gesponsort werde habe ich gegenüber vielen anderen vielleicht einen kleinen Vorteil aber das ganze Vorhaben kostet einiges an Nerven, Zeit, Geduld und Geld.
Fange ich mal mit der Zeit und den Nerven an. Zuerst einmal dauert es eine Weile bis man herausgefunden hat, welche Möglichkeiten es gibt nach Kanada einzuwandern, welche Bedingungen und Anforderungen daran geknüpft sind und welche Vor- bzw. Nachteile es dabei gibt. Sehr hilfreich sind dabei die Seiten der Kanadischen Einwanderungsbehörde, auf der man auch die ganzen notwendigen Formulare runterladen kann. Wenn man fremde Hilfe braucht, kann man sich entweder an Auswanderungs(bzw.Einwanderungs-)berater wenden, mit hat bisher das Kanada Forum sehr geholfen. Nachdem ich rausgefunden habe, dass die wohl beste Möglichkeit für mich ist, als sog. Conjugal Partner (ein Art getrennt lebender Partner) in der Family Class (eine der Einwanderungsklassen) meinen Einwanderungseintrag zu stellen, ging das ausfüllen der Formulare und das sammeln von Dokumenten los, Es hat sich leider ziemlich in die Länge gezogen (teilweise mein Fehler). Vor ca. 2 Wochen haben wir endlich alles einschicken können, sodass jetzt erstmal für ca. 1 1/2 Monate warten angesagt ist, bis wir eine Antwort bekommen.
Geld ist der nächste Faktor, den man bei der Einwanderung berücksichtigen muss. Nicht dass man für den Anfang in Kanada Reserve braucht, auch die Einwanderungsgebühren sind zu berücksichtigen. Bisher haben wir ca. € 1.000 zahlen müssen (Einwanderungsgebühr, Arzt, Übersetzungen).
Geduld und Ausdauer sind bei der Auswanderung ebenfalls sehr wichtig. Die Einarbeitung in die Dokumente, die teils widersprüchlichen Informationen in Foren können schon die ersten Nerven kosten, aber vermutlich war das noch der einfachere Teil. Nämlich jetzt kommt das Warten und die Unsicherheit ob die Einwanderungspapiere so akzeptiert werden, ob die Unterlagen ausreichend waren und den Bearbeitern die Erklärungen und ausgereicht haben. Laut offiziellen Angaben können da je nach Art der Einwanderungsklasse Monate bis Jahre vergehen bis man eine Zu- oder Absage hat.

Das soll fürs Erste mal zu dem Thema reichen. In nächster Zeit werde ich sicher mehr dazu und auch zum Work & Travel Programm 2009 schreiben.

Blog gestartet

2008-10-04T15:17:00.003-04:00

Nachdem ich schon länger meine Website vernachlässigt habe, da es mir einfach zu aufwändig war die regelmäßig zu pflegen, habe ich mich jetzt entschieden einen eigenen Blog zu starten auf dem ich hin und wieder schnell mal etwas posten kann.
Ich werde sicher nicht häufig was schreiben. Aktuelle Themen wie meine Masterthesis oder die anstehende Auswanderung nach Kanada, werden mir aber sicher immer wieder mal die Gelegenheit geben, diesen Blog zu füllen.