Databases and Reporting Sydney Australia

Excel 2010 Addin – Business Intelligence for the end user

2010-07-29T00:18:00.001-07:00

This is totally awesome…
Code named Gemini, PowerPivot is an addin for Excel 2010 which gives you BI out of the box.
There's still the need for a decent data warehouse, so Database guys like me will still be required.
Given the nature of most data out there, this is both great and dangerous at the same time.
What's really compelling is the ability to suck in data from the web to compare your current data with.
There's also the PowerPivot server option in SharePoint 2010.
Note: You need to install SharePoint 2010 and not configure it and then install Sql2008R2 to let the magic begin.

Most people will only install the 32 bit version of Excel 2010 - the 64 bit is for those business analysts using PowerPivot to the extreme
..as long as they have more than 4G of RAM on their desktop.

Regards,

Tom BizannesExcel 2010 Addins

Reporting Services 2008 custom template

2009-09-14T17:39:00.000-07:00

You can create a custom template by editing a report and placing it in one of the following locations depending if you have a 64 bit or 32 bit pc.

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Then to use this template you right click on the reports menu and select new item.

What one would do is to have a nicely formatted header and footer with page numbers etc.

The only problem is that the wizard is nice in setting up your table or matrix.

It just doesn't allow one to have a default page of A4. Instead it defaults to the us letter sizes.

Sydney Sql Server User Group 9th September 2008

2008-09-09T19:02:00.000-07:00

Quite an interesting night based around Sql Server Reporting Services 2008…..

The pizza was plentiful…Cholesterol levels rose quite a bit last night.

Grant from Angry Koala, gave away some of his little koala bears for answers to some of his questions!
Interesting Marketing ploy!
Maybe MacroView needs to give away a lot more cubes!

The exciting news is that in October a SQL Server 2008 feature Pack will come out with lots more goodies such as report builder with a nice office 2007 look and being almost the same as the report designer!
Business users should get exciting.
There will be some other exciting new extras in this so will let you guys know…..
Hopefully will go through all this in the sql code camp in October etc…
There should be a lot more feature packs so that nice enhancements can be delivered faster and easier….

The new report designer is much nicer and changed quite a bit.
The tablix controls means smarter simpler and more powerful reporting without code gymnastics!

Big reports run smarter – eg. Only render the first page so they won’t bring the server to it’s knees!

Also the outputs to Excel are better along with the new output to Word. Nice!!!
Apparently one of the uses is to generate and send monthly invoicing etc….

The rich text stuff was only briefly flashed on the screen, but everything is so much nicer that you won’t want to do anything with the “old” stuff…

Two tips of the day:
1: Setting the interactive size for height and width to zero means it will fit the whole screen – e.g. no next page stuff.
The page size settings apply to printing…..

2: Name all reports starting with a number e.g. R001 – New Prospects.
That way every time a client ( or internal user ) calls about a report, it is really easy to figure out which one.
And what with the long names you use, many users will also get to know their favourite reports by number as well!

Coming up:
Next Wednesday at Microsoft, Adam Cogan at the .net user group will go over all the admin stuff..
I might go along and keep him in line and teach him a few tricks to do with incorporating this into SharePoint!

Also SQL Code Camp in Wagga Wagga is coming up this October so it should be quite interesting…

Regards,
Tom Bizannes
Microsoft Certified Professional
Databases and Office Development
Sydney, Australia

Integrating Reporting Services with SharePoint

2008-05-15T20:34:00.000-07:00

Had fun trying this on a windows 2008 box.
Everything worked after hitting each bit a few times!
So important to get permissions set, root site collections set properly and IIS7 configured right before installing anything.

So the steps in order are:
Set up IIS7 with all the correct settings:

Common HTTP Features:
Static Content
Default Document
HTTP Redirection
Directory Browsing

Application Development:
ASP.Net
ISAPI Extension
ISAPI Filters

Security:
Windows Authentication

Tools:
IIS Metabase Management
IIS 6 WMI Management

Then make sure you set tcp and named pipes in the sql server surface area configuration tool for the remote connections.

Then install service 2 for sql server and the service pack for the service pack if it isn't up to version 9.0.3054

