Angry Koala Blog

Spot - NSW Birds with Pivot

Tue, 23 Mar 2010 11:57:00 GMT

I have created an application called Spot for NSW bird sightings with Microsoft Researches new Pivot Technology.

Please vote for Spot if you like it!

You can run Spot yourself but you need to Download Pivot and install it first on Vista or Windows 7 - running Aero

Here is a Spot screencast demo...

or a lower quality on youtube

I have entered this in the Apps4nsw competition

Purpose
Allows you to identify a bird species that you have seen. Or find birds in you location that are under threat. Or just experience the variety and beauty of birds in NSW.

Users
Bird watchers, those interested in there environment and the status of bird life. People wanting to experiment with new technology...

Data
Spot is a Proof of Concept based on a 10% sample of species sighting data for NSW from the Department of Environment, Climate Change and Water combined with descriptive information from Birds Australia and create common photographs from some helpful photographers at Flickr.com. It could be extended to include all animal species, not just birds...

Note: the need to download special software (Pivot) will not be necessary once the Silverlight version of Pivot is released in a few months, at which point a standard browser will be enough...

Thank You
To Geoff Hutchinson for help entering descriptive info and his photos and the other photographers, Jon Irvine, Julian Robinson, Kip Lee and James Bergamin.

Grant

Event: Grant is presenting at the SQL PASS conference in Seattle

Wed, 04 Nov 2009 01:15:00 GMT

On Thursday 5 November at 1pm, Grant Paisley will team up with Paul Turley to present at the SQL PASS conference in Seattle. Paul will be presenting on Super Reports: Patterns and Recipes and Grant will join Paul for a part of the presentation to talk about building dynamic reports on Cubes.

Grant's topic will cover how to obtain & present dimension metadata to drive integrated report parameter selection, accessing metadata, dynamic rows and measures and he will be also showing off the OLAP browsing with Spermlines.

Dynamic Rows

Cube Metadata

Spermlines for 12 months of data

Seattle City B.I. on a Microsoft Surface Table

Mon, 26 Jan 2009 03:28:00 GMT

An interesting demo shown at the Microsoft B.I. Conference in Seattle, showing B.I. data overlayed on a 3D representation of the city, completely interactive using Microsoft's Surface touch screen technology.

Microsoft Video: Business Intelligence #01a: Introduction to Microsoft BI

Wed, 14 Jan 2009 04:45:00 GMT

From the Microsoft MSDN Channel 9 site:

"This screencast is the first in a series BI Developer screencasts recorded as part of a workshop built on SQL Server 2005 that have been delivered around the globe by Microsoft and Microsoft Partners. This training event takes the student through the Microsoft BI Platform giving a BI Developer the introduction and basic comfort needed to tackle a BI project using Microsoft technology".

This is a developer level screencast, so if you have any questions at all, please fell free to ask them in the comments section of this post.

See the video here:

Microsoft Video: Business Intelligence #00: Platform Overview

Mon, 26 Jan 2009 03:21:00 GMT

From the Microsoft MSDN Channel 9 site:

"This screencast is the first in a series BI Developer screencasts recorded as part of a workshop built on SQL Server 2005 that has been delivered around the globe by Microsoft and Microsoft Partners. This training event takes the student through the Microsoft BI Platform giving a BI Developer the introduction and basic comfort needed to tackle a BI project using Microsoft technology.
This first screencast will take you through the Microsoft BI Platform briefly touching on each of the technologies".

This is an introductory level screencast. If you have any questions at all, please fell free to ask them in the comments section of this post.

See the video here

Event: Fun with Dick and Jane? Excel 2007 Data Mining Add-in can tell us why

Fri, 30 Jan 2009 09:55:00 GMT

Grant will be presenting at the Office DevCon conference to be held at the Sydney Microsoft office on 15-16 Nov 2008. Office DevCon is a free community driven conference about all aspects of Microsoft Office Devlopment.

This is a deep-dive session.

