BI review & help

In the data source view, when you edit the SQL by yourself (right click on the table in the data source view, Replace table -> With new named query) be careful when using Oracle DB tables. When writing in the SQL the Oracle's table name with small casing, the SQL parser will add commas to the table name, making the SQL not work because the Oracle does not recognize this table name (with the commas).

Solution: Enter the table name with big casing, which will make the parser leave the table name as is. Also, remember: when creating or editing a named query always check and syntax, but also run the query and check that you get the desirable result before hitting the OK and saving the new named query.

by the way, I wonder: what were we doing if the Oracle was case sensitive?

Few months ago we were given an assignment to copy/move all our DTSs that were running with SQL Server 2000 to the new SQL Server 2005 Integration Services (SSIS). My friend Michael did it and wrote some important notes that he discovered when building ETLs with Integration Services. I decided to list them here because they are important and useful, especially for those who haven't got the time to develop with SSIS so far.

• One of the greatest improvements in SSIS is that between the source and the destination of the ETL process you can do many things, such as making new fields, sorting, converting data types, union all between different sources, implement your logic on a field, and much more. This is much easier than ever because all you need to do is to add a block to the data flow task and define it for your purposes.
• SSIS ships with a tool for migrating SQL Server 2000 DTSs. Do not use this tool. Sometimes the result of the conversion is not good enough and in all cases you can't edit the new migrated data task.
• When making a connection to a non-Microsoft DB, such as Oracle, use OLE DB client instead of the out-of-fashion ODBC.
• When the destination field is shorter (string type) than the source, add a data conversion block and cut the string. Otherwise, there will be an annoying warning even if the truncation is wanted.
• In many times (when working with non-Microsoft providers) the automatic recognition of the length & types of the source fields is not correct. Enter the source block and edit these properties by yourself.
• When moving a Unicode field (data type DT_WSTR) to a non-Unicode destination field (DT_STR), a data conversion block is required.
• SQL Server 2000 Stored Procedures will work in SSIS, but the Linked Servers definitions are problematic. Consider another options rather than using linked servers.
• When the source/destination is a CSV file, use Flat File Connection. But if it is an Excel file (.xls), use a Microsoft Jet OLE DB connection and define the source as OLE DB Source (yes, it will work with Excel files).

Again, thanks for Michael for making and sharing these notes.

For some weeks we were fighting with (or against?) the Panorama software in order to make it work right and show a nice pilot of BI dashboard screen to our managers. After three weeks we managed to show a good opening position by building a nice dashboard screen including a map, graphs, gauges and crosstabs.

I found that the installation of the SQL Server and the Panorama server was not good enough in my company, so I decided to try it for myself. The installation of the SQL Server is quite easy (Next, Next, Next ...), but installing the Panorama server is a complicated process. Paying attention to so many small details, knowing and remembering what to do inside the Windows server, IIS, Windows services, Windows registry and more is not so easy. Finally, after two days I managed to do this. Now I know that some things in the installation in my work place were not so good and I can point them out.

You can see the exciting (for me, at least) result in this picture, as it is a nice dashboard taken from my screen. Many posts about Panorama and SQL Server 2005 will come ahead. Now I can relax - Panorama is on the way...

I'm now starting to work and learn the world of BI.

My company has just bought Panorama's NovaView, which is a BI tool that focus on the UI level. this tool shows beautiful dashboards, including metrics, charts, cubes and more. I think that it worth looking - it's just beautiful.

So, as a beginning: What is BI? the google definition ("define:BI") says: "Technologies that help companies make better business decisions". I think it summarize it up quite well, but I can add one more thing: Technologies that make/help managers and decision-makers to see, or to understand much better what they have in their hands, what have they done so far and what can they do with their resources in the future.

What I'm learning right now is Data Warehousing, the Panorama tools, MDX and some other stuff.

Wish me good luck.

As I have said in the introduction, I recently left the .Net department in my company. We worked on a very interesting GIS application, using ESRI's ArcGIS line of products.
In the day before our deadline, I've found a really serious but also foolish bug: In many places in our product, the date looked like this: 13/41/07, 20/56/07, and so on.
When I've seen that I immediately knew what happened. Instead of formatting our DateTime data as dd/MM/yy, we used dd/mm/yy. As you probably know, "mm" is the minutes and "MM" is the month, so the dates had the minutes instead of the month.
That was a foolish mistake which caused us to reopen the solution and running many tests all over again. bummer.

What have I learned from this?

1. Always remember the DRY (Don't Repeat Yourself) principle (from the pragmatic programmer). If our DateTime format was stored in one place, the change could be much more simple.
2. Tests have to be more specific: "Check that all fields are OK" is not enought. Many programmers checked these pages/modules and nobody found this bug.
3. I believe that code review should had cover this bug. The code review must be in high quality.
   

Hi,
My name is Miky Schreiber.
I'm a programmer in a big computer company in Israel.
Recently I been moved from a .Net department to BI (Business Intelligence) department in my comapny.
In this blog I'll try to cover some interesting BI stuff.
Along with that, I'll write about everything else that interesting me, such as:

• Jewish life: Halacha (jewish way of life), Jewish wisdom and more.
• Politics, specially in Israel
• Technology, web and computers.
• Philosophy and life
• Everything else that comes around

Enjoy your reading.