Then run the sharepoint reporting services msi file that integrates them noting this doesn't run on a windows 208 server unless you install a reg hack to run as admin or turn off the uac.

Then you need to go into the reporting services configuration tool to configure and that is where many get mixed up.
Reporting Services needs to use the same pool as sharepoint to integrate.

Everything finally fell into place except the reporting services configuration settings didn't show in the sharepoint admin application page.
Had to go to the _layouts pages instead.

When deploying your reports, the pc with report designer should also be service packed and then you get an extra option regarding sharepoint in your deployment options wording for your target folders etc.

http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx
http://servername:portno/_layouts/ReportServer/ManageTrustedAccounts.aspx
http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx?Settings=RS

Regards,
Tom Bizannes
Sydney, Australia
For Consulting click here:

Windows Server 2008 was released to manufacturing today along with Windows Vista SP1.

2008-02-06T02:37:00.000-08:00

Now all the fun updating etc….

Think I’ll wait and see how many people have issues!

Windows Server 2008 was released to manufacturing today along with Windows Vista SP1. Good stuff.

Don’t you love how they’ll give everyone until April before windows update will clobber their pcs anyway?

Installing SQL 2005 on Vista or Windows 2008:

2008-02-06T02:33:00.000-08:00

There have been some installations with sql 2005 without sql 2005 reporting services due to not adding some components in windows 2008 or vista.

Installing SQL 2005 on Vista or Windows 2008:

And any installs on Windows 2008 Server or Vista PCs won’t install reporting services 2005 unless you make sure you have added all the required internet components, not just IIS7.

You need IIS 6 compatibility, plus a lot of other components so read this article:

http://support.microsoft.com/kb/934164

You need to run the install as admin.

Install SQL server 2005 then the Sql Server 2005 sp2 and then the vista provisioning tool and Visual Studio sp1.

** All fun but easier than the alternative to uninstalling and then do the steps above and then re-attaching all databases….just because an install missed one step.

Save the pies for dessert!

2007-10-11T18:33:00.000-07:00

Microsoft SQL Server User Group 5th October 2007

This was very interesting from a Reporting / Dashboard point of view!

Time was also convenient as Microsoft has just released Performance Point Server, their Hyperion/Cognos/Business Objects replacement product!

Stephen Few has two books out – one called “Show me the numbers!” and another called “Information Dashboard Design”.
I am definitely leaning towards getting his latest book …

He was a manager of a Business Intelligence team but wasn’t happy with a lot of standard charts and tables produced as they only served to confuse top executives!

So he quit to work on finding better ways to communicate with graphs and tables!

Most modern charts might look fancy but they don’t often help decision makers or anyone looking at them to really glean information.

He walked us through many examples of ones entered by software vendors in competitions plus from the software user documentation and in most cases showed how they weren’t effective in telling the story.

So it appears many people mislead without knowing as opposed to the old days when people lied with statistics.

He mentioned a book called how to lie with statistics and how this was a best seller!

Some vendors have some amazingly confusing charting features which are up there in the stratosphere for confusing people!

There was a charting guy called Edward Tuftie back in the 80’s and most of his ideas still hold in today’s reporting but very few use them.

This was more about what not to use versus what to use..

e.g. 3D charts look good but the human eye cannot work out the shades and angles.

Often tables can convey information better than charts.

Vertically orientated labels are very difficult to read.

Tables are good for individual values or mixing different types of data e.g. % and $...

Fancy terms like spark lines and blink graphs were thrown around…

Small multiples was a concept by Edward Tuftie in 1983…

This involves breaking up one chart into many charts.

E,g, Sales Per Month for different products.

Breaking this up into a chart for each product all on one page.

E.g. if you have 8 products then break this up into 8 mini charts!

That way one can glance at the figures to better see which product / products need attention etc.

But be careful as most people can only remember 4 chunks of information at a time!

10% of men cannot distinguish between green and red!

So often using shades of the one colour is better than having a traffic light approach!

He does this one day training and gets people walking out and making reports so much more informative!

At the end of the day a Dashboard should highlight what’s important.
Although in a discussion at work it appears that company annual reports use alot of pie charts as they don't want to easily show any anomalies etc to their readers!
So you are not always required to make numbers pop out of the page for decision makers ....

