In case you missed it, SetFocus Senior Instructor and Microsoft MVP, Kevin Goff, recently presented a comprehensive webinar on the Business Intelligence Master’s Program and discussed topics covered during the 12 week program, provided demos and answered all questions. Topics included:
- Intro to Transact-SQL 2008 Demonstration
- Advanced Transact-SQL 2008 Demonstration
- SQL Server Integration Services 2008 Demonstration
- Data Warehousing/Dimensional Modeling Demonstration
- SQL Server Analysis Services 2008 Demonstration
- MDX 2008 Programming Demonstration
- SQL Server Reporting Services 2008 Demonstration
- PerformancePoint Server 2007 / SharePoint 2007 Demons
Q&A Roundup
Adrian Asked: With new advancements pointing to more and more use of Object Oriented databases as opposed to Relational Databases, do you see Microsoft evolving SQL Server to this new type of database? Also, do you think that SQL would still be useful as a language to work with OO Databases, or would another language/tool take it’s place?
This is a very good question, and one that others have asked in the past. While one can never predict with certainty what direction Microsoft will take, I don’t expect Microsoft to make huge advancements in evolving the core SQL Server database management system to an OO-type database, at least not in the near term.
Having said that, I anticipate more growth and accessibility of tools like the .NET Entity Framework and third-party tools like nHibernate and other ORM (Object-relational mapping) products. This satisfied the interest of .NET developers who want to map relational databases into more object-oriented constructs, while also maintaining the relational paradigm to which DBAs and database developers are accustomed.
And yes, I still see the SQL language as important. Microsoft made some T-SQL enhancements in SQL 2008, and it’s likely we’ll see more enhancements in the future. Even though languages like T-SQL and MDX are procedural and functional in nature, they are not going away any time soon.
T-SQL is simply “all over the place” in the BI stack. T-SQL is necessary in database ETL processes. T-SQL is necessary in managing a database staging area. T-SQL is necessary in creating OLAP partitions. T-SQL is certainly necessary when creating complex reports against relational databases/data warehouses.
Tom Asks: My focus now is using the Azure platform, using F# as my primary language now, concerned with performance & parallel-asynchronous workflows included multi-core, concerned about performance issues using xml, linq mainly for feeds to banks etc., used plenty of RS, less OLAP work so issues with that is my question from the cloud context.
Currently SetFocus is not covering Azure, though we are always evaluating newer technologies for possible future curriculum.
SQL Server 2008 and 2008R2 are designed more for parallel execution than SQL Server 2005. So, “out of the box”, SQL Server 2008 takes greater advantage of multi-processor environments than SQL 2005.
The inquiries regarding XML and LINQ are very broad. Depending on your specifics, some of these issues have been addressed by different distributed techniques.
If the question could be a little more specific, we’ll be happy to respond.
Jweeta Asked: What would be the impact on the performance when implementing CDC on the database and several tables?
Change Data Capture (CDC) certainly has an impact on both performance and the size of the transaction log. Recently I did a test where I ran a SQL script that inserted and updated a half million rows, both with and without CDC running. With CDC running, the process took 87 seconds. Without CDC running, the process took 47 seconds.
Now, the non-CDC process that took 47 seconds was not doing ANY audit trail logging with database triggers. Surely, implementing update triggers (for audit trail processing) on the non-CDC process would have added some time to the 47 seconds.
Microsoft has published an excellent white paper on CDC performance tuning: http://msdn.microsoft.com/en-us/library/dd266396(SQL.100).aspx
Angel Asked: What was the add-on for SSIS configurations?
The SSIS configuration editor I used is a free editor from the following site:
http://agilebi.com/cs/files/folders/17/download.aspx
As far as free utilities go, this free SSIS configuration editor is one of the better free utilities I’ve ever seen.
Timothy Asked: Do I really need to know .net VB or C# for 4 or 5 lines of code? Couldn’t I have a developer do that? Am I going to learn VB.net or C#.net in the BI Masters program?
Good question – unfortunately, there’s not a simple answer. In some environments, a .NET developer might be available to help you. In other instances, you may not have that luxury.
A good number of SSIS packages require at least a few lines of VB or C# script – we cover several of these instances in class. We also cover other scenarios – such as when a developer might need to integrate an SSRS report with a .NET web page.
We don’t teach full-blown .NET in the BI Master’s program, but we do offer some information and sites and free videos from third-party sources to get started with .NET.
Milton Asked: Could you briefly describe the differences between Bill Inmon methodology vs. Kimball methodology?
The following link provides a good general breakdown:
http://www.nagesh.com/publications/technology/173-inmon-vs-kimball-an-analysis.html
In a nutshell, the Kimball approach is viewed as more of a “bottom-up” approach, where data marts are constructed and then integrated into a more large-scale enterprise system. The Inmon approach is viewed as more “top-down”, by creating a large enterprise-wide database and then creating data marts.
There are also other differences, such as Inmon advocating 3rd normal form, whereas Kimball recommends denormalized star schemas (and occasionally snowflake dimension schemas)
Michael Asked: Can you address how SSRS stacks up against Crystal? What might a Crystal shop want to consider before migrating to SSRS?
A good question. I used Crystal Reports heavily for over a decade, but I work more with SSRS these days. Crystal is a feature-rich product, and has had close to 20 total releases going back to the early 1990’s. SSRS 2008 R2 (the latest release of SSRS) has seen 4 total releases.
SSRS has most (approximately 85-90%) of the functionality that Crystal does. Considering SSRS has only seen 4 releases, that’s quite an amazing feat. There are a few report grouping options that work better in Crystal than in SSRS. There are instances where building report parameters in SSRS require a little more effort. But in general, I find I’m just as productive in SSRS as I am in Crystal. (And the newest release of SSRS 2008R2 has some charting features that work better than in Crystal).
The paradigms are certainly different, so much of the learning curve in moving from one environment to a completely different one. Anyone who has used Microsoft Access will comment that SSRS looks like Access, though SSRS many capabilities beyond Access.
A Crystal shop that is thinking of migrating to SSRS should take a small handful of their most difficult reports, and make sure they can recreate the report in SSRS (or at the very least, build a good functional prototype in SSRS). That’s the best way to see what types of issues you might encounter. I DO recommend recreating the report from scratch, as opposed to trying to use any conversion program.
Some people who migrated from Crystal to SSRS 2005 discovered the resource and performance issues associated with SSRS 2005 in heavy reporting environments. These issues were so significant that in some instances, companies reportedly would run the reporting engine on a different server. Fortunately, Microsoft re-wrote the reporting engine in SSRS 2008 to address these resource and performance issues. (Microsoft also changed SSRS 2008 so that IIS was no longer required for internal reporting applications).
Back around 2005, some polls showed that Crystal had more than 50% of the report developer market, while other tools like SSRS and ActiveReports were much lower. However, most recent polls have showed SSRS gaining significant ground – much of this is attributed to the increased functionality in SSRS, the overall growth of the SQL Server database platform, and even Crystal’s licensing policies (which have changed several times over the years).
Because I’ve worked heavily both with Crystal Reports and SSRS, I’m able to answer specific questions in our classes on going between one and the other.
If you have follow-up questions, please feel free to email Kevin at kgoff@setfocus.com or visit his blog