Category Archives: Business Intelligence

Lönar sig en investering i Business Intelligence?

Den 17de maj pratar jag om och demonstrerar verksamhetsnyttan i en investering i Microsofts BI plattform under ett seminarie på Microsoft.

In English: On May 17th I will present and demonstrate the business value of an investment in the Microsoft BI platform.

Mer information på Solvers hemsida: http://sj.la/12JLaiH

Mer information på Microsofts CloudFacts-sida: http://sj.la/YTDYfG

Mer information:

Solver demonstrerar verksamhetsnyttig BI med Microsoft Office 2013 och Sharepoint 2013

Fråga: Hur får min personal tillgång till och möjlighet att förstå och analysera både företagets och sina personliga nyckeltal (KPI)
Svar: – Vi visar effektiva sätt att få tillgång till och analysera de viktigaste nyckeltalen genom intranätets BI-portal

Fråga: Som ansvarig för IT har jag tillstyrkt stora investeringar i avtal med Microsoft, hur drar jag nytta av dessa?
Svar: – Vi visar upp olika exempel hur dessa investeringar kan utvecklas till en verksamhetsanpassad informations- och BI-lösning med standardprodukter från Microsoft

Agenda:
– Kort om Solver
– Microsoft BI, en översikt
– Demonstrationer

Seminarieinformation
När? – Fredagen den 17 maj 8.00 – 10.00 (registrering och kaffe 08.00 – 08.30)
Var? – Microsoft, Finlandsgatan 36, Kista
Anmälan? – Maila din anmälan till bjorn.nylander@solver.se eller ring Björn Nylander på 08-24 88 00 för att boka ditt deltagande. O.S.A senast 10 maj. Seminariet är kostnadsfritt men begränsat till 40 deltagare.
Välkommen!

 

Migrating to TFS, Team Foundation Service

As part of the Easter holiday time abundance I am migrating some projects to the TFS service at http://tfs.visualstudio.com/. This is part of the evaluation to see if it works for some of the more complex scenarios we face when developing BI solutions.

Once a project is created the following message greets the creator:

“Your project is created and your team is going to absolutely love this.”

BIFrameworkprojectcreated

So at least we are off to a good start.

How to: use select all in a Tablix filter

This post describes one way to let users select all values in a filter in a data area such as a Tablix in Microsoft SQL Server Reporting Services.

During a recent Reporting Services 2008 course (6236) I was asked about how to use filtering in a data region, such as a Tablix, and present an option for the user to select all filter values.

A search for doing this with parameters gives you quite a few tutorials, such as Chris Hays’s from 2004. Searching for the same approach for filtering doesn’t yield the same amount of tutorials. So here is a quick guide for a similar approach for select all in filters.

This guide will use SQL server 2008 r2 and BIDS based on Visual Studio 2008.

Initial tasks for demo

Step 1, start BIDS and create your empty Reporting Services project

1-createproject

Above: Screenshot of the new project dialog.

Step 2, Create a new empty report (not through the wizard)

2-createemptyreport

Above: Screenshot of Add New Item dialog.

Step 3, Create a demo dataset (with an empty data source)

3-demodataset

Above: Dataset dialog.

Step 4, Create a Tablix and bind it to our dataset

4-Tablix

Above: Screenshot of report body with a Tablix bound to our dataset and with the detail row showing our data field

Step 5, Preview report

To verify that we are still on track, check the preview of the report and make sure it displays our demo data

5-preview

Above: Screenshot of preview of our not so impressive report

Step 6, Create a Parameter

To be able to filter our dataset through a prompt we need a parameter. We will create a manual one. Please use proper datasets mapped to your data for production reports.

6-ParameterForFiltering

Above: Manual filter items for all and for each of our demo data values.

Step 7, create filter in Tablix

Go to Tablix properties and choose the filter tab.

For our Filter Expression we want to create a filtering expression that can evaluate to true. In a traditional filtering expression we would just choose our field and choose to compare it to our parameter. In this scenario we want to do the logic in this expression so that we can do whatever logic is needed. In this case we choose to compare either to one of the parameter values , or check if the parameter is 0 (our “All” value from the parameter settings)

7-filterexpression

Above: Expression for Filter

For our Value expression we want to evaluate true so that we can compare the two. Note that we need to enter “=true”, not just “true”.

8-filterexpressionpart2

Above: Screenshot of Filter value expression.

Once we have our two expressions our filter properties for the Tablix will show as this:

9-filterdialog

Above: Filters dialog in Tablix properties

Step 8, test filtering

Run report and choose All in parameter:

10-filteredresultall

Above: All data visible with All filter

Verify functionality with a specific parameter:

11-filteredresult1

Above: Only some data is visible when filtering with parameter

More on filtering

The whole point on using data area filtering versus using data set parameters can be expanded on quite a bit. There are loads of resources on that around.

But trying to be complete: One aspect is how much data you want to generate from data source on initial report run, another is if you want to be able to run the same report with different data without generating another trip to the source. By using parameters and filtering on the data set you only retrieve a limited amount of data. By using filters you can run your report once against the data source and then filter on the intermediate result in a cache or on a report snapshot.

Demo Report

Here is the report file used in the demo

 

Free ebook: Introducing Microsoft SQL Server 2012

Ross Mistry and Stacia Misner’s book “Introducing Microsoft SQL Server 2012” is available from Microsoft:

http://blogs.msdn.com/b/microsoft_press/archive/2012/03/15/free-ebook-introducing-microsoft-sql-server-2012.aspx

Stacia Misner has a whole part of the book dedicated to BI.

 

[googleplusauthor]

Excel Services errors when AS cube changes

