MCITP Business Intelligence Developer 2008 Certified! Passed 70-452 (71-452)

Just checked my email this morning and last night I got the email results from taking the 71-452 PRO Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008 test and I passed!  Here was the official email from Microsoft Certification program:

Congratulations on earning your Microsoft Certified IT Professional: Busines Intelligence Developer 2008 certification! We hope you enjoy the benefits of your certification and of membership in the Microsoft Certified Professional community.

If you are planning on taking the test I would recommend reviewing the preparation guide that you can review here.  You can also take a look at my feedback in regards to the exam here. Just a quick reminder that this is not the same format as the SQL Server 2005 MCITP 70-446 exam and it is focused on the SQL Server 2008 new features.  Good luck!

So now that means that I am fully Microsoft BI Certified for SQL Server 2005 and 2008

Supposedly I could have just taken the upgrade exam, 70-455 – Upgrade: Transition your MCITP Business Intelligence Developer Skills to MCITP Business Intelligence Developer 2008, since I already have the 2005 certification.  Unfortunately I don’t see any details on that yet on the Microsoft Certification site.

Report Builder 2.0 RTM install and overview

Yesterday the blog posting came out on the Reporting Services Team Blog here stating that the Report Builder 2.0 RTM was available to download.  Some of the new features since the release of RC1 are the ability to open up reports on the server for editing and using shared data sources that are on the report server.

