Star-Join Optimisation – Prerequisites

A colleague of mine asked recently: Do we need to have foreign keys between our fact tables and dim tables in order to take advantage of the new SQL Server 2008 star-join optimisation. I decided to ask the question at the MSDN Forums. Just now I got a reply and I thought it may be good to share with everyone:

Charles Wang – MSFT

To use star join, indexes are required on tables. FK constraints are not necessary, but it is recommended that you have FK constraints defined since without it SQL Server must depend on heuristics to detect star schema query patterns. There are some restrictions for heuristics to determine a start join query. And it may not pick up a correct start join query plan under some cases. You can find the detailed information in the section “Star Join Heuristics” of this article, Data Warehouse Query Performance. The following is an extraction of the content:

Many physical designs for data warehouses follow the star schema but do not completely specify the relationships between the fact and dimension tables, as mentioned earlier for foreign key constraints, for instance. Without the foreign key constraints explicitly specified, SQL Server must depend on heuristics to detect star schema query patterns. The following heuristics are applied to detect star join query patterns

1. The largest of the tables participating in the n-ary join is considered the fact table. There are additional restrictions on the minimum size of the fact table. For instance, if even the largest table is not beyond a specific size, the n-ary join is not considered a star join.

2.All join conditions of the binary joins in a star join query have to be single column equality predicates. The joins have to be inner joins. While this might sound restrictive, it covers the vast majority of joins between the fact table and dimension tables on the surrogate key in typical star schemas. If a join has a more complex join condition that doesn’t fit the pattern described above, the join is excluded from the star join. A five-way join, for example, can lead to a three-way star join (with two additional joins later on), if two of the joins have more complex join predicates.

There we go – so we need indexes, while FK constraints would be good to have but not necessary. I also wrote to the CSS SQL Server Team, so if they come back with more information, I will update this post.

Advertisements

Obtaining Microsoft BI Certification

I have been a little busy recently with getting certified with Microsoft. I am quite happy to announce I have passed both 70-448 and 70-452 exams in the last couple of weeks without reading a single page of preparation material. Now I am MCP, MCTS and MCITP in SQL Server 2008 BI.

I have always wondered how important and relevant these certificates are. The common opinion around the industry professionals seems to be that experience is what really counts and certifications are for people without experience who are trying to get into the profession. I did it the other way and I am not sorry the least bit for it. An overview of my experience in regards to the two certifications:

MCTS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
Exam: 70-448 Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

I passed this one with 857 (out of 1000). Since it was my first attempt at certification I did go through a few sample questions from a prep book based on MeasureUp, and a friend also flicked my way some really poor quality Braindumps, which contained some quite poor terminology and a fair bit of nonsense. On the MeasureUp tests I scored between 65-85%, so I decided to get a Free Second Shot voucher from Prometric and just go with no further preparation. As expected, my Data Mining skills did not quite cover the expectations and I had a few glitches around SSIS maintenance, but as expected I got more than 90% on all the development components and around 80% on all the administration/maintenance ones. After all I am a developer and I am not that experienced with administering BI solutions. So in general, my impression was that the test can be passed without much preparation from fairly inexperienced developers, who read some preparation materials.

MCITP: Business Intelligence Developer 2008
Exam: 70-452 PRO Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008

This one was harder, but I passed it with 92% with doing one sample test of 30ish questions before the attempt. I had another Prometric Free Second Shot voucher, so I was not stressing if I would pass or fail. The test had double the amount of questions than the MCTS one and it took me a fir while to go through all of them. Again – Data Mining was my weakest part (less than 50% right), while I managed to score 100% on SSRS, SSAS and “Designing the BI Architecture” part. SSIS was almost perfect too. Now, if a fairly inexpreienced developer passes MCTS, this test will definitely cause a lot more headaches. The questions are much more practical and much higher degree of relevant experience would be required to pass (or much more reading).