When using Excel Services to display data from an Analysis Services cube, the following error might occur when the cube structure changes.

Web part page error

If the Excel spreadsheet is displayed as a web part, the following error will appear in the web part page:

image

Text: Error. Web Part Error: An error occurred while setting the value of this property: Microsoft.Office.Excel.WebUI.ExcelWebRenderer:WorkbookUri – Exception has been thrown by the target of an invocation.

Edit Web part error message

image

 

Text: Cannot save all of the property settings for this Web Part. An error has occurred

An item with the same key has already been added.

The error comes in scenarios such as: after cube changes such as when a dimension has been deleted, and this dimension is used as a page filter in the Excel pivot table and the page filter was a named item and the named item was exposed as a parameter to Excel Services when saved into SharePoint. If the Excel file is updated with the new metadata from the cube but the named items is not, the error message appears.

The solution is to update the Excel file with the new metadata from the cube and also update the named items used as parameters so that they correspond to the relevant cells in the filters area.

Numbers in perspective

While it is very nice to read about Microsoft’s free cloud service for research:

http://blogs.technet.com/b/microsoft_blog/archive/2010/11/18/democratizing-research-with-the-cloud.aspx

I couldn’t help noticing that their perspective on numbers are a bit peculiar.

 

msft_and_numbers

 

The comparison image they use even fail to link to their sources, you need to type them yourself:

http://www.census.gov/ipc/www/idb/worldpop.php

http://www.ncbi.nlm.nih.gov/genbank/genbankstats.html

Link to original image in larger format:

http://blogs.technet.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-80-54/8132.NCBI_2D00_lg.bmp

While I don’t know much about DNA base pairs, I thought the number of people worldwide was a bit of… Now I know more about the number of base pairs – by verifying the source…

This reminds me of the essay by Edward Tufte on NASA’s use of PowerPoint:
http://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=0001yB&topic_id=1

What is the cost of cloudspamming?

Being of curious nature, I wanted to test if spam traffic would affect cloud services in a typical “pay as you go” scenario.

I ordered an introductory SQL Azure package from Microsoft with a pay as you go pricing plan.

image

By creating a plain, empty, SQL server instance with a random public access name I managed to get a monthly $10 cost in just 2 months without any advertising or mentions anywhere.

It will be very interesting to se how the cloud services will develop in the future in regards to this aspect.

I

Setting debug to true in web.config breaks Reporting Services in SharePoint

When using Reporting Services reports in SharePoint and at the same time changing the debug attribute to true in web.config the following error message might appear in a web browser alongside the Reporting Services reports not behaving properly:

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET4.0C; .NET4.0E; .NET CLR 3.5.30729; .NET CLR 3.0.30729; InfoPath.3)
Timestamp: Thu, 14 Oct 2010 10:01:21 UTC

Message: Sys.ArgumentNullException: Value cannot be null.
Parameter name: panelsCreated[x]
Line: 129
Char: 12
Code: 0
URI: http://servername/ScriptResource.axd?d=somethingcomplicated…

Changing the following attribute in the correct web.config will remedy the problem, however the debugging opportunities will take a hit…

<compilation optimizeCompilations="true" batch="false" debug="false">

How to: Enable larger reports in Reporting Services

When deploying large reports to SharePoint or Reporting Services from BIDS (Visual Studio) something like the following message might appear:

Error    1    System.Web.Services.Protocols.SoapException: There was an exception running the extensions specified in the config file. —> System.Web.HttpException: Maximum request length exceeded.    at System.Web.HttpRequest.GetEntireRawContent()    at System.Web.HttpRequest.get_InputStream()    at System.Web.Services.Protocols.SoapServerProtocol.Initialize()    — End of inner exception stack trace —    at System.Web.Services.Protocols.SoapServerProtocol.Initialize()    at System.Web.Services.Protocols.ServerProtocol.SetContext(Type type, HttpContext context, HttpRequest request, HttpResponse response)    at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)        0    0

This happens when the report is bigger than the maxRequestLength specified in the Reporting Services web.config file. The default is 4 MB so if the Report is bigger than that it will fail to deploy. it will frequently happen if the report contains map reports using embedded esri shape file data…

The solution, apart from making the report smaller, is to increase the maxRequestLength attribute of the httpRuntime Element in the web.config file of the Reporting Services instance. This file might be found in a directory like this for SQL Server 2008 r2: C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServer

Change the element to the following:

<httpRuntime executionTimeout="9000" maxRequestLength = "16384" />

where the 16384 equals 16 MB max sixe for the reports. Change the value to suit your report size needs…

 

more information about the element in this article: http://msdn.microsoft.com/en-us/library/e1f13641.aspx

 

Updated 2012-12-08: The above link is outdated. Here are some alternative resources:

Report and Snapshot Size Limits (SQL Server 2008)

 Modify Reporting Services configuration files [AX 2012]

 

SQL Server Agent job status web part for SharePoint

I have released a small web part for SharePoint that displays the current status of a SQL Server Agent job as a colored icon and displays status messages as text.

The solution and the source code is available at:

http://jobstatus.codeplex.com

Here is a screenshot of how it might look in a standard SharePoint 2007 team site.

screenshot_JobStatus

Why would anyone want this?

Sometimes SQL Server Agent jobs take some time to complete and other systems might rely on that job to complete before any useful information can be extracted from them. One example is slow import jobs done through an agent job. To be able to provide the user with the status means they know if the jobs are done or not.

This is available for download and immediate use free of charge from the codeplex site, no programming needed if you are happy with the current implementation. The source code is quite uncomplicated and anyone should be able to rework it if needed.