So I went ahead and downloaded the latest version and grabbed a few shots to show the installation process and to point out some of the features if you are not familiar with the new stand-alone report designer with the Office 2007 look-and-feel.  Click on the blog posting title to view the entire posting in full screen or simply click here.

	Here you can see what the minimum space requirement is	You can set the target server URL and you can see this later in the options section (it will default to http://localhost/reportserver)
The location in the start menu and notice that it comes with a help file too	The SQL 2008 Report Builder 2.0 splash screen	Here is the new report designer and notice the table, matrix, and chart wizards
Here is how you can access the additional options	shows the target URL for deployment and additional settings like being able to clear the recent files list	Includes a nice section of resources to reference and leave feedback
See the nice ribbon with the rich formatting menu access	Here is how you turn on the properties pane	Look at the nice snap-lines for aligning your report items
The report parameters get setup in the new data region pane	With the acquisition of the Dundas visualization code base there are now 58 chart types available	And you can access 21 different types of gauge report items too
Check out the new flexibility with the tablix functionality – multiple groupings/views of the data within the same matrix and subtotaling capabilities	And there is also a new rendering format to output to Microsoft Word – the CSV and Excel outputs have also been enhanced and embedded data regions are now supported in the Excel output	Getting back to the Report Builder 2.0 help documentation.  Here is a quick peek at the content and look at the nice tutorials that are available

Well I hope that this gave a good overview of the installation process and highlighted some of the new functionality with the new end-user report designer that is available.  This is an outstanding tool that end-users are going to really embrace.  The product team has really improved the functionality and improved the user interface.  So go out to the download center and check it out for yourself – Report Builder 2.0 RTM.

If you are interested in learning how the new tablix functionality works take a look at this virtual lab that is available online here – TechNet Virtual Lab: Authoring Reports Using SQL Server 2008 Reporting Services.

UPDATE (3/6/2009): For information about Report Builder 2.0 ClickOnce check out my posting here – Report Builder 2.0 ClickOnce and SQL Server 2008 CTP SP1 Experience.

 

BI Publisher for PerformancePoint Server (PPS) on CodePlex?

I was taking a look this morning at the forum postings in the PerformancePoint Monitoring & Analytics section and came across a previous forum posting link here.  I have had customers and colleagues inquiring about a process to help automate the deployment of PPS dashboard items between environments and it looks like there is already something in the works by Kevin Idzi and Deepak Mani of Microsoft called BI Report Automation Publisher.

So what can it currently do?  Well the current features include the following:

PerformancePoint (PPS) Publishing

Moves elements from one server to another, adjusting server specific settings for related elements automatically including:

• PPS Data Connections
• Proclarity Analytic Server Report Links
• Excel Service SharePoint Links
• Excel Service Parameters
• Add/Remove Credentials

Proclarity Analytic Server (PAS)

• Update report page connection properties to redirect to a different SSAS server.

Excel Services

• Dynamically change link to Office Data Connection file.
• Provide a seamless way to create a list of Links to Proclarity Analytic Server reports

Configurability

• Net configuration API is used, allowing flexible XML Configuration and validation.
• Command Line tool execution, Output sent to tracelistener and is configurable.
• Use MSBuild or WIX to inject properties into the Config file before execution, to truly integrate this in the build/deploy environment.

And they have plenty more features on the radar:

PerformancePoint Target Location Redirection

Currently, this tool moves PerformancePoint (PPS) Elements from the source to target retaining the same naming and structure as it came from. For some people, it might be useful to have the PPS elements moved with a suffix (or prefix) attached for side-by-side testing. And it might be helpful to have the linked reports be retargeted to point to different Libraries / folders within Sharepoint/PAS
This does not apply to Proclarity since the tool works directly on the reports. And it does not apply to the Excel Serivce direct updates since the target URL is included.

PPS Sql Server Reporting Services (SSRS) Report Support – Where is it??!

I have not worked with SSRS reports in my current work. If someone has and would like to work with me, we might be able to find a way to integrate support to allow redirecting the PPS Report’s target server.

PerformancePoint Input from BWSX Files

The tool currently reads from the source PPS Server. It may be helpful to specify a BWSX file in which the elements are read, and serialized into the needed objects to be used for updating and publishing.

Deploying PAS Reports

It might be helpful to have the tool actually take PAS reports from a source and deploy them on a target. In looking at this briefly, I have no idea how the Books are generated when sent to the server for an update.

Deploying PPS Dashboard

Currently, all of the elements are update, but if the dashboard itself is modified or components added/removed, it will need to be manually deployed. There are a string of Sharepoint Webservice calls when this happens to put the files at the target location and make it available. If those were deconstructed, it could be done.

Updating Post-Deployed PPS Webparts

Ever want to remove chrome on a webpart, or adjust the template externally (sharepoint site is secured from template modifications)? Then being able to adjust the webpart that PPS generates would be very helpful. Currently we are actually doing this as manual steps after we deploy (when we need to).

So this tool looks really promising and looks like a really nice way to move items between environments.  There is always the migration path that is listed in the documentation too that you can take a look at here.  How is everyone else promoting PPS dashboards between environments?  I would definitely like to hear what other people are doing.

Microsoft Business Intelligence VPC Release 7

So this past week I was at the MS BI Conference and finally got a copy of the MS BI VPC 7 release (July 23, 2008).  Still waiting on the actual publication of the files for the community wide download like releases 5.1 and 6.  The overview documentation has not been updated with the new items that were added, so I quickly went through and tried to pick out the new items and mark them below.

There were a couple of new demos that I wanted to point out in regards to IT and Hospitality.  The IT demo has a nice overview and service level dashboard to take a look at:

 

And the Hospitality one has quite a few pages and here is a screenshot of the executive dashboard page:

I just thought this one was interesting since I worked in the casino industry for over ten years and it is kind of neat to get other points of view on how people like to analyze the data and take a look at the cubes that were designed.

The VPC is still running on Windows Server 2003 and SQL Server 2005 and has a time bomb expiration of December 4, 2009 (same password as before – pass@word1).  Hopefully it will be available soon for everyone to download, so until then this will give you a sneak peek. 

If you are going to be using these VPCs I would definitely recommend running it on an external USB drive, give it about 1.5 to 2GB of memory, and when you are done using it close the VPC with the ‘save state’ option from the Action menu within the virtual PC (this will speed up the start time for when you want to use it the next time since everything will already be loaded).

UPDATE: If you are looking for the download you can check my more recent blog posting about MS BI VPC R7.1 – Microsoft Business Intelligence VPC Release 7.1

MCTS 70-448 Certified (71-448)

Just got the email results from taking the 71-448 MCTS Microsoft SQL Server 2008, Business Intelligence Development and Maintenance test and I passed! Here was the official email from the Microsoft Certification program:

Congratulations on earning your Microsoft Certified Technology Specialist: SQL Server 2008, Business Intelligence Development and Maintenance certification! We hope you enjoy the benefits of your certification and of membership in the Microsoft Certified Professional community.

If you are planning on taking the test I would recommend reviewing the preparation guide that you can review here.  You can also take a look at my feedback in regards to the exam here.  Good luck!

Now I am just waiting on the results from the MCITP beta exam 70-452 (71-452).

MS BI Conference follow-up

So I didn’t get a chance to blog about Day 2 yesterday and now the conference is over.  I was really disappointed that there were no talks or discussions about what is coming in PerformancePoint Server SP2 (which I have heard is possibly coming out maybe in Nov…).  Assuming that this will include full support for SQL Server 2008 and who knows what else.  I would have also liked to have heard about what the plans are for v2 of the product.  Anything would have been great unless I missed it some where along the way.  Improvements with the Analytical reports and getting the additional ProClarity Analytical reports full integrated would be nice with possibly some nicer charting capabilities incorporating some Silverlight or WPF visualizations would be great.  Maybe turning the PPS items into desktop components (widgets) would be a nice feature, but I suppose you could set them up in your desktop sidebar.

I finally got to see Mosha today during his ‘Got MDX problems? MDX Studio to the rescue!’ presentation.  I think he was a little hard on himself since he didn’t have any control over the room setup in his posting here – MS BI Conference 2008 is over.  I thought it was really helpful how he pointed out all of the capabilities in MDX Studio and the Analyze feature was very cool where it actually provides comments and suggestions along with links to his blog postings for reference.  Having a larger screen or a projector would have been very helpful though.

All-in-all the biggest news was by far ‘Gemini’ and the next release of SQL Server ‘Kilimanjaro’.  You can check out the videos of the keynotes on the MS BI main web page here –

Stephen Elop President, Microsoft Business Division 9:00 – 10:00 a.m. PST	Ted Kummert Corporate Vice President, Data and Storage Platform Division 10:00 – 11:00 a.m. PST
View Webcast: Low | High

If you want to see what ‘Madison’ is about skip ahead to 1 hour 9 minutes and for ‘Gemini’ skip to 1 hour and 27 minutes to see Donald Farmer’s demo.  If you want to check out the fairy tale story go to 1 hour 17 minutes.

It was a good conference with some decent technical seasons, just no PPS v2 discussion.  I did get to meet with the MS Virtual TS people that were at the conference, found out what the program is all about, and I got a copy of the MS BI VPC 7 release:)  As soon as I get it fired up I will post the changes in the VPC (the overview document still had the version 6 information with no updates).

