MOSS and BI Presentation at SBTUG

Grant Paisley - Sunday, July 29, 2007
This month I presented at the Sydney Business and Technology User Group on the topic of MOSS (Microsoft Office Sharepoint Server) and BI.

You can read Craig Bailey's review of the session here: http://blog.craigbailey.net/2007/07/sbtug-report-on-last-meeting-online.html.

Business Intelligence Presentation at Sydney SharePoint Users Group

Grant Paisley - Thursday, June 21, 2007
This month I presented a talk at the Sydney SharePoint Users Group about Business Intelligence.

There is an overview of the session in a great blog post by Nick Kellett here: http://planetmoss.blogspot.com/2007/06/yesterday-i-attended-monthly-sydney.html.

Nick also has some other great posts on SharePoint over at his Planet Moss Blog.

Thanks for the post Nick!

Event: Best Practice in Analysis Server and MDX

Grant Paisley - Tuesday, June 05, 2007
Microsoft SQL Server 2005 Analysis Services includes a complete re-architecture of the Online Analytical Processing (OLAP) engine from Microsoft SQL Server 2000.

In this session we will focus on the performance and scalability aspects of the Analysis Services architecture and cover cube design, providing specific guidance on how to design for optimal performance, and also cover MDX and calculations.

Cube design and storage

We will discuss cube modeling (emphasizing the importance of attribute relationships), partitioning strategies, aggregation management and cube processing. For each of these, we will explain the important design principles, and provide best practices for performance and scalability.

MDX and calculations

We take common business problems and show how MDX can be utilized to solve them while keeping query performance under control. Finally we point out common gotchas for poor performance in MDX and best practices to address them.

These principles and practices should be a necessary foundation for any organization embarking on a large-scale business intelligence project based on Analysis Services 2005.

We will make *you* the BI hero!

For more information see http://www.sqlserver.org.au/Events/ViewEvent.aspx?EventId=264

Event: Implementing Partitioned Tables