Some tools to investigate include:

Juice Analytics – used to have a free excel addin.

MicroCharts for Excel – bought by XLCubed? And there is a product called Chart Tamer?
http://www.xlcubed.com/en/Products_XLCUbed_MicroCharts.html

Tablo and Spot fire were also mentioned

When I did some checking on Juice Analytics foud this article which shows one of the silly things people do with charts that can easily mislead.

http://www.juiceanalytics.com/writing/2007/06/excel-2007-and-lie-factor/

Found MicroCharts at BonaVista?

http://www.bonavistasystems.com/

Tom Bizannes is a SQL Server Professional in Sydney Australia.
http://www.smartbiz.com.au

Performance Point demo at .Net User Group Wednesday 19th September 2007

2007-09-19T18:51:00.000-07:00

The turnout was smaller than usual!

Maybe the topic was more suited to the SQL Server user group!

First the news then the demo on Performance Point which will mean more business for companies who write cubes!

Office 2003 service pack 3 is out!

Office DevCon is coming at Microsoft over the weekend of 3 and 4th of November.
Bit like a code camp

Adam couldn’t stop talking about facebook and how much time he spends on it.
They even track how long staff use facebook as he is worried they might get as addicted as he is.
Simple things like seeing who has the fastest broadband connectivity or who has travelled the most etc…..

Cool sites were mentioned like trailfire.com where you can make a trail of your searching along with comments.
Useful? An example was looking up info on css and seeing the trail to sites with good info.
One could also use this to make a trail of places you are going to see…Good for travellers?

There’s a free High Performance Computing event this Friday at the MLC Centre.
This is by Frank Chism, world expert in high performance computing, discuss the future of cluster server technology
Frank admits to "over 40 years in HPC" and is "not looking to leave any time soon because I couldn't afford the cool toys I get to play with if I have to buy them myself."

Andrew Coates from Microsoft also talked about the OpenXML standard going to ISO…
There are some links regarding this and what was funny was how they even held events in New Zealand to explain this in more detail but the Kiwis rejected the proposal and we discussed is that because they are so anti-american?
The OpenXML standard apparently is more flexible than ODF and standardises even the older Microsoft office formats.
For more information goto:
openxmlcommunity.org/
openxmldeveloper.org/

Those who follow second life will like the inline protest of ibm staff against the 50 virtual offices that ibm runs as the profit has gone up but their bonuses have gone down!
workers-shape-up-for-big-blue-with-ibm

techcrunch.com/ - IT news – has rss feeds….

iScrybe for online calenders was mentioned as worth taking a look

They explained that SSW workers only do things if they get an email…talk about an inbox nightmare!
They didn’t want to use outlook as it was a problem with people coming and going…..
So they asked a business person to come up with a way to email monthly/quarterly tasks to staff and Google Calendar got a good wrap as it allows sending to many and can be shared easily.
You can even write code to talk with Google Calendar.

What I found perplexing was they mentioned SharePoint but then dished that as not be easy to set up re-occurring tasks!
From my view point their requirement is a little crude as one likes to know if a task has been completed and this didn’t seem to be their business requirement!

Performance Point:
There are three different parts to this.
M A P
M – Monitor / KPIs – based on Business ScoreCard Manager…
A – Analyse – They bought pro-clarity and what a Kool tool! ….This alone is worth it!
P – Planning (Planning/ Forecasting/Budgeting etc) from BizSharp – have yet to see anything about this but the business users like this part!

The main competitors are: Business Objects / Cognos and Hyperion.
At about 20K USD and $200USD per user, one of the guys from CBA where they use this said that was a good price.
Business Objects drill through isn’t as good…
Cognos is used by many in a similar way that reporting services is used….

There are guys at Microsoft who only deal in one of these three areas..and Adam only walked us through a canned demo of the first two.

So this is the new Hyperion Killer – Hyperion is a great tool, Ajaz enabled and using Bindows.net which is a very cool bit of Ajax technology that has been around for a few years.