MS BI Conference Day 1 follow-up

So the first day of the conference has come and gone.  So the big theme is ‘Thinking Bigger about BI’.  We want to be able to put information at people’s fingertips and minimize the amount of data across the enterprise.  We can make the impact through software to empower information workers providing them the necessary data to make decisions.  User adoption rate is very high using the Microsoft products because people have been using these tools for two decades now, so integrating BI products with the Office suite makes sense.  Reducing the amount of effort required to produce reports in order to make decisions is key.

Everyone that attended the keynote presentations this morning got the first public preview of Microsoft’s self-service business intelligence code named ‘Gemini’.  This is going to be what they called ‘people ready BI’ – BI that works for everyone and allows users to make decisions more quickly.  So when will we see these new changes?  Well they stated that a CTP would be available in the next 12 months.  In the first half of 2010 Microsoft is going to be releasing data warehousing enhancements code named ‘Madision’ which incorporates DATAllegro with SQL Server.  They will also release self-service BI ‘Gemini’ enabling people ready BI.

So the new release of SQL Server in the first half of 2010 code named ‘Kilimanjaro’ will be once again focusing on new BI capabilities and apparently the next release of Analysis Services which is ‘Gemini’ according to Mosha’s posting So what is exactly “Project Gemini”.  Project ‘Madison’ will allow companies to scale-out their data warehouses and enable SQL Server data warehouses of all sizes.  With SQL Server and DATAllegro this will provide massive scale (hundreds of TBs) with low TCO.  There was a demonstration of 24 instances of SQL Server with the controlling component monitoring the processing with a visual display of the core utilization of each processor along with the disk IO.  A query was run against a 150 TB data warehouse with MPP (massively parallel processing) that had a fact table (1 trillion rows) that was equally spread across the instances with the dimension tables replicated in each instance.  The query was able to process extremely quickly to generate the SSRS report performing the aggregation (not utilizing SSAS).

Donald Farmer did a presentation with the ‘Gemini’ Excel add-in.  He showed how you could pull data in from a source (data warehouse) and some of the tables had 20+ M rows into Excel which could quickly be sorted in sub-second response time.  He then added in some additional data that he added into a new worksheet to perform a mashup of data to analyze.  A PivotTable was then generated and it was actually able to create a model that inferred relationships between data with intelligent slicers for filtering.  Donald also added a theme to the spreadsheet and published to SharePoint.  The data could then be scheduled to be refreshed through SharePoint, there was a new operations dashboard that IT could reference in SharePoint Application Management, and based on usage could perform an upgrade of the ‘Gemini’ solution to PerformancePoint.  This new ‘Gemini’ technology was using column based in-memory storage.

For the rest of the day I focused on dashboards and PerformancePoint Server attending the designing effective dashboards, creating richly formatted reports, advanced dashboard and scorecard design, and the PerformancePoint SDK sessions.  Talked about Stephen Few’s visualization concepts (download the articles here – Common Pitfalls in Dashboard Design and Effectively Communicating Numbers: Selecting the Best Means and Manner of Display), about creating sparkline and bullet charts in SSRS, Soft Artisan Office components being integrated into the ‘Kilimanjaro’ release with SSRS, Alyson Powell Erwin went over quite a few of the blog entries that have been posted on The Microsoft PerformancePoint Team Blog, and Stephen Handy talked about all of the options that you could do with the PPS SDK (creating templates, wizards, data sources, filters, reports, etc.).

