If you showed a business analyst or finance guy how to manipulate a whole year’s worth of data in Excel they’ll probably buy you lunch. PowerPivot for Excel does just that, but before you cash in, you need to be aware of how it works its magic and understand why it compliments rather than replaces your existing investment in business intelligence.
First of all having this power in Excel can be seen as a two edged sword..
However PowerPivot functionality is also built into SharePoint 2010, enabling the designer of the PowerPivot to share his work with his colleagues, by posting it to a special PowerPivot gallery...
What’s clever about this is that they can quickly slice and dice the PowerPivot but in a browser…
PowerPivot has other really useful features..
a management screen showing PowerPivot Report Usage
a management screen showing PowerPivot Report Usage
However it is not the total cure for all BI ..
I see PowerPivot as a way of letting the business concentrate on the analysis and presentation of data, in order to meet an immediate need. This leaves the technical team to concentrate on providing good clean data, and to incorporate some of this tactical work in PowerPivot into new & existing enterprise/strategic BI projects as appropriate.
To learn more about PowerPivot, simply go to it’s own special site..
Report Builder 2 in SQL Server 2008 allows for some collaboration on report design between the business user and the IT professional, but this is limited to a working on a whole report. A possible scenario would be that the IT guy would create a data source and a query (dataset) and then hand this over to the business user (btw Microsoft call these people information workers) to complete and maybe this user would then come back to you for problem resolution or because they need more fields on their report and they don’t have the skills to do that.
Report Builder 3.0 in SQL Server 2008 R2, improves on this by providing options to share parts of a report such as the tables and matrices (aka tablix in SQL Server 2008 onwards), charts and the graphics that give reports a corporate look and feel.
You can then consume these from a report part gallery..
..which hides behind the properties tab on the right hand side of Report Builder 3.
Up until now in Reporting Services we’ve only had the option to have shared data sources. Anew thing we can now do for our users is to provide them with shared data sets which are shared differently to the other report parts, possibly because it is something we would do as the IT Professional. My two key reasons to look at these would be..
1. You the IT guy can get the query exactly right for a set of reports and then share it with those business users as easily as creating a normal data set form a shared data source 2. You can cache the data sets on an ad hoc or scheduled basis
1. You the IT guy can get the query exactly right for a set of reports and then share it with those business users as easily as creating a normal data set form a shared data source
2. You can cache the data sets on an ad hoc or scheduled basis
If any of this is of interest I have a step by step guide on this for you to follow you’ll need the SQL Server 2008 R2 November CTP. Alternatively you can put your feet up for 7 minutes and watch me show you how to this in a screencast on TechNet Edge!
Is SQL Server is only as good as the operating system – discuss
The ability of SQL Server to make use of underlying hardware is often held up by the operating system, or to put it another way, pretty much as soon as there is support for some new thing the SQL Server team have a version that uses it. If we go back to SQL Server 2000 , then it wasn’t too long after windows could run on intel’s shiny new itanium processor that SQL could too, and it was the same for the subsequent generations of AMD & Intel x64 CPUs.
Windows Server 2008 R2 can now support 256 cores (the previous limit was 64), and hot on the heels of that is SQL Server 2008 R2 which can also do that. There isn’t too much hardware out there yet that even breaks the 64 core limit, but I did find a man who has such a rig, Henk van der Valk from Unisys. He runs a query from hell on it against a SQL Server 2008 R2 database on a 96 core Unisys E7000, just to see how fast he can do stuff!
I was impressed so I grabbed the film crew and made a quick five minute video on TechNet Edge which you can watch here.
BTW Henk has worked on a lot of the performance tests on SQL Server such as the ETL World record and this guide on tuning Integration Services.
My parting thought – How big does the server have to be before the perception that SQL Server doesn’t scale finally dies out?
Report Builder 3 is built into SQL Server 2008 R2 report manager and is a click once application. It is also available as a separate download in the feature pack.
At the time of writing the November ctp is the latest verison which can be downloaded from here, and the relevant feature pack is here
Shared Report Parts
Before you start this example you’ll need to create a report with stuff in that you want to share e.g. charts , maps and tablix (the all in one replacement for table and matrix data controls in SQL Server 2008 onwards).
Having done this go to the main menu in Report Builder…
and select Publish report Parts…
I’ve gone fo rthe review option and now I can see what I want to share..
notice that some things (shared datasources) are greyed out, because they are already shared, I’cc come back to that later.
so now I can publish and be damned, but ne aware that before I did this I set the options in Report Builder to remember my report server and to publish the report parts to a specified folder..
So now those components are in that folder. Notice too that I took the trouble to change their names from tablix1, map2 to something maningful before I did this..
To use them in a new report you need to find them and you do this from the report parts gallery by selecting it in view or clicking on the tab behind properties on the right hand side of the design surface..
You will need to connect to the report server and then click on search (you don’t need to enter a search term) to bring up the report parts available as I have done in the screenshot above
Now you can drag them on to the design surface and then simply run the report.
However what if someone changes a report part you are using in your report? You can elect to use the new version or not by first checking to see what’s changed, by selecting the check for updates option on the main menu..
You’ll get a warning telling what’s changed..
and clicking on view updates will show you the details..
Now you can select update to use the latest version or simply decide not to.
Shared Data Sets
For shared data sets you either..
Select New from the main Report Builder menu..
and from the wizard select Shared Data Set
or select an existing one and open in Report Builder to edit it.
From the manage option above you can also cache it and select a schedule to keep the cache as up to date as you need..
so a powerful way to store one set of data on the Report server which can then be consumed in many reports using this shared dataset.
A Business Intelligence project can often run into the sand because of data quality issues and tools like PowerPivot and Reporting Services will only highlight these problems back to the business,. These quality issues aren’t simply about keying errors they relate to the reference data that is stored in multiple places in many systems.
An obvious example is the many versions of a customer that exist across these systems e.g. the marketing system have an address where they send out the catalogue, but this is different to the billing address in the finance system. However while this may well need to be fixed, it isn’t killing the business in that bills are being paid by customers even if the odd catalogue is being mis-mailed.
Anyway my point is that this reference data exists in several systems, and fixing this in the data warehouse is OK for reporting but doesn’t resolve issues that can occur in production. Also this kind of problem is a business process issue, rather than being of a technical nature. Having said that technology can certainly help and this is where Master Data Services in SQL Server 2008 R2 comes in.
The new release will provide a portal where end users can manage this reference data..
At this point I would like to hand off to a real expert on the subject, Donald Farmer who I interviewed at SQL Bits a couple of weeks ago. I’ve put the video on TechNet Edge so over to you Donald..
If you want to know more, there is also part of the main SQL Server site dedicated to Master Data Services and when I found where all the sessions of SQL Bits have been posted to I will also let you know.
On the MSNBC RedTape site there’s an article, 12 Things Computer Should Fear in 2010 and coming in at number 10 is Windows 7 (not sure if the list is on order). One of the comments is that because Microsoft has worked so hard on compatibility then 8 of 10 windows viruses will still work. I doubt this is a comparison of XP to Windows 7.
So here’s some healthy Christmas cheer to counter the gloom:
1. If there threats out there for windows 7 out there now, then Bob would be writing about them , and the story would be all over the Register, Slashdot FARK etc..
2. Microsoft Security Intelligence Report. This graph shows infection rates reported to Microsoft across the various version of Windows to June 2009..
which is why Windows 7 and Vista sp2 aren’t listed. However my point is that any version of Vista is under half the rate of any version of XP. I should mention that this graph is also independent of the relative numbers of each version out there i.e. the ubiquity of XP is not a factor in its higher incident rate.
3. Keep up to date. This might be patches to the OS, anti virus signature updates, and regularly backups, and don’t get me started on all the people out there still running IE6!
4. I try and avoid being beaten up by not hanging round dark alleys in big cities, and I am very careful about who I let into my home. I do the same stuff when I’m online, and I think my fellow surfers are also more ‘webwise’ and sites like Microsoft, the Beeb, Trend are generally good at getting the word out early if there is a real threat.
5. Use the cloud to backup critical data, e.g. SkyDrive, Flickr and the other digital vaults is a way of protecting your data and getting it back after a disaster befalls a machine.
6.Try 64 bit Windows 7, which can be a pain occasionally (usually getting the right drivers for legacy stuff), but has even less reported threats than it’s 32 bit client cousins, possibly because it’s rarity, and because some 32 bit threats won’t work
So I am not saying don’t be cautious, but I am saying there’s no need to panic if you are getting a shiny new windows 7 machine for Christmas.
Hopefully you have got where you need to be in this awful weather and are going to have a great Christmas.
I took along a Microsoft RoundTable to the EcoCamp I was at over the weekend which sparked off a conversation on the value of working from home.
I had always assumed that home working was environmentally sound, because of the savings in travel. However one of the discussion group Jim Hensman at Coventry University has tried to quantify this for the university and noted that there may be higher energy usages caused by individuals heating their own homes and working in them rather than working together in an office. Some home workers at the camp reckoned their energy bills had doubled because of home working.
My thoughts after this talk were:
Jim himself recognised that measuring any of this is impossible as we don’t have the necessary raw data.
Clearly some types of workers can’t do this e.g. the lecturers at Coventry University have to be there to lecture to students and take tutorials. On the other side of things tools like Live Meeting and the Roundtable device (know known as the Polycom cx5000) make perfect sense in cutting down foreign travel.
Jim’s research on home working highlighted two other issues:
I feel the challenge is to overcome these prejudices where the environmental and economic benefits to employee and employer of home working can be established.
This is because the technology for remote or home working is easy, it’s the cultural shift that’s hard. For example at Microsoft, we have the tools in house to enable home working..
More importantly the UK MD, Gordon Frazer, has addressed the cultural issues by simply setting out a policy which essentially means we only travel to meet customers. This is not only ensures we hit our aggressive carbon targets it means we are getting through the recession by keeping variable costs to a minimum like expenses.
Therefore as someone who rarely goes to the office:
I could go on endlessly but my point is that we need to do more remote working where it makes sense to save energy, and deal with the cultural issues, because actually this isn’t the new world of work it’s the medieval world of work where the majority of work was done locally.
The best thing about my job is the interesting people I get to meet and this weekend was no exception, because I went to an EcoCamp in Coventry and met an arctic explorer and some amazing industrial designers amongst others. You might wonder what that has got to do with my work? Well strictly nothing, but even though I work for Microsoft I am an inhabitant of earth and actually Microsoft really does care for the environment and is also at COP15 (the Copenhagen Climate change conference).
In my opinion one of the biggest problems with the environment is that most people simply don’t understand the urgency of the situation or the severity of these outcomes for each of us. So I went to this EcoCamp, not so much to speak but to listen, and one of the biggest debates was on exactly this issue.
Microsoft is trying to raise environmental awareness in a number of ways, and perhaps the most interesting evidence of this is around spatial data:
The background to these is the commitment to academic research that is needed to fully understand the inevitable rise in global temperatures, on the planet and human society, such as this research by Microsoft Research Cambridge on Computational Ecology and Environmental Science.
I realise this can all sound like gloom and doom, but the individuals and organisations and even countries that adapt now will be the ones that are best equipped to survive these changes.
Finally thanks to the EcoCamp organisers for a very informative event
Green IT is about saving power in offices and data centres, so my definition of brown IT is about making the best use of the existing kit you have. This is good for two reasons, it saves you money as you don’t have to buy new hardware, and it saves vital resources.
You will see loads of stuff telling how energy efficient all the latest laptops and servers are but the materials need to make a 2Kg laptop weigh in at over 1200Kg the chief culprits being the chips and circuit boards. Another way to look at this that only a quarter of the power used by a laptop is used while it is running, the other three quarters are used to make it, and this doesn’t consider the cost of disposal. So the environmental benefits of replacing hardware less often outweigh the power savings by upgrading to the latest gear.
This is why I would say Windows 7 is better for the planet, either you can repurpose old kit to run it as is or perform a modest upgrade.
Of course upgrading the software can be hard, and in my village they don’t have software assurance with it’s shiny desktop optimisation pack, they don’t have Windows Deployment Services , they haven’t got System Center etc. they’ve got me!
I used to dread doing this sort of thing because consumer PCs are just so varied,possibly reflecting their owners! But it is all just so much less hard work than it used to be with XP - you may not remember how much fun that was to upgrade from Windows 95/98/SE, but I do!
A good recent example is my neighbour Chris. He’s got an old Dell running XP with an after fit Hauppage TV tuner, web cam, an old HP printer etc, This all just quietly upgraded to Windows 7 while we had a glass of wine and tried to play Beatles Rock Band. Of course he’s got to reinstall all his software, but actually that’s a great opportunity to have a good old clear out, and apart from a copy of Office, most of his software is bought online. He’s kept the keys and passwords for these other applications (which is essential for disaster recovery) so he just pulled down the latest version of each one and he was all set.
So if you didn’t feel like holding a Windows 7 launch party, maybe you could keep yourself in beer and nibbles over the festive season by hawking your upgrade expertise in your neighbourhood, it certainly works for me!