Performance Point should lift the demand for SQL Server cubes being built although it can query normal odbc sources….
It works with SharePoint and was annoyed at Adam’s comment that you need Moss when it states either wss3 or moss!
It even states Office 2003 in the requirements…
This will be released soon so we should see the final specifications then.

The target market is decision makers and Ceos and Operational Managers will go gaga over this!

Regards,
Tom Bizannes
Sydney, Australia

SQL Server User Group last Friday 9th February 2007

2007-02-13T03:06:00.000-08:00

The pizza was hot and the beer was cold….which is better than the other way around!

There was mention of SQL Server 2005 Service Pack 2 being released this week with some extra in adding data mining and profile via excel!
Apart from the long waited fix for Reporting Services, this should be a welcome update.

Warren Thornthwaite (co-author The Microsoft Data Warehouse Toolkit)
spoke about Dimension Modelling and gave us some good tips
and a very quick walkthrough what is normally a 4 day course.

He suggested for those into Business Intelligence/Data Warehousing to get on their Design Tips email list.
Go to the Kimball Group Web site http://www.kimballgroup.com/

His emphasis was good and the approach I like…
He said that most of the Data Warehousing and Business Intelligence effort should be based on finding out what the users do.
Not necessarily asking what they want as they most often don’t know!
Warren also did not believe in doing Business Intelligence demos, as the users will only see a small picture of what is possible.
Data Warehousing and Business Intelligence can greatly improve a company’s reporting which in turn affects their bottom line. This is hard to demo!

Basic approach is to de-normalise dimensions for usability and normalise facts for performance.
Modelling is best at the lowest level of detail then everything is more flexible.
Dimensions come from asking who,what,when,where,why and how. E.g. Product, Date, Client..
He normally uses a star schema.
Biggest bottleneck can be standard naming across an organisation.
e.g. Area versus region. But if this are not the same, then there is a case of having both fields available.
The problem comes when different divisions talk about their report by area verus the other divisions report by region!
Or maybe the same name is used for different reasons. E.g., Region  there might have to be a Sales- Region versus a Marketing-Region.
As he stated, very few problems are technical, most are political.

Warren said he has seen Data Warehousing/Business Intelligence work on every platform and also fail on every platform.
His formula is to deliver value to the business as quick as possible rather than do the “easy” things first…

He normally draws up a Bus Matrix (Business Matrix) and mentioned a spreadsheet tool in his book, which they use to document and even start create the Data Warehouse/ Dimensions!

This matrix has processes/value chain items down the left and the dimensions across the top as multiple divisions will want to access the same dimensions.

The one tricky item but most important is slowly changing dimensions and how there are catered for.
Whenever someone did not cater for this initially, it was a nightmare to incorporate, so he always does these at the beginning.
E.g. A customer changes state so the previous sales figures per state will change unless this is catered for…
There were several approaches including do nothing, create a new dimension when a attribute changes etc.

He discouraged snowflaking and normalising the model and that with SQL 2005 the case for snowflaking is no longer there.

Other fancy terms were thrown around such as Junk dimensions for putting all those little lookups into one dimension – but be careful how many are added!

Also many to many dimensions – bridging tables or in SQL termes – intermediate fact tables.

A lot of value in Data Warehousing/Business Intelligence is the ETL Process – (Extraction, Transformation and Loading Process)
In addition, initially you mirror the Relational Model in Analysis services.

All in all his book sounds compelling although still have to finish and build all the projects in Practical Business Intelligence with SQL 2005 which is a great book to get you up to speed on Data Warehousing/Business Intelligence. It included the same concept of surrogate keys as Warren suggested….

Regards,
Tom Bizannes
Databases and Business Intelligence
http://www.smartbiz.com.au

2006-08-02T20:50:00.000-07:00

Business Intelligence "lights up" with Office SharePoint 2007 was the topic.

Sydney SQL Server User Group meeting on Tuesday 1st August 2006

This must have been their most packed session to date!
Possibly 100 people there and the lecturette was packed!

There was no pizza this time, instead they had sub-way or some similar type of rolls
– These weren’t too bad but how long will the healthy food keep coming?