Data mining has moved from the hallowed halls of long bearded professor types to today where literally anyone can dabble with some serious data exploration. Come along and learn how to take data in Excel and utilizing the new Table Analytics (Data Mining Excel 2007 Add-In) and really understand the behaviour of people like Dick and Jane. How are your customers similar? What customers are different? Basket analysis - is there really a relationship between Nappies and Beer?

Find out more here http://www.officedevcon.com.au

Event: Angry Koala at TechEd 2008

Sun, 01 Feb 2009 10:21:00 GMT

Below is a list of sessions that the Angry Koala crew will be presenting at TechEd 2008. TechEd is Microsoft's premier developer conference in Australia.

Grant Paisley

Report Authoring with Microsoft SQL Server 2008 Reporting Services

Explore the SQL Server 2008 Reporting Services Report Designer tool. This session is a walkthrough of the many enhancements in the new release. Learn about some of the new Report Designer usability features, how to add data visualizations with Gauges, build complex structures with Tablix, and build richly formatted Textboxs.

Shh data mining works in Excel

Shh don't tell your friends, but anyone can use the new data mining add-in for Excel. But if *you* do, you can impress your friend / boss / colleague / mum. I guarantee by the end of this session you will be a data mining guru, at least to your Mum. We will utilizeTable Analytics to find unusual data, troll through customer demographics to find what influences buying decisions, classify customers...

Glyn Llewellyn

SQL Service Broker in Action

Introduced in SQL 2005, SQL Service Broker forms the basis for many of the new features in SQL 2008. People are using it in a wide variety of applications from :- i) Distributed data gathering (retail stores trickling sales into Head Office) ii) High Availability &/or Load Balancing (every transaction is pushed out to DR & Reporting services) thru to iii) Spreading out Load Spikes. Come & see how your company could take advantage of this flexible technology.

Geoff Orr

MDX for a SQL dude (Demystifying MDX)

If your brain is wired for Transact-SQL and you're looking for a quick and dirty introduction to the weird and wonderful world of Multi-Dimensional eXpressions then this is the session for you. The syntax looks similar to SQL but does not work the same way. Cubes have far more metadata & MDX takes advantage of that. The session commences with an explanation of the basic concepts and principles underpinning the MDX language, contrasts it with TSQL and then proceeds to describe and demonstrate key syntax, MDX queries and some MDX scripting. Key Topics: Dimensions, Measure Groups and Members, How to do a join, How to add a condition, Where vs Filters. Perspectives, Accessing Properties, A few must know functions.

Rob Farley

TSQL Tips n Techniques: Improving Your T-SQL Arsenal

As the quantity and complexity of questions we ask of our data increases, database professionals need to be as proficient as possible with T-SQL. In this talk, see many advanced T-SQL tips and learn techniques to improve your T-SQL arsenal.

For more information about the sessions at TechEd see https://aunz.msteched.com/au/public/sessions.aspx

Event: Report Authoring in SQL 2008 Reporting Services

Fri, 30 Jan 2009 09:32:00 GMT

Grant will cover tips learnt building reports in SQL Server 2008 Reporting Services, including a walkthrough of the many enhancements in the new Report Designer tool. Learn about some of the new Report Designer usability features, how to add data visualizations, build complex structures with Tablix, and build richly formatted Textboxes. You may even see sperm!

For more details see http://www.sqlserver.org.au/events/ViewEvent.aspx?EventId=340

MOSS and BI Presentation at SBTUG

Tue, 20 Jan 2009 04:26:00 GMT

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

Tue, 20 Jan 2009 04:14:00 GMT

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

Fri, 30 Jan 2009 09:38:00 GMT

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

Fri, 30 Jan 2009 09:39:00 GMT

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

Fri, 30 Jan 2009 09:48:00 GMT

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

Fri, 30 Jan 2009 09:34:00 GMT

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

Sun, 01 Feb 2009 11:13:00 GMT

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