Ok so I have not blogged in a long while. So time for an update!
I have been very busy working on a few data warehouse projects and have not had the time to blog. So I am glad to get sometime out to go to Microsoft's first BI conference in Seattle next week.
It's a Conference of many firsts, first time Microsoft has created a forum for BI using Microsoft products. Sure there has been many a 'TechEd Session' on SSRS, SSAS and SSIS. But this is the real deal best practices, case studies and the like. And I am not talking about project real either. The big difference with this conference is there is not mix bag of sessions.
As a part of the trip I am going to blog every day of the conference to let you know what went to.
It's very exciting.
2007-01-19T21:24:46.443+13:00Microsoft and Teradata have formally announced a partnership. It looks like SSIS, AS2005 and RS2005 will be able to access Teradata enterprise warehouse. This will help Tetra data access Performance Point.
2007-01-18T21:23:53.693+13:00Just came across a set of PerformancePoint webcasts on http://thesource.ofallevil.com/events/series/sqlserverbi.mspx. Check out:
TechNet Webcast: Microsoft Business Intelligence: Introduction to PerformancePoint Server 2007 (Level 200)
Friday, January 19, 2007
8:00 A.M.–9:00 A.M. Pacific Time
Discover how Office PerformancePoint Server 2007 provides you with critical visibility into the factors that affect your business, allowing you to make decisions and take actions that drive better business outcomes.
2007-01-18T21:23:00.450+13:00Happy New Year! I am sorry I have not blogged in awhile. It’s largely due to a house we are building in Auckland. Building a house is very similar to building a data warehouse, best left to the experts.
What a year 2006 it has been. I have to say Business Intelligence on SQL server has definitely seen some big changes this year. After the release of SQL Server 2005, big things had to happen to top the release and what a year it has been.
Service Pack 1 Was not long in coming after the November release of SQL, helped a lot of companies to get of over the ‘not until SP1’ issue. The release was not without controversy. The removal of the ‘Select All Parameter’ caused some developers concern. Microsoft removed it due to performance issues. At the same time MS released the feature pack with a series of great free add-ons. Such as a SAP .Net OLE DB driver and data mining viewer controls. Check out:
Rather Ripped (Select All in Service Pack 1)
Feature Pack for Microsoft SQL Server 2005
The acquisition of Proclarity has shown Microsoft continued commitment to build its BI platform. Seeing the direction and integration of Proclarity, Balance Scorecard Manager, etc into Performance Point is very exciting and will start to give some of the Analytical BI vendors a run for their money. Check Out:
Performance Point Home
Office 2007 was shown off at TechEd and is very exciting from a BI point of view. The new excel features are going to continue Excels front-end dominance. Although the new Excel front-end may take some getting use to for some users the new BI features are well worth the upgrade. Check out:
Business Intelligence in Excel 2007
Service pack 2 CTP is going to continue to enhance the BI features of SQL 2005. Report builder will now support Oracle as a data source. The ‘Select All’ parameter is now back in en-vogue. Of course there will be the standard bug fixes. So best get to testing and seeing the new fixes/enhancements. Check out:
A list of the bugs that are fixed in SQL Server 2005 Service Pack 2 Community Technology Preview (CTP)
2006-12-20T14:12:10.836+13:00Microsoft have just release SQL Server 2005 Service Pack 2 CTP. Check out: How to obtain the latest service pack for SQL Server 2005
2006-09-30T19:29:56.606+12:00Microsoft has just released an update to the Microsoft Certified IT Professional (MCITP) with the creation of Business Intelligence Developer credential. It looks like you only need to sit two exams to become a Business Intelligence Developer.
A colleague of mine has just introduced me to one of the best blogs I have seen in along while. The Tips, Tricks, and Advice is brought to you by the SQL Server Query Optimization Team.
Here are some of my favourite posts on the performance features of SQL 2005 Query Optimizer. Check out the following:
Microsoft has released a series of free skill tests that are well worth trying. There are tests covering most Microsoft products. There are two categories that I have given a go.
2006-09-04T00:11:40.420+12:00After TechEd I realised that in my slide deck I did not recommend The Microsoft Data Warehouse ToolKit by Joy Mundy and Warren Thornthwaite as required reading before starting a BI project.
2006-08-31T21:39:54.046+12:00My TechEd session on Delivering an End to End Business Intelligence is now up on Microsoft for download.
2006-08-30T08:09:35.363+12:00I have just come across a new download from project real. It’s a complete business intelligence reference implementation. I am currently downloading the 238.2 mb sample. It looks like it a good example of an end to end solution.
2006-08-23T12:39:59.993+12:00TechEd 06 has been a sold out event for a number of weeks now. It sold out in record time. For those you that are not aware, Microsoft NZ TechEd is the largest annual IT conference in New Zealand with over 2000 delegates attending.
2007-01-18T21:21:54.963+13:00I have lost count the number of times I go searching for the patch level or version number in SQL.
Other Build versions
There are heaps of sources for this information. Listed below are some helpful links for earlier versions of SQL Server and software update naming conventions.
How to identify your SQL Server version and edition
New naming schema for Microsoft SQL Server software update packages
2006-07-25T23:47:07.106+12:00I have just come across a new set of sample databases for SQL Server 2005. Some of the examples are based on Project Real Business Intelligence best practices project. Based on a real world implementation of SQL Server 2005 by Microsoft.
2006-12-06T01:00:25.816+13:00The other day something happened to my laptop that took me completely by surprise. Windows Update decided SQL2005 SP1 was a critical patch. And proceeded to download and install SP1.
2006-07-17T19:59:45.750+12:00I have just come across a series of must see webcasts by the Kimball Group consultants that wrote The Microsoft Data Warehouse Toolkit. I have not viewed them all yet. I am currently viewing the MSDN Architecture Webcast: Using SQL Server 2005 Integration Services to Populate a Kimball Method Data Warehouse (Level 200). Which I highly recomend.
2006-07-05T10:38:19.810+12:00I normally don’t recommend tools on this blog. But over last month I have been working with couple of tools that are worth a mention.
2007-01-18T21:21:02.575+13:00I have been working with SQL Management Studio (SMS) a fair bit lately. Like most developers I always look for the standard short cuts that I used on Query Analyser. Below are a few of my favourites.Commenting I use these short cuts all the time to comment large sections of code. The comment style I prefer the most is dash form which I prefer too the slash style. -- Dash Comment /* Slash Comment */To comment in SQL management studio you must now do Ctrl+K, Ctrl+C instead of Ctrl+Shift+C in Query Analyzer.To uncomment in SQL management studio you must now do Ctrl+K, Ctrl+U instead of Ctrl+Shift+R in Query Analyzer. Formatting To indent a block of code Select the code you would like to indent and press TAB to indentShift+TAB to un-indent your code Word Warp long strings with Ctrl+E, Ctrl+WMake Uppercase Ctrl+Shift+UMake Lowercase Ctrl+Shift+LBookmarksOk so you are working on a massive script that travels for miles. Well then you better be using book marks to navigate that code. Bookmarks are great for moving through scripts and trouble shooting. Try out the following the next time you are working on a big script.Toggle Book Mark on/off and Crtl+K, Ctrl+K Enable all book marks Crtl+K, Ctrl+AMove to next bookmark Ctrl+K, Ctrl+N Move to previous bookmark Crtl+K, Ctrl+PMove to next bookmark folder Ctrl+Shift+K, Ctrl+Shift+NMove to previous bookmark folder Ctrl+Shift+K, Ctrl+Shift+PClear book marks Ctrl+K, Ctrl+L[...]
2006-05-16T00:57:55.936+12:00Ok I haven’t recommended a book in a while, and it’s not from the lack of reading them. I thought its time I recommend one of the books I always use on most of the projects I work on. SQL in a Nutshell from O'Reilly Press is my universal translator between database engines.
2006-04-29T23:52:58.840+12:00Ok, it’s been a really long time since I posted, well over a month. Work commitments have taken a bit more of my time this month. If you missed it this may have been the biggest month for SQL Server since RTM release.
2006-03-25T23:33:18.680+12:00I came across an interesting KB post on Microsoft support today. A complete list of bugs fixed in SQL Server 2005 Service Pack 1 CTP. There is a list of 19 fixes at the time of this post. This CTP service pack fixes a fair amount of Analysis services issues. The list will be updated as Microsoft document other fixes.
If you have not downloaded SQL Server 2005 Service Pack 1 CTP check out:
2006-03-15T23:10:31.646+13:00Microsoft have just release another SSIS Sample Component for Packed Decimals.
2006-03-15T09:36:14.486+13:00This is the second instalment of my blog feature on Beginners Guide to Business Intelligence. This time I am looking at: What is a Data Warehouse exactly?Business Intelligence ContextFirst Business Intelligence in my opinion is not just architecture. It is a strategy of information within an organization. All organizations report on the performance of their business processes, services and sales. How they manage and organize information is maintained by a Business Intelligence Architecture, which is made up of several components. A data warehouse is just one such component. The data warehouse is sometimes seen as the corporate repository. Before we get into the detail of data warehouse definitions lets have a flash back for a bit of data warehouse history. Birth of Data warehousingIn a sense, companies have been reporting on information since the dawn of computing systems. Data warehousing is really a collection of technologies that have evolved within information systems. Listed below are some of the legacy terms that you may come across when talking about data warehouses.Executive Information System (EIS)One of the first attempts at giving executive managers a high level view of business activities, this kind of system was widely adopted in the late 70s and early 80s. Usually these systems would be summary tables built on top of transactional tables. EIS focused solely on the financial view of the organisation with limited information other than budget vs. actual. The tables would be maintained within the source system and loaded once a month and reports would be generated on top of them, almost always printed on line-flow paper. Management Information System (MIS)An extension of EIS, the MIS gave business users a wider view of information in the form of Balance Scorecards and other summarised views of information. The term Key Performance Indicators (KPI) was widely adopted for measures that were just not financial based. For the first time summary tables would be maintained independently of the transactional source system. This kind of system was widely adopted in the late 80’s early 90’s. In fact entire departments were named after MIS systems, hence the term MIS Department.Decision Support Systems (DSS)The next evolutionary step was the creation of the DSS. A term widely used when discussing OLAP solutions. In the 90’s you were more than likely building a DSS system to support OLAP reporting. This was the era of creating reporting systems for areas of the business no longer just focusing on Financial and Sales reporting. It has to be noted that if is was not for products like Essbase, Cognos and front-end tools like Lotus 1-2-3 and Microsoft Excel; DSS systems may not have been so successful. It was the first time that information was delivered to operational roles outside of transactional systems. Data WarehouseThe data warehouse really is a central repository combining all of these earlier systems. The early data warehouses were third normal form 3NF databases, taking incredible amounts of time to develop. Due to the large nature of early data warehouse, data integration was heavily used in the creation of data warehouses, requiring special ETL (Extract, Transform, and Load) tools and skills - even worse to develop and create reports against. At this time two distinct trains of thought appeared when it came to defining what a data warehouse should be. The first general definition was that t[...]
2006-02-06T21:36:03.976+13:00Well is seems the flow of add-ins for SSIS is starting. Microsoft has just release a lot of usefuls add-ins for SSIS. I am a big fan of the Regex component as it is often the hardest to code. Check out the links below.SQL Server SSIS Sample Component: CalendarTransformCalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes.SQL Server SSIS Sample Component: UnDoubleOutUnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place, or via the creation of a new output column.SQL Server SSIS Sample Component: RegexRegex is an SSIS dataflow component that applies a configured regular expression against an incoming column, matching, extracting, or splitting, as configured by the user.SQL Server SSIS Sample Component: UnpackDecimalUnpackDecimal takes an input column formatted in packed decimal (comp-3), and generates the corresponding Decimal value.SQL Server SSIS Sample Component: RTrimPlusRTrimPlus takes a string or unicode column, and removes trailing spaces, whether ASCII, or Japanese.SQL Server SSIS Sample Component: SeeBuffer SeeBuffer is an SSIS dataflow component that sits in a data flow and is provided a look at each buffer that is presented to it.SQL Server SSIS Sample Component: NullDetectorNullDetector is an SSIS dataflow component that sits astride a data flow, and, depending on whether the value of a user-indicated column is null or not, routes rows to one or the other of its outputs.SQL Server SSIS Sample Component: CodePageConvertCodePageConvert is an SSIS dataflow component that translates from and to any code page or unicode character representations.SQL Server SSIS Sample Component: ConfigureUnDoubleConfigureUnDouble takes a text column, and, removes bracketing quotes if present, plus places double quotes inside the text with sinqle quotes.SQL Server SSIS Sample Component: UnDoubleUnDouble takes a text column, and, removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.[...]