BI review & help

If you want to join fact and dimension table based on non-equality operator, such as between statement or <= and >=, you can't do it using simple join. It will throw you the error "All operators must be equality". To do it, you must use the complex join (the one with the yellow button).

From time to time I find myself writing a hierarchial query in Oracle DB. It helps me a lot when I need to understand hierarchial data, mostly resides in the source systems of the Datawarehouse. Yesterday I've seen that a lot of my colleagues haven't heard about these kind of queries, even the most experienced ones. So here is a tiny tutorial for this subject which you can go back to any time. The important thing here is that you'll know that these kind of queries exists even if you'll google it the next time you'll need it (search for "start with" or "connect by").
Let's say you have a table which looks like this:

Key	Father	Description
1	10	Renault
2	20	Jambo
3	20	Airbus
4	10	Hyundai
5	10	Toyota
6	10	Honda
7	20	F16
8	20	F15
9	10	Alpha Romeo
10	-1	Cars
20	-1	Airplanes
-1	-99	Transportation

You want to see the data in a tree form, meaning that you'll see Renault, its parent, its grandparent and so on. All you need to write is the following query:

select * from MyTable
start with KEY = 1
connect by prior FATHER = KEY

That's it. The result is:

Key	Father	Description
1	10	Renault
10	-1	Cars
-1	-99	Transportation

Note that the recursion stops only when it cannot find the next parent. In addition, many times you'll want only one row from the tree (the root, its son or some other criteria). In this case you can simply add a where clause to the query.
Unfortunately, doing the same with SQL Server is much more difficult (tell me if I'm wrong).

Enjoy.

SQL Server 2008 R2 has been released not-so-long-ago. You can download the trial and check it for yourselves. Alternatively, you can enter the digital tour or read the free e-book. I started reading the e-book and I promise to update here about the new features of R2.

 

"Tony Stark once saved his heart - and his life - with the Iron Man suit. Now he's found his heart and forged new life into his company. Stark Industries, by abandoning munitions manufacture for cutting-edge communications. Learn how Oracle helped with the transformation"

Have they lost their minds? Don't they have better ways to increase their revenues?

At the end of the month the Israeli SQL & BI conference will be held. The conference will last two days and there will be number of seminars, some of them will cover Microsoft BI issues. You can see here the list of the seminars. Luckily, I give away a free ticket and you, dear reader, can have it. All you have to do is to answer the following question by leaving a comment here and maybe you'll get a ticket. And here's the answer:

During 2007-2008, Microsoft made two important acquisitions. Two of the new features in SQL Server 2008 R2 are based on these acquisitions. Which are those acquisitions and what are those new features?

Your comments won't show here but don't worry, I can see them.

Good luck.

I just opened a new blog in the Israeli Microsoft blogs site. The blog will be written in Hebrew and it will be focused on the israeli BI scene. If you're from Israel or know reading Hebrew, you're welcome to visit or the subscribe to the RSS feed.