In general, both of the tests are not easy and I do believe that if one can pass them without preparation (from experience), he is quite prepared to tackle the design, implementation and administration of Microsoft BI solutions. The Free Second Shot vouchers are also great for eliminating stress to some degree. However, on the not-so-good side, the tests can be passed with no experience and because the questions do follow a certain pattern and do not allow for much freedom in choosing the correct answer (short answer ones could be better), I think that there is a moderate chance of inexperienced and not that knowledgeable people to study, pass and then immediately forget the subject matter.

Still, I have no idea how much my career will benefit from these certifications, however I did get a few PDF certificates signed by Steven A. Ballmer to wave at sutiable occasions 🙂

Reporting Services 2008 Layout Properties Glitch

Today while working with a basic sample dashboard in SSRS I noticed that for some reason one of my subreports disappeared: 

 
While it got rendered on its own in Report Manager, when I placed it within a subreport (with no other containers around it) it just refused to show up. One of the properties I tried was the Layout menu item on the subreport:
 
 

To my surprise, selecting the “Bring To Front” option solved my problem: 

 

This one was also hard to understand since it rendered correctly in BIDS, leading me to think that there is something wrong with my browser or report server setup. It seems like report items can be sent behind the report background, which at a first glance seems like a confusing and redundant bit of functionality. I also unsucessfully tried to reproduce this in SQL 2005, so it must be a new “feature”. Also, it happened without any action on my part – I did not edit any layout options prior to its disappearance. 

EDIT (15/01/2010)
The issue appears only with a subreport containing a Map. Interestingly, the Map actions – I have a Go To URL on the states still work, but the actual image gets lost somehow. 

EDIT (21/01/2010)
Microsoft replied with the following: 

Posted by Microsoft on 20/01/2010 at 11:25 AM
Thank you for reporting this issue. It only happens if the Map is the first report item on the Subreport and there are no Data Sets specified. You can work around this problem by placing the Map into a Rectangle report item.

Check your spelling in SSAS 2008 MDX queries

I just found out that because of wrong/different spelling of a dimension name/attribute in SSAS 2008 the server may return the following message:
The set must have a single hierarchy to be used with the complement operator.

To fix the issue, we must ensure that all the dimensions in our queries are spelled exactly the way they are named in the cube (surprise?)…

I encountered this message by first building a Reporting Services report with data coming from SSAS. Then, a colleague misspelt a dimension name and instead of Natural Code he typed Natrual Code. Next time I needed to run the query in Query Designer I got the message. The query indeed used a complement operator on that dimension, exculding a particular account by its Natural Code. The query was something like:

SELECT {
[Measures].[Amount]
} ON COLULMNS,
{
([Account].[Major Account].ALLMEMBERS *
[Employee].[Employee Name].ALLMEMBERS)
} ON ROWS
FROM (
SELECT (-{[Account].[Natural Code].&[990]}) ON COLUMNS
FROM [Finance]
)

Changes in SQL Server 2008 sysadmin group

There are some noteworthy changes in the way SQL Server 2008 handles security; apart from the single major improvement – the replacement of the Surface Area Configuration tool by Policy-Based Management.

One thing that surprised me today was that even though I was a Domain Admin and a member of the local Administrators group, SQL Server 2008 refused to let me log in. A login had to be explicitly created so I could access the instance. After some research, I found out that in SQL Server 2008 the local Windows administrators do not get mapped to the sysadmin role. Therefore, it is possible to get locked out of a server instance if there are no sysadmins on it. This is a feature, which separates more clearly SQL Server admins and Windows admins.

A further note on this topic. I would have not lost a small SQL Server war on a Dev environment recently if we were using SQL Server 2008 instead of SQL Server 2005. Now, being a Domain Admin does not necessarily win the battle for SQL Server permissions.

There is a TechNet page describing SQL Server 2008 Security Changes for further reference.

And another one, helping in case all system administrators are locked out.