BI review & help

I was asked how to get to SSIS log to see how much time took for the package to run.
Well, that depends.
On Development:
When developing new package, after running the process (click on the green arrow or press F5) there's a new tab called Progress. Clicking it will show you everything about the package's execution, including the time it started and the time it finished.

On Production:
When developing the package, open the SSIS menu (Yes, there is a menu called as the product's name. Microsoft...) and click on Logging... There, you can define logs for your package. You can log in many ways: Writing to SQL sever, output file, XML file and more. I recommend logging into SQL server and logging only the big and "hard" parts in your data flow. In the Details tab, pick up only the exceptional events, such as onError, onTaskFailed and onWarning. If you wish to know how much time took for you package to run, also pick up onProgress.

Follow this link to read about every event in SSIS.

I won't cover here the topic of Exception handling in MDX, but show you a funny thing that I have never seen in any computer language. Consider this MDX code:

iif (1.0e+40 * 1.0e+40 = (1/0), "Overflowed", "Didn't Overflow")*

On some processors, this code will output "Overflowed". That's because this multiplication will overflow and (1/0) also overflows, so what we have here is two "overflow values" that are equal.

Where on earth have you seen something like this???

---

* Taken from the book "MDX Solutions" second edition, p. 136

I'm almost done with my exams, so my writing can continue.

This post is not about how to customize your Dashboard (well, not only about it). Its purpose is to say it loud: Customize your Dashboard!
When the executives of your company (Yeah, I guess you work in a company. Does someone building Bi Portal for himself?) see the customized gauges with their company logo on it, they'll love it. No matter what these gauges will show them, you got their attention and their sympathy for the Dashboards site you made. Now, everything is easier. The bosses are in your hands.

For the Panorama NovaView users:

1. Follow this link to learn how to do this.
2. Do NOT start working before you backup your E-BI/KPI folder !
3. I recommend using Notepad++ or another good XML editor when writing in the XML files. Otherwise, you can mix the whole file and you'll have to start all over again.

A good example of how to not use Ajax.

Few months ago, Blizzard Entertainment announced that Starcraft 2 is on the way and they opened a web site to make sales promotions. In the sections where you see details about units and buildings in the new game, clicking on a unit will change 90% of the page with Ajax (no refresh in browser). The problem is that many heavy pictures and flash are loaded using this method and this makes the browser freezing for a while. I tried it with different browsers and different computers but it won't help. The browser always freezes.

Think twice when designing Ajax-enabled web page. Downloading too much data with Ajax can perform troubles. And troubled users won't come back to your web site.

We've been working for a while to enable SSO in our Panorama's Dashboard site. In a matter of fact, the responsibility for this was under the skilled hands of our system team. After a short time they succeeded and SSO was established in our site. We saw it when we entered the site: Instead of login page we directly entered the dashboard page.
After a few days, when I entered into the settings section of the dashboard site, I saw this:

Yes, that's right. No security at all. This is why we entered directly to the dashboard page instead of the login page...
The system team claims that they never said that the SSO succeeded and we say they did. No one will prove he's right, so there's no one to blame. But blaming is not everything. The important thing here is to learn for the next time: When you think you got a feature - check it. Things not always as they seems to be.

While reading the first chapter of the book "MDX Solutions With MS SQL Server Analysis Services 2005 And Hyperion Essbase", I wrote down some important notes, especially for the MDX beginners. Even if you're experienced user, check this out. You may find something useful.

• If you were a code programmer in your past, you can relax: MDX don't care about capitalization.
• Don't even try to skip an axis: It's impossible and it is meaningless. Use the predefined names for the axis, such as: columns, rows, pages, etc.
• You're new to MDX and the whole OLAP gives you a headache? Try to imagine this as a hypercube. It can help you a lot.
• When writing large queries, pay attention to the "readability" of your MDX. Use the Monospace fonts whenever possible.
• Do NOT think of SQL when learning or working with MDX. Although the syntaxes may look alike, these languages are totally different when you get to know them.
• .Members will give you all regular members. .AllMembers will also include calculated members.
• An expression like [Time].Members won't work if the Time dimension has multiple hierarchies.
• The asterisk (*) can replace the CrossJoin function. It may improve readability of the code.
• When using Order() function, you can specify a sorting criteria which is not shown in the result grid.
  

For some reason (and don't ask me why), the Panorama NovaView's documentation doesn't contain any information about some of the most important settings. Here some of them, hoping that this will help many users:

• In the Panorama Web Access web site, by default a user can  save his views (after he made his modifications) only in his private book. To enable him save his views in the Briefing Book (which means - the public book), do the following: Inside the Panorama server, open the Registry Editor (Start -> Run -> regedit) and go to the path HKEY_LOCAL_MACHINE\Software\Panorama\Nova View 5\Admin. Add a new string value named "PublicBookAdmin". As its value, enter all the users you want to give them the option to save their views in the Briefing Book by this template: <User1Domain>\<User1Name>,<User2Domain>\<User2Name>, etc. For example: panoramaDevServer\PowerUser, MSHOME\Miky.
• The subscriptions web part will show you only the views that you registered to, but by default no one can register himself to the views. What you need to do is to open the Registry Editor in the panorama server, go to the same path as mentioned above and add a string value named "ShowSubsAndAlerts" with the value 1. After that, every user will be able to right click on any view in the Panorama Web Access web site, click on Register and it will be added to the subscriptions web part for him.
  
• For some users, the loading animations which are shown before every applet appears in the Dashboard website will stay forever, meaning that the user will never see the dashboard itself. I think this has something to do with the Java or Microsoft VM of the user. Anyway, a nice workaround is to cancel this animation. To do this, enter the path C:\Program Files\Panorama\E-BI\Dashboard\include (replace the beginning if you installed to panorama software in a different location) and inside the Config.asp file, change the constant "ShowAnimationWhileLoadingApplets" value to false. This is a good workaroung because anyway, the applets should appear in a second or two. Otherwise - buy a faster server.
  

As I go on working with Panorama I'll write some more tips & tricks. Stay Tuned.

When designing a dimension in Analysis Services, there's a funny button called Add Business Intelligence. Clicking it opens a beautiful wizard which letting you define some basic properties of the dimension such as ordering and enabling writeback. I'll take the writeback feature as an example: When doing this using the wizard, it's taking you through many screen where all you have to do except for clicking next, next is to check a checkbox in one of the screens. That's it. After that, all this long wizard doing it setting a property called WriteEnabled to True. I think that it's a strange software design of Microsoft. Maybe it's for making the product seem more professional. You know - Add Business Intelligence sounds like a heavy operation. Anyway, I don't know what they had in mind.

I never thought that I'll do a commercial to Microsoft, but Project Real is a great thing that they did and they should get the credit for it. This project is a full end-to-end BI solution, including ETLs (using SSIS), Analysis Services cubes and mining modules, Reporting Services reports, end-user Panorama views and more.

We work with Panorama as our main GUI tool to show our users the cube's data as tables, charts, dashboards, etc, so this project is really helping us to learn how to implement our project from the first ETL step all the way to the last Panorama step.

Recommended.

Two days ago I talked with a friend about our feelings at work. I told him that many times I have the feeling that people just don't work. He asked me how I can see that, and I told him that they can't concentrate when they all going to have coffee or talking to each other every minute. "So, what you want them to do, sing: Work, Work, Work! all day?", he said. Then I told him this short story:

Last week I went to say hello to my friends in a very serious (and therefore successful) programming team. There were three guys (Doron, Yossi and Roy) coding there. I love challenges, so I tried to go to the other side of the room (and this team has a big room) unseen. Guess what - I made it. I went to the other side of the room and no-one saw me. They were all very concentrated on their code. I could steal everything I wanted, but you know - friends...

"That's how should people work", I told my friend. Still, I don't think that everyone can do this all the time, but I expect every worker to behave that way at least an hour or two in a day, and I promise you that their production will greatly grow.

The original idea of the Shabbat Candles was lighting the Shabbat's evening dinner. Lightning in both ways - physically and spiritually. Some times I think that the material reason in not relevant anymore. Everyone has lights in their houses, so why the candles?

Until the last Shabbat. The lights went out in the middle of the evening's dinner. My wife was mad at me because I adjusted the Shabbat clock to shut down the light too early. The candles saved our dinner (and my skin...).
So maybe the candles are not so irrelevant after all.

This may look as a simple task but it isn't, especially when I can't connect to the Internet because I'm working on an intranet which is not conneted to the web.
The most simple way is to look for an application that does that (I've been told that there are some) but again - I don't have access to the web.

An interesting workaround I found is to save the document as a complete web site (not only plain html). This will create a directory with all the images and stuff with the original html file. You can find all the images you need in this directory.

Important Note: When saving, go to the Tools menu (Save As -> Tools -> Web Options -> Pictures) and there increase the resolution of the saves images, if needed.
This tip is relevant for Word 97 and further.

knowing the rules doesn't mean you know how to play.
A great post in the Panorama blog with a riddle in MDX. No knowledge in MDX is required for this riddle, because they teach you what you need to know to solve this riddle.
Have fun. Believe me - you will.

Update: Look at Mosha Pasumansky's blog for another review at this MDX riddle. He claims that the answer in Panorama's Blog is not complete. While I'm new to MDX, I understand that their answer is not 100% complete, but I think it's enought in order to make their point.

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...