Reporting Services
If you haven’t heard yet, CTP 3, which is a public beta of SQL Server 2008 has now been released and can be downloaded at
the Connect website. After a bit of unscientific research, here’s my top 5 BI features in SQL Server 2008. It’s ordered in my priority order. It’s important to note that some of these features have not been introduced in CTP 3 yet but will be coming soon.
- No more IIS requirement for SSRS! – Finally, there’s not the overhead of having to install IIS as part of the popular reporting tool.
- CDC and Merge – While this is a database engine improvement, it will tremendously help your BI effort in SSIS. The ability to do Change Data Capture on a SQL Server table will help warehousing scenarios and reduce your load time by a factor of 9x.
- SSRS scalability – The reporting engine has been significantly improved to scale to million row reports. Also introduced is a best of both worlds table and matrix style report called tablix.
- SSIS thread scheduler improvements – In some early testing, the thread improvements have netted a 20-70% improvement in data flow performance. The main improvement is going to be with a long list of synchronous transforms.
- Authoring Reports in Excel and Word – Thanks to some smart licensing of Soft Artisians’ product suites, you’ll now be able to author a report in Word or Excel. I would expect this to be a pretty big blow to competing reporting platforms since users will be able to write reports in where they spend most their time in.
-- Brian Knight
We're coming up on one year in business and that's cause for a minor celebration! Most of you have probably heard that a high percentage of startups fail within their first year - we're pleased to have made it over that hurdle. We've learned a lot of lessons and probably still have bunch more to go, but it's been fun, challenging, and occasionally intimidating. If you ever get the chance to go solo, try it! You'll be out of your comfort zone and it may not succeed, but it's still a great growth opportunity.
Free Hotel Stay or Disney in June
But back to the celebration. Sign up for the June 11-14 Performance Tuning or June 18-21 Integration Services class between May 21 and June 18, 2007, and you can have a choice of a $250 Disney Gift card (bring the family!) or four free nights in a hotel. Just enter GIFTCARD or HOTEL in the customer comments field during check out. This gives you the chance to visit our Orlando training center for about the same cost as attending a local class since we pay for the hotel for up to four nights! Alumni, don't forget you can receive a 10% discount on any of our classes - just ask for it! June is a great time to visit Florida and the evenings are the best time to enjoy the local attractions. Contact us at training@endtoendtraining.com with questions about the offers, limitations, restrictions, etc.
Upcoming Classes
In our last update we announced two new offerings; End to End Business Intelligence and One on One Mentoring. We've had a lot of interest in both and a lot of requests for variations of the One on One Mentoring. We're also working on a free SQL event in Orlando similar to a Code Camp that will be targeted at SQL professionals. If it works out, we may try it in other cities.
Below is a quick synopsis of our schedule, you can view the full schedule here.
Integration Services
Jun 18, Aug 27, Dec 17
Performance Tuning
May 21, Jun 11, Jul 16, Aug 20, Sep 10, Oct 15
End to End Business Intelligence
Jul 30
One on One Mentoring
Jun 4, June 25, Jul 9
Analysis Services
Aug 13
See the full schedule and registration on http://www.endtoendtraining.com
-- Brian
Wow, I've broken the number one blog rule. I've been busily writing these three BI classes, that I haven't had time to eat, much less blog. My apologies! Well back from the long lull, the classes are now complete and will be given in Jacksonville (Jax) and other cities around the country soon. Here's the upcoming schedule. Each topic is two days in total and the combo makes 4 days. After giving the SSIS class a number of times, we're expanding it to give you more hands-on topics so when you leave the class, you will truely know how to implement solutions.
Designing and Implementing SSIS Solutions - July 18th - 21st in Jacksonville
Designing and Implementing SSIS Solutions - July 24th-27th in Orlando
Designing and Implementing SSIS Solutions - Oct 2-5, 2006 in Orlando
--- End Series 1 ----
SSIS / SSAS August 22nd - 25th in Jax
SSIS / SSRS Sept 19th - 22nd in Jax
SSAS / SSRS Oct 24 - 27th in Jax
Email SQLTraining@idea.com for any of these three above classes.
--- End Series 2 ----
Agenda: http://www.whiteknighttechnology.com/downloads/bi%20class%20outline.doc
Price: The classes run $2,200 for 4 days.
-- Brian Knight
Service Pack 1 is here and it fixes loads of items. Some of which were quite embarassing when training or developing a solution with a client standing behind you. The largest enhancement is the addition of database mirroring, which allows you add high availability to your database by sending transactions to another server in real-time. There are loads of SSIS fixes and enhancements. Add a quick rolled up level, here are the SSIS fixes:
| • |
Usability of the Import/Export Wizard has been improved in multiple-table scenarios. |
| • |
The IDtsPipelineEnvironmentService service lets custom data flow components have programmatic access to the parent Data Flow task. |
| • |
Interoperability with Analysis Services has been improved. The DataReader source in SSIS supports the System.Object data type by converting columns that have this data type to the DT_NTEXT ssISnoversion data type. To change the data type to a type that is more appropriate for your data, you can add a Data Conversion transformation. |
| • |
Performance has been improved for many transformations, such as the Sort transformation. |
| • |
Designer usability has been improved. For example, you can now right-click Dataflow and then click Execute Task to execute only the Dataflow task. You do not have to switch to the control flow to execute only the Dataflow task. |
| • |
The Expression Builder dialog box now has a public and documented API. This increased access to expression-related objects will benefit task developers because expressions are important to the way that tasks are interrelated. |
| • |
We have added a registry-based policy for handling SSIS package signatures as a package is executed. For example, administrators can disable loading of unsigned packages and untrusted packages. Because these settings are in the registry, administrators can propagate these settings in the domain by using Microsoft Windows Group Policy policy settings. |
| • |
In the Advanced Editor dialog box, the Flat File source has the new property UseBinaryFormat. This property supports loading packed decimal data into the pipeline for processing by a script or by a custom transformation. |
| • |
In the Advanced Editor dialog box, the DataReader source has the new property CommandTimeout. You can use this property to modify the time-out period to allow for long-running operations. |
| • |
To create or to modify the property expressions of variables, you can now open the Expression Builder dialog box from the Properties window. |
| • |
You can now add annotations to precedence constraints. |
http://www.microsoft.com/sql/sp1.mspx
It's certainly a must download for anyone using 2005.
-- Brian Knight
In Reporting Services you can dynamically alter the color of a font by clicking the drop-down box for a font color and selecting Expression. This works well, if you would like to show a red color if your not crossing certain price points and green if your numbers look good. The syntax to perform this type of action would look like this:
=IIF( Fields!ytd_profit.Value < 1,"red","green")
Essentially the above is a clasic IF THEN....ELSE statement. If the profit is less than $0, then turn the color red, otherwise keep it green. The problem comes if you want a third or fourth condition. In this type of situation, you'll need to use the SWITCH statement. This statement in Reporting Services performs the same type of activity as a CASE statement in VB. Here's an example of how you could use the same logic as before but show especially good profit as blue.
=Switch( Fields!profit.Value < 1,"red",
Fields!profit.Value > 0, "Green",
Fields!profit.Value > 100, "blue")
-- Brian Knight