All-in-all a great first day and looking forward to day number two.  Stay tuned.

Deploying PerformancePoint Server (PPS) Dashboard Viewer web part to new Web Application

After you complete the install of PPS Monitoring Server you will go through the configuration manager wizard.  At this point one of the screens will look like this allowing you to deploy the PPS Dashboard Viewer web part to a SharePoint site collection.

So what if I need to deploy this to a different SharePoint Web Application?  If you go through the wizard again everything was been installed so you are not able to deploy to a different site collection.  If you go to the PerformancePoint Team Blog site you can follow the manual instructions to perform the install (How do I deploy the Monitoring web part on a separate SharePoint Web Application on the same SharePoint Web Server?).  I actually went through the steps and in the end I must have missed something because I couldn’t get it to work.  It seemed like there should be an easier way to do this.

So what I ended up doing was to modify the XML file that the configuration manager references.  I went ahead and removed the web part section and then walked through the wizard component again and clicked the install components button and deployed the web part to a different site collection.

Step 1 – go to the following directory to modify the MonitoringConfig.xml file %PROGRAMFILES%Microsoft Office PerformancePoint Server3.0ToolsMonitoringConfiguration.

Step 2 – open up the MonitoringConfig.xml file with Administrative privileges (so you might want to run notepad as administrator)

Step 3 – remove the <ConfigUnit ID=”WebParts” /> section in the XML file, save the file, and run the Monitoring Server Configuration Manager wizard again.

 

Step 4 – getting the web part deployed to new site collection

New site collection in new web application without the web part	Run the Monitoring Server Configuration Manager wizard and select ‘Add componenets’
Notice that the Dashboard Viewer web part is available now for deploying	Displays the database settings for the Monitoring Server, simply continue on to the next step
Select the new site collection that you want to deploy the web part to and proceed on with the configuration process	Dashboard Viewer web part is now available to be used

Well that sure was a lot easier then going through the manual process, but understanding what is going on behind the scenes is always important to fully understand how everything works.

Filtering PerformancePoint Server (PPS) KPIs

There are a few ways that you can filter the KPIs and it all depends on how you are creating them and want to use them in your dashboards.  In this posting I will discuss filtering KPIs created from an Analysis Service database source. 

So the first option would be adding the filter to the Actual and Target within the data mapping section.  So if you create the KPIs from measures within your cube and map the Actual and Target to a measures then you can add in additional filters like the following picture demonstrates:

Most likely you will want to name this KPI something meaningful because now it is being filtered, so the KPI is more specific.  If you have created Analysis Services scorecards using the wizard and filtered the KPIs this way, the wizard will actually create these KPIs in the workspace and reference them.  These KPIs will include the same type of data mapping filters as the one above.  Now I have noticed that if you reference an existing KPI and try to filter it through the wizard that this will not actually work because it won’t modify the existing KPI and it doesn’t create a new one.

Step 1 – creating the scorecard	Step 2 – creating KPIs from measures
Step 3 – setting up the KPI and naming it	Step 4 – adding in the filters
Step 5 – new KPI created in workspace	Step 6 – filter has been added into the data mapping by the wizard

So what if you don’t want to filter the KPI and want it to be more generic.  Then you can filter the KPI once it is in a scorecard by using a filter on the dashboard that is linked to the scorecard.  This is really nice since the end-user can choose how they want to view the KPI (for a particular date period, geography area, sales team, store location, etc.) – empower the users.  So down below here I have created a Sales Country filter that we will link to the scorecard that can be used to filter the data.

 

If this doesn’t work and you need to filter the KPIs by a dimension member, but they don’t want to see the filter.  Then another option would be to include that member in your scorecard above the KPIs and then right-click the member in the scorecard and select ‘Hide’.  This will turn that member color to red and when you view the scorecard in the dashboard it will not be displayed.  You will probably want to make the name of the scorecard meaningful so that the users know what they are looking at.  So down below here I will filter the KPI on the scorecard with the Sales Territory Country France member, hide the dimension member in the scorecard, remove the filter on the dashboard, and then preview the results.

Step 1 – drag the dimension member from the available items list and place it to the left of the KPI	Step 2 – select the specific member that I want to filter the KPI on
Step 3 – hide the dimension member on the scorecard	Step 4 – shows that the member is now marked as hidden
Step 5 – preview the results which are the same as the previous dashboard (notice that France isn’t being displayed in the scorecard), but now there isn’t a nice drop down filter list

So what is the best way to filter a KPI?  Do you do it in the cube, in the data mappings of the KPI, on the dashboard page with a nice filter?  Well, it depends.  You need to know what the requirements are and then plan appropriately and build out the items to create the dashboard and present the results to the user.

For additional information on the different types of filters in PPS check out the online documentation – Creating filters in Dashboard Designer.