One event coming up is SQL Code camp October 7th and 8th in Wagga Wagga at the Charles Sturt Uni.
This is funded by Microsoft but accommodation and food is up to you.
http://www.sqldownunder.com/CodeCamp/tabid/53/Default.aspx

Grant stepped us through using Excel 2007 and how it works nicely with Excel services.

Microsoft appears to be gearing Excel 2007 as the new tool to extract and view your database/cube information.
And it has a lot more smarts built in than the earlier versions of excel with the cube add-ons etc.

Also the new SharePoint ties in all your business intelligence a lot better and easier than ever before!
There is going to be a major stampede when this product is released for this aspect alone.

I liked the one point grant raised but Business intelligence – You can make good decisions, wrong decisions and bad decisions, but what is the difference between a wrong decision and a bad decision? The answer was a bad decision is made when you haven’t got enough information!

The main point here is that some organisations may have a few people running office 2007 just so they can publish the “server based” excel workbooks etc.
Others can read and input parameters to generate different results via a web browser interface.
So this might be a great way to publish excel like reports with charts and other interactive features to key groups of users!

The KPI options in SharePoint lists look cool and will probably be demonstrated over and over again.
There was also the bars inside a columns with different colours based on the values using some standard deviation formula to show more reddish the lower end and more greenish the upper end.

One trick with Analysis services is not to include fields that you won’t need. It is too easy to select everything from all your tables etc.
Fields like phone numbers and names are probably only important when you drill down, and these should be excluded from the cubes and be shown on drill through via a SQL query rather than via the cube.

Another nice to have feature was the ability to drill down in the same column. Means the columns to the right with the key values don’t get pushed off your screen to the right.

The standard dashboard template was great and was shown linking to an excel services file as it was difficult to find and set the right permissions to analysis services etc.

The new SharePoint Server and SQL Server 2005 each have a very tight security to deploy and will require knowledgeable people to configure anything and everything as access is turned off for just about everything.

Regards,

Tom Bizannes (B.Business,MCP)
Microsoft Certified Professional
(Microsoft SQL Server Database Administration and Design)
MacroView Business Technology
http://www.macroview.com.au
Microsoft Gold Certified Partner
Phone: 61 2 9249 2700 Fax: 61 2 9279 4111
"Solutions in SharePoint and Office"
2005 Winner - MSD2D Readers Choice award - Document Management

Microsoft SQL Server User Group Sydney

2006-05-07T03:39:00.000-07:00

Tuesday 2nd May 2006

Well the pizza was hot and there was plenty of it.

Dr Greg Low presented and his depth of knowledge is very good.

There were no new announcements except to say to look out for SQL down under podcasts.
http://www.sqldownunder.com/

They mentioned fun with installing sp1 for sql server 2005. One trick was to stop the services only at a particular point. Will need to try it to see how easy/difficult it is....

Again we here another reason to upgrade to SQL Server 2005. This time it was because stored procedures will run faster as they do statement level execution rather than recompling the whole batch.
This means, less memory is used, faster execution and less compile locks.
This also means you don;t need to break stored procedures up into small ones to run faster, as has been the "workaround" in SQL Server 2000...

There were a few interesting articles to look at regarding this, if you want to write better stored procedures. Names of these authors were Arun Marathe and Tibor Karaszi so will need to Google these guys for their SQL articles.

Some tricks we were advised about were:
keeping naming identical even case sensitivity is important as well and spaces etc.
Strong naming was also raised as a good way to call your stored procedures, saving it looking in the master, then for the current user and then for dbo. Also don't start stored procedures with SP_.
e.g. exec proc1 will look for username.proc1 and then dbo.proc1
and sp_proc1 will look for sp_proc1 in the master database before looking for username.proc1 and then dbo.proc1. So the more you qualify the name the better.

Then we had a general session and talked about various things:

One tip is to use checkdb shrinkfile rather shrinkdb for your sql server 2000 databases etc.

Pro-clarity was bought by microsoft and they have a BI client and also work in excel? Will need to investigate further.
http://www.proclarity.com/

Mendocino will be called duet and this is a SAP connector tool...
http://www.eweek.com/article2/0,1895,1956671,00.asp

Tom Bizannes
Microsoft Certified Professional
http://www.macroview.com.au