Grant Paisley - Wednesday, March 14, 2007
In this session Geoff Orr will demonstrate how to implement and update partitioned tables. He will also show how partitioned tables can assist to manage very large databases (VLDB's) and how to perform archive operations without the need to perform deletes.

For more information see http://www.sqlserver.org.au/Events/ViewEvent.aspx?EventId=243

Your Questions Answered - how SQL Server 2005 works with Office 2007

Grant Paisley - Monday, January 01, 2007
Greg Linwood and Grant Paisley team up to answer your questions about how SQL Server 2005 works with Office 2007.

This article first appeared in ZDNet Australia's Microsoft MVP Series Site

Can you point me to cost saving stats which I can take to my boss to convince him that an upgrade to SQL Server 05 is a must-do from our current ss2000 environ?

I'm not aware of any published cost savings statistics, but one very clear cost saving advantage is that SQL Server 2005 Standard Edition has removed the previous memory constraints imposed by SQL Server 2000 Standard Edition (2Gb). This means that a version upgrade from SQL Server 2000 Standard Edition to SQL Server 2005 Standard Edition is much cheaper than an edition upgrade to SQL Server 2000 Enterprise Edition. Another way of looking at this is that SQL Server 2005 Standard Edition can handle vastly larger workloads than SQL Server 2000 Standard Edition, simply because of it's significantly improved memory addressability. There are many other cost saving manageability aspects with SQL Server 2005, but being able to address significantly more memory is a real winner on its own!

Is SQL 2005 going to be intergrated into Exchange 2007 nativelly.

Not that I'm aware of. I'm a SQL Server specialist, but I believe Exchange 2007 still uses its own DBengine.

Is is true that you can use Excel as a reporting tool now and get data from different data sources, cubes, etc.?

Excel has always supported getting data from multiple data sources via ODBC and OLEDB - what is new in Excel 2007 is it’s richer graphical capabilities. For instance it now has Conditional Formatting options such as

  • "Data bars" - overlays transparent bar on data value
  • "Color Scales" - heat map cell coloring
  • "Icons" - traditional KPI type flags

It also natively supports SQL Analysis Server 2005 including displaying KPI’s. Even better with Excel Server you can publish Excel 2007 workbooks up onto a server where users can access the Excel/Excel Report with just a browser.

What do you need to have installed on your desktop PC to use Office with SQL Server 2005? Can you use previous versions of Office as well?

I must admit that I'm still using Office 2003 with SQL Server 2000 & 2005 on my notebook, so I can confirm this works (as far as I'm aware, there are no special installation requirements -- you just install Office, connect to SQL Server 2005 & start work!

You just need a standard install of Office XP, 2003 or 2007 as they will all play with SQL 2005 fine. If you want to interrogate SQL Server Analysis Server 2005 (SSAS2005) then any version of Excel will work, though only Office 2007 will best utilize the SSAS 2005 features. Where things get exciting is when utilizing Office 2007 with Office SharePoint Server 2007 - this combination gives true enterprise functionality, including document libraries, versioning, work flow, thin client Excel publication, enterprise search...

I want to run SQL Server 2000 and SQL Server 2005 on the same server until I can get another machine -- is this possible?

Absolutely. It's possible to run a few instances of both actually, using named instances. I personally keep one SQL Server 2000 named instances & one SQL Server 2005 named instance running on my notebook for support purposes, although I tend to turn the SQL Server 2000 instance off via the Windows Services applet (under Administrative Tools) to save CPU & memory resources whilst I'm not using it.

How much does it cost to upgrade to SQL Server 2005?

Licensing questions such as these are very tricky as there are many possible answers. It depends on which existing edition & version of SQL Server you currently have, whether you have software assurance, possibly an enterprise agreement, whether you're licensing per seat / CPU or via SPLA. So unfortunately, I have to refer to your reseller or Microsoft for this.

Is it true you can use Excel as a reporting tool?

This is not new -- Excel 2003 can look at Analysis Server 2000 and 2005 "cubes" but user interface is challenging for the user with Office 2003 looking at SQL 2005 Analysis Server -- eg Office 2007 gives much better support to Analysis Server now. Excel as a reporting tool is especially useful when combined with Office SharePoint Server 2007. So power users have Excel 2007 and publish up into a SharePoint document library. Other users only require a browser to display the Excel worksheet/report.

I am looking to build a scorecarding application using Excel as the front-end and want to use SQL Server 2005. Can I use Excel XP to get started or do I have to install Office 2007 first to get it to work with SQL Server.

Assuming you are talking about building a scorecard against Analysis Server, I would recommend moving straight to Excel 2007 as it directly supports KPIs, and has richer graphical capabilities. For instance it now has Conditional Formatting options such as

  • "Data bars" - overlays transparent bar on data value
  • "Color Scales" - heat map cell coloring
  • "Icons" - traditional KPI type flags

Even better with Excel Server (part of Office SharePoint Server) you can publish Excel 2007 workbooks up onto a server where users can access the Excel/Excel Report with just a browser. You could build it with Excel XP but you would get very frustrated...

Excel 2007 would be the best choice for building a scorecard as it natively includes indicators and data bars.

Note: you can install Excel 2007 and keep you existing version of Excel without any problems. So you *could* start on Excel XP.

Can I still use Microsoft Access as a front-end to SQL Server 2005? Will I need to upgrade to the latest version of Office? If that is the case, I probably won't upgrade to SQL Server 2000 as we have deployed Office 2000 at the moment.

I'm not sure about Access 2000, but Access 2003 certainly works well with SQL Server 2005 (we use this combination extensively at work). Given that Access's pass-through query feature works simply over ODBC & SQL Server 2005 still supports ODBC, you shouldn't expect any problems in this area.

Are there any plans to update the query tool in Excel 2007 or future versions? We use it extensively for pulling data from various databases/sources and probably won't upgrade until we see what we can do with Excel 2007.

Query building tools really only generate SQL statements, so there's no reason why you can't use another tool if one doesn't cut it for you. As SQL is my primary language, I haven't learned the finer points of difference between the various tools, but I know that Visual Studio, MS Access & SQL Management Studio all have their own flavours of these tools, so it might be worth trying a few others out to see which you like best.

I'd like to find out more about the query mechanism in Excel 2007. Does Microsoft have any plans to update it?

I can't say whether Microsoft have any further plans to upgrade query mechanisms in Excel 2007, but as with the previous answer, I'd suggest you shop around some of the other query tools and see if any other do the job better for you.

What is the policy regarding upgrades for SQL? If just bought SQL Server 2000 but I want to use 2005 now. Is there someplace I can download an updater or something? I looked on the M$FT site and they only had a trial version of SQL Server 2005.

Naturally, you'd need to purchase an upgrade licence from a reseller, unless you have software assurance.. I wish I could set policy otherwise.

SQL Servants... is that spelled correctly?

Yep -- we're just humble little SQL Server Servants here.

Can you install SQL Server 2005 on a 64x box? We are getting a new 64x box for Exchange (which I am still fuming over) and want to put SQL on the same platform.

Yes, SQL Server 2005 runs great on x64. I'd recommend thinking twice about running SQL Server & Exchange side-by-side though as they're both really designed as stand-alone server solutions & will compete with each other for resournces (memory, CPU etc).

Are indexes any better in SQL 2005? I have queries from Excel that run like a dog (no offense to the dog).

I've met a couple of slow dogs that couldn't get away from me but that's another story! As for indexes, there are some MAJOR improvements in SQL Server 2005. By far my favourite improvement is online index rebuilds. I've lost a lot of sleep over the years managing index rebuilds on transaction processing systems which can't be taken offline until late in the evening / early morning. With SQL Server 2000, you had to rebuild your indexes either late at night or block transactions if you did it during the day. SQL Server 2005's ability to rebuild indexes online means you have much more flexibility about when you rebuild indexes, which translates indirectly to more personal sleep! (not with dogs though). Included columns can help "cover" more queries, improving performance & being able to disable indexes can also help make smaller copies of databases too.

How would you reccomend going about upgrading from MSDE 2000 to SQL Server 2005? We have a VB front-end that we use for reporting on a number of small databases, but we would like to move this to Excel 2007 if given the chance.

There are a very small number of potential gotchas in upgrading between MSDE 2000 to SQL Server 2005. As MSDE didn't have DTS, by far the biggest gotcha area in SQL 2000 > SQL 2005 upgrades is right out of the picture. There ar a few deprecated language statements, but it's very unlikely you're using those unless you've got some very old code. So, I suggest you simply download a trial version of SQL Server 2005, restore a backup of your MSDE 2000 DB to your new SQL Server 2005 instance (this should just work) & test for any errors, which should be very unlikely. As for whether migrating to Excel 2007 works better for you than VB.NET, this will be very application specific, but one thing I still love about VB.NET over Excel is the richer .NET language & tools.

I have downloaded the evaluation copy of Office 2007 and installed it but I don't see where you get into SQL Server.

Go to www.microsoft.com/sql & pull down a copy of SQL Server 2005 Express Edition from the left hand "Downloads" link. It's free & you should be able to connect direct to it from Office 2007 once you've got it set up.

What is AS 2005?

[A]nalysis [S]ervices 2005 (AS 2005) is SQL Server's multi-dimensional (OLAP) engine. The main relational engine (RDBMS) works with two dimensional tables (rows & columns) & is typically used for transaction processing via it's Transact-SQL (TSQL) language. AS 2005 works with multi-dimensional "cubes" via its Multi-Dimensional Expressions (MDX) language. Cubes are typically used for reporting & analytical purposes where running queries against the base transaction processing RDBMS databases would be too cumbersome or inefficient. Data is typically extracted from relational databases & transformed (aggregated) into multi-dimensional structures, representing summary views of information which needs to be quickly & easily analysed (eg sales data).

What are the advantages of SQL Server over open-source alternatives such as MySQL and PostgreSQL? Is the cost justified?

Who knows how well MySQL and PostgreSQL can perform when they don't post benchmarked performance results to TPC.org? Sure, claims can be made but TPC is the participant agreed industry standard for measuring performance & I've not seen any audited benchmarks from those guys so its impossible to really say how well they can perform. Don't forget that it's usually the hardware vendors who are trying to boast about hardware performance in these benchmarks, so if there were any advantages in choosing mySQL / PostgreSQL, they'd be sure to choose them. The benchmarks are cost / performance oriented, so it's hard for them to even make the cost saving argument plausible.. Like so many things - you get what you pay for with database management systems.

Event: Business Intelligence "lights up" with Office SharePoint 2007

Grant Paisley - Thursday, November 09, 2006
Come and see how Office SharePoint 2007 "lights up" Business Intelligence.

What is business intelligence? It is simply "turning data into knowledge"

We have extraordinarily powerful quiver of tools for building knowledge from data in SQL Server including:

1. SQL Engine - data storage
2. Analysis Server - multidimensional database
3. Integration Services - move and transform data
4. Reporting Services - creation, management, deployment of reports.

Office SharePoint 2007 gives us the best vehicle to integrate and expose this knowledge through collaboration and document management. In this session we get a whirlwind tour of:

- KPI's / Dashboards
- Server based Excel and data visualization
- Report Centre
- BI Web Parts
- Business Data Catalog

This is the session presented by Grant at TechEd 2006 in Australia and China

Microsoft Office SharePoint 2007 is in Beta 2 now and will be release late this year...

For more details see http://www.sqlserver.org.au/Events/RegisterMeeting.aspx?EventId=220

Event: Grant to present at TechEd China 2006

Grant Paisley - Thursday, September 07, 2006


Business Intelligence "lights up" with Office SharePoint 2007

What is business intelligence? It is simply "turning data into knowledge"

We have extraordinarily powerful quiver of tools for building knowledge from data in SQL Server including:

1. SQL Engine - data storage
2. Analysis Server - multidimensional database
3. Integration Services - move and transform data
4. Reporting Services - creation, management, deployment of reports.

Office SharePoint 2007 gives us the best vehicle to integrate and expose this knowledge through collaboration and document management. In this session we get a whirlwind tour of:

- KPI's / Dashboards
- Server based Excel and data visualization
- Report Centre
- BI Web Parts
- Business Data Catalog

This is the session presented by Grant at TechEd 2006 in Australia and it will now be presented at TechEd in China

Event: SQL Server 2005 Reporting Services: Advanced Report Authoring

Grant Paisley - Sunday, July 17, 2005
This session presents advanced techniques for building reports with SQL Server 2005 Reporting Services and the Visual Studio-based Report Designer.

Topics include

* how to use the built-in expression language,

* report parameterization
(data driven, multi-valued, and hierarchical),

* supporting multiple data sources
(including relational, multi-dimensional, and XML), and

* making reports interactive.

See http://www.sqlserver.org.au/Events/ViewEvent.aspx?EventId=100 for details

Event: Business Intelligence - What will my user see?

Grant Paisley - Monday, June 27, 2005
Ever wondered what Microsoft offers the users for Business Intelligence?

What is possible with tools you already own...

Plumbing - BI Architecture SQL2000 v's SQL2005
Exploring - What tools are best when
Gluing - What do we use to bring it all together

Perception is that Microsoft has a market leading back end in with Analysis Server, but has not had decent front end tools. This no longer true! There are now a range of technologies available including:

- Office Web Components,
- BI Portal,
- Sharepoint,
- Reporting Services,
- Report Builder,
- Excel,
- Data Analyzer,
- Excel Accelerator for OLAP,
- Scorecard Accelerator.

In this session we will explore some of these offerings demonstrating the rich BI delivery platform Microsoft now offers with real world examples.

For more details see http://www.sqlserver.org.au/Events/RegisterMeeting.aspx?EventId=90