SQL Server 2008 R2

As a response on my last post I was asked if this is also possible with postal code regions (PLZ). It’s more difficult to find free, usable map data for this purpose. But with the help of a popular search engine I came up with the following link: http://arnulf.us/PLZ

Here you can get the ESRI shape file for the German postal code regions. You can load this data in exactly the same way as shown in my last post.

This is how the map looks after importing it into a SQL Server table (displayed in SQL Spatial Query Visualizer):

As you can see, the map is very detailed. It would be perfect, if we could add some layer of aggregation. For the German postal code, it would be a good idea to aggregate them using the first two digits, so that for example, the aggregated level of 40 includes all the postal codes like 40221 (the postal code of my company’s headquarters).

In order to join the shapes of the postal code areas we can use the SQL Server 2008 spatial functions. Therefore I loaded the data provided by the link above into a table post_pl. The following T-SQL script then creates a new table with the name post_pl2 containing the shape data for the aggregated areas:

IF EXISTS(SELECT name FROM sys.tables WHERE name = ‘post_pl2′) begin drop table post_pl2 end

create table post_pl2 (
PLZ2 nvarchar(2),
geom geometry
)

DECLARE @pl_group nvarchar(2)
DECLARE @t geometry = ‘GEOMETRYCOLLECTION EMPTY’;

DECLARE my_cursor CURSOR FOR
SELECT distinct left(PLZ99,2) FROM post_pl

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @PL_Group

WHILE @@FETCH_STATUS = 0
BEGIN
    set @t=’GEOMETRYCOLLECTION EMPTY’
    select @t= @t.STUnion(geom.MakeValid()) from dbo.post_pl 
       where PLZ99 like @pl_group+’%’

    insert into post_pl2(PLZ2,geom) values(@pl_group, @t.BufferWithTolerance(0.001,0.1,0))

    FETCH NEXT FROM my_cursor
    INTO @PL_Group
END

CLOSE my_cursor
DEALLOCATE my_cursor

The query takes some time to execute (about a minute on my virtual development environment), so be patient. I’m using BufferWithTolerance here to smooth the results, because the postal code areas do not fit perfectly. The resulting spatial data is now joined together at postal code region level (2 digits):

From here you can get a more detailed view by filtering on a single postal code region (in my example, I’m using the region 40xxx):

Instead of the T-SQL code from above you could also use the .Net stored-procedure from Craig Dunn’s post. This makes it very easy to build the aggregate because it provides a “normal” aggregation function UnionAggregate that is capable of aggregating the data type geometry. If your spatial data is stored in a field of the type geography instead, you will definitely want to take a look at the SQL Server Spatial Tools at Codeplex. Here you can find the corresponding GeographyUnionAggregate for the geography data type.

You could also combine the spatial information from this post with the shapes from my last post to allow a drilldown like Country, German state (Bundesland) and then postal code region and postal code.

SQL Server 2008R2 SSRS

In this post I will show how to implement drill down into a more detailed view for a SQL Server 2K8 R2 Reporting Services (SSRS) map.

STEP 1. Acquiring map data and importing the data into a SQL Server database

For this example, I’m going to use a map of Germany. The map can be downloaded from http://www.diva-gis.org/gData

From my download I got a ZIP-file containing the ESRI shape files needed for import. For our needs it is important that we get different levels of granularity:

DEU_adm0	DEU_adm1	DEU_adm2	DEU_adm3

STEP 2. Loading the map into a SQL Server database

I’m using Shape2sql here. The tool can be downloaded from http://www.sharpgis.net/page/sql-server-2008-spatial-tools.aspx. In order to actually load the data files into a database, we first have to create this database. In my case, it’s located on my localhost machine with the name GIS (just an empty database).

The following screenshot shows the loading of the DEU_adm3.shp file. In my case I had to deactivate “Create Spatial Index” as I got an error with this one.

After loading DEU_adm1.shp, DEU_adm2.shp and DEU_adm3.shp you will find three new database tables in your database:

We can now use SQL Server Management Studio or SQL Spatial Query Visualizer (contained in the download for Shape2sql above) to visualize our maps. I used the latter one to create the map screenshots shown above.

STEP 3. Creating the report

Now we can actually start building our report. I created a new Report Server project using Business Intelligence Development Studio. In this report project I created a shared data source to connect to my GIS database.

Then we can finally start with the report. I created a new report and added the shared data source to my report. Here are the steps:

Next step is to add a dataset for our map data. Here are the steps:

For the query, copy and paste the following SQL query into the Query field:

with mapdata as (
select 3 as MapLevel, ID_1,NAME_1,ID_2,NAME_2,ID_3,NAME_3,geom, NAME_3 As Lbl from dbo.DEU_adm3
union all
select 2 as MapLevel, ID_1,NAME_1,ID_2,NAME_2,null,null,geom, NAME_2 from dbo.DEU_adm2
union all
select 1 as MapLevel, ID_1,NAME_1,null,null,null,null,geom, NAME_1 from dbo.DEU_adm1
)
select * from mapdata
where
    (ID_1=@LocID1 OR @LocID1 Is NULL)
and (ID_2=@LocID2 OR @LocID2 Is NULL)
and (ID_3=@LocID3 OR @LocID3 Is NULL)
and MapLevel=@MapLevel

Note, that this query actually defines a union of all different layers of detail into one result set. If you like you can also look at the query results in Management Studio (of course you have to remove the where condition and I would recommend to order by id_1, id_2, id_3):

Here you can see, how the detail levels (field MapLevel) are adding more information.

Let’s go back to our report. After adding the query you will notice that the parameters of the query are automatically added to the report as shown below:

For our report, we hide all parameters by setting the visibility option to hidden. Also, we allow NULL-values for each parameter. Here are the details:

For the MapLevel parameter (and only for this one), we’re going to define an initial detail level of 1:

Now we’re ready to add the map to the report. From the toolbox simply drag’n’drop a map on your report:

After adding the map we have to decide for a data source. Of course we’re using the SQL Server here:

Next, we need to choose from our datasets. Since we only have defined one, it’s an easy choice:

I got an error message here saying that the data could not be loaded. We ignore this error here. In the next screen leave the defaults and just press “Next”.

Now for the map type, choose the basic map and continue:

Finally you can decide for a style (which I leave up to you) and you’re done. You should now see a map (not with our data) on your report. You can still make some changes with the map (move it around, add a title, remove the legend).

When using “Preview” you should already be able to see a map like this:

STEP 4. Adding Drill-Down

If you click into the map, you should be able to see an additional layer menu to the right of the map. From this menu open the context menu for the first and only layer and click “Polygon Properties…”:

On the General Tab we define the [Lbl] field of our query as the label for our map data:

On the action tab, we define the actual drill-down. In this case, we’re going to link our report with itself (just choose “Go To Report” and select the name of the current report from the drop-down list). Next we have to map all the parameters. Click the “Add” button four times and add the following mappings:

LocID1 –> [First(ID_1)]
LocID2 –> [First(ID_2)]
LocID3 –> [First(ID_3)]
MapLevel –> =iif(Parameters!MapLevel.Value>=3,3,Parameters!MapLevel.Value+1)

By doing so we’re going to link our report parameters to the selected area on the map (id columns of the different elements). Also, we increment the MapLevel with each click to get to the next detailed level as shown in the expression for the MapLevel (use the expression editor to compose the above expression or simply copy it into the editor).

That’s all we need to do in order to create the report. We can now test it in the Visual Studio environment or deploy it to a report server. This is how the report looks in the report server if you first click on “Nordrhein-Westfalen” and then on “Düsseldorf”. Simply use the browser’s back-button to go back one step.

Of course you can also use some data aware shading of your map, but for the current post, I’ll leave it as it is.

SSRS 2008; Data basis: AdventureWorks 2008  

The results are to be represented in chart as bars. The data are to be summarized dynamically in groups. The dataset is used in other tables and diagrams and should not be changed.  

Solution: In the characteristics of the Category Group Group Expressions can be inserted. The Expression accesses report parameters, which can be filled by other datasets. Example:  

=Fields!Reseller_Order_Count.Value<= Parameters!StartBucket1.Value  

In this example all data are summarized, whose value is smaller or equal to the parameter. Also the Expression of the announcement is accordingly dynamically filled:  

=IIF(Fields!Reseller_Order_Count.Value <= Parameters!StartBucket1.Value,”1-” & Parameters!StartBucket1.Value,  

IIF(Fields!Reseller_Order_Count.Value>Parameters!StartBucket1.Value AND Fields!Reseller_Order_Count.Value<=Parameters!StartBucket2.Value, 

 IIF(Fields!Reseller_Order_Count.Value>Parameters!StartBucket2.Value AND Fields!Reseller_Order_Count.Value<=Parameters!

StartBucket3.Value,Parameters!StartBucket2.Value+1 & “-” & Parameters!StartBucket3.Value,”>” & Parameters!StartBucket3.Value)))  

For each group a parameter is needed. As many as desired groups can be provided. If the Expression should not supply value, the group is not indicated, charts behaves also here dynamically. There should be values, which are larger than the highest value of a parameter, these are indicated automatically in their own group. In the following example data with 3 groups/parameters are indicated, the fourth group contain the values, which are larger than the indicated highest parameter 10:

  

SQL Server 2008R2 | Excel 2010/PowerPivot

Since I’m using the recently released Microsoft PowerPivot Add-In for Excel 2010 and when reading the rumors about the future of traditional OLAP and MDX there are some questions about the big picture of a BI-environment including self-service functionality. Basically, the BI world of the past had excepted the idea of a central data warehouse having a meta data layer such as OLAP to perfectly present the information to the end users. What about this new player PowerPivot then? How does this fit into the picture? Is there still a future for things like OLAP, MDX, central data warehouses or do we only need to roll out self-service BI functionality to every desktop PC? Some people have asked me questions about my point of view here and although I’m not a Microsoft representative I’d like to share my personal opinion with you:

Self-service BI tools are not and will never be a replacement for traditional BI-systems but a great enhancement for them.

  Simple dashboard build using only Excel 2010 with PowerPivot. No centralized BI needed anymore?

In other words, including self-service BI functionality to your BI-system will increase the possibilities and the analytical power of the end users. But if you are cutting costs for centralized BI solutions believing to replace them with self-service tools, you’ll end up with even higher costs as suddenly the work that has only been done once in the centralized BI system will be performed redundantly, in an inconsistent way, error-prone and with much more working time needed.

In order to understand this, let’s look at just some advantages, a centralized BI-system can offer, which cannot be replaced by a decentralized self-service BI tool like PowerPivot:

Combination of data from multiple source in a consistent and time-saving way
If this is done on a user per user approach, it is very likely that different users are getting to different results. Also, this work is highly redundant. Imagine different departments getting to the IT in order get “their” data exported, then trying to combine it into a single data store. Often enough, this job requires additional mapping tables (customers, articles etc. may have different ids in different systems). We are supporting customers with multiple ERP-systems (due to mergers) and the mapping can be quite complicated. But even if it is just a single source of information, mapping between different tables has to be done and requires skills and knowledge about the data models. For example, if you forget to consider a key field the result may differ significantly. Or think about the need to exclude rows with a certain status, because this means they’re cancelled. One end user might know about this, the other might not.
This means that you will still need your sophisticated ETL processes, a proper front room model, slowly changing dimensions and all the stuff we know from our typical BI projects.

Consistent use of common calculations considering approved business rules
In many cases, our current ETL processes include complicated business processes for doing calculations and data mappings. Key measures and performance indicators have to be calculated in a consistent way. Business rules are the backbone for the company’s information system. If different departments are comparing apples and oranges, there is a lot of space for confusion and wrong decisions. Think of a simple example, like reporting the revenue. Are warranty adjustments considered? What about partial payments or commissions? What about discounts (for example staff discounts)? For all those aspects it has to be decided to include or exclude them into a certain key measure and also which relation to the time dimension is correct. Is revenue for a partial payment considered as a sale (full amount) at the date of purchase or are the real payments (cash flow) considered? If every user has to make these decisions it is very unlikely that everybody is doing the same calculations. Comparing results for different departments (think of a sales meeting for the different product managers) will then get very difficult.

Security can only be implemented in a central data store
Some real world scenarios are currently looking like this: The IT department exports data for other organizational structures, manually filtering out the data that is not intended for the recipient. This might work with very simple security structures, but with more security roles, user dependent security or more information recipients, this would lead to an enormous amount of work for exporting all the data. And if there are changes to the companies security model, all the exports have to be considered again. Having a central OLAP solution makes it easy to define the security roles and access rights in a central place using the business view on the data. For example, in OLAP you can restrict a user to see the cost centers for which she or he is responsible – OLAP takes care about all related data (for example automatically filtering the cost facts to these cost centers). There is no need for a huge amount of data exports as users can retrieve the data and information they need and IT only has to make sure that the data is available and secured.

The need for management reporting
Management needs an overview about some or all business units. Having the information (especially the calculations, KPIs etc.) in a decentralized environment makes it very difficult to get this management reporting in a simple, time saving way. It is more likely, that IT has to do special exports which are then processed by the controlling department to build the management reports. This could result in controlling spending all the time in doing data management, not information management and controlling. Also, in this scenario, the data from the management reporting will most likely differ from the data of the departments. Just imagine the CEO going to a some product manager saying “Hey, you’re product profitability is –5%” and the product manager says “No, it isn’t. Look at MY report. Here it reads +3%” and actually neither of them could say which result was the right one…

Then, after all, if the central BI environment is so important, do we really need decentralized self service BI? Well, not every user will need self-service BI but for some it can be a real time saver or give them a lot of analytical power. Here are just two important scenarios:

• The end user wants to analyze information by special properties of the data which are not present in the centralized data warehouse. Just think of product managers. Each product has a different target group, special conditions in the market and therefore potentially certain aspects that are different from product to product and therefore from product manager to product manager. Those information might only be relevant for certain products. Having this in a central data warehouse would be confusing as the information has no meaning for most of the products. Allowing each department to cover the specific needs of their work while still providing the central information being available in the data warehouse is the best way here.
• The end user wants to combine the centrally provided information with other sources of data, for example information that has been purchased/acquired from external sources and which is not complete in means of geography, time etc. Think of a marketing department planning a campaign. In order to do so, they want to analyze sales data in conjunction with external data for purchasing power. The external information was only purchased once for the region where the campaign is planned. This kind of data cannot be loaded into the central data warehouse. But with self-service BI it can be analyzed side-by-side with the centrally provided sales data.
  Or think of one department trying to improve product quality by changing some of the parameters during the production cycle for some of the production batches. These changes are tracked in some other system (let’s say Excel) but not in the central DWH as they do only apply to this single line of production. Self-service BI allows us to analyze changes in the parameters together with data from the central data warehouse side by side (for example quality control data in this case).

So my opinion is that the traditional centralized BI systems really benefit from self-service BI functionality. However, self-service BI can never replace traditional BI. But what about technical aspects, especially the future of MDX? MDX is the query language for multidimensional databases (used by many vendors). In PowerPivot we can do a lot of the calculations using the new expression language called DAX. Will DAX be a replacement for MDX? Absolutely not! DAX is meant to bring analytical power to Excel users. It looks similar to Excel functions and in fact many Excel functions can be used. Its strength is simplicity. Although one could imagine to extent the expression based DAX language to query functionality (in MDX you can write both, queries and expressions) this would also complicate the use of DAX which is clearly not intended. Even with today’s Excel, many users only know about the operators +, –, / and * and the SUM function (advanced users know about SUMIF…). In order to have end users, even power users, being able to leverage the power of a self service BI solution, the calculation functions have to be as simple as possible. This is the idea of DAX. However, when defining complex queries, building highly sophisticated business logic into calculations or KPIs, implementing ease-of-use like KPI trends, OLAP actions, drill through queries, navigation in hierarchies (DAX has no hierarchies) MDX has everything that’s needed here. Including this functionality into DAX would only make it complicated and more difficult to understand and use. Of course, this is only my opinion, but I’m sure that MDX will still be used for what it is used today and DAX expressions will be used for self-service calculations and mappings that are intended to be done by none-technical people.

When using client-side technology to create any kind of informational insight we have to monitor this process carefully. There has to be a process to maintain business requirements and implement changes to the central BI-system to avoid the negative effects mentioned above. As with PowerPivot, it is also possible to monitor which workbooks have been used and which data sources have been queried (if the PowerPivot sheets are published to Sharepoint 2010). I think this is also important to really understand, if the self-service BI tool is used in a way it is intended to be used or if some analysis requirements that should have been part of the central BI solution are now starting to be solved in multiple departments redundantly.

So again, we will see a co-operation between the centralized BI-system and self-service BI solutions, as well as between MDX and DAX. Self-service BI and DAX are not the fox in the chicken-house of traditional BI but they are extending the vision and scope of BI-systems of today and in the future.

SQL Server 2008 | SQL Server 2008 R2

OLAP is perfect for analyzing fact records that are mapped to dimensions. However, what can be done, if the reference to the dimension changes depending on the selected period? I’m not talking about slowly changing dimensions here, but really about the period of selection.

Think of the following example: A manager wants to analyze the number of visited customers for his sales force. He wants to see, how many customers have been visited once, twice and so on during the last three months (or any other time period).

In order to do so, we need a dimension “Visits” (with the number of visits, for example 0, 1, 2, …) but we cannot map the visits against this dimension because this mapping depends on the selected period. For example, one single customer was visited once in January and once in February. If the selected period is January, the we need to see one customer being visited once. If the selected period is January and February together, we would need to see the customer being visited twice.

In SQL Server 2008 we can solve this by not linking our Visits dimension to the measure groups. The calculations are then performed by a cube script. For our example, we’re using a very simple data model:

The fact table FactVisits contains one row per visit (linked to date and customer). Note that the Visits dimension is not linked to the fact table. The source table for the visit dimension looks like this.

For the cube, we need to calculate the entries for the Visits dimension. To do so, I created an additional measure (Customer Count) based on some other value I found in the source fact table (the value will be overwritten using the cube script later). Usually you would create an additional column in the data source view (value 0) to source this measure from. Here is the definition of my Customer Count measure:

In order to calculate the number of customers for each visit count, I created the following cube script:

scope ([Visits].[Visit].[Visit],[Customer].[Customer].[Customer],[Measures].[Customer Count]);
this=iif(([Measures].[Visit Count])=CInt([Visits].[Visit].currentmember.properties("KEY")),1,NULL);
end scope;

The script is computed on leaf level of the visits (visit count dimension). In the cube script, the reference to [Visits].[Visit].currentmember.properties("KEY") results in the VisitID (number of visits). If the number of visits (measure [Visit Count]) is equal to the number of visits found in the visit dimension, this is counted as one. Note, that I used NULL instead of 0. This makes it easier to analyze, which customers have been visited for a given number of times, as null values can be suppressed (see last screenshot of this post).

So, let’s check the results up to this point.

The calculation now works for every single line but currently the totals are still wrong. The correct value for the totals can be computed using a dynamic set (to allow Excel multi selects) like this:

CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[DynaVisits] AS [Visits].[Visit].[Visit];
([Visits].[Visit].[All])=Sum(existing [DynaVisits]);

I also want to count more than ten visits on a special dimension element (ID 999 in the table above). This can be achieved with the following cube script:

scope ([Visits].[Visit].&[999],[Customer].[Customer].[Customer],[Measures].[Customer Count]);
this=iif(([Measures].[Visit Count])>10,1,NULL);
end scope;

Testing the results (here using Microsoft Excel) shows the correct calculation for the grand total as well as for other levels of the visit count hierarchy:

The way our calculations works can be best observed when looking at a single customer.

In this case we had two visits of our customer in 2008 and one visit in 2009 giving a total of three visits for 2008 and 2009. Note that the actual fact is shown as a dimensional property here.

We can also apply multi select filters in Excel. Here is another sample screenshot filtering only customers with less than 4 visits during the last three months in 2009 (this is the ‘(multiple items)’ filter for the calendar). The data is now also analyzed by the sales representative (attribute of the customer). Note, that the grand totals are still correct:

As mentioned before it is also possible to see which customers are listed here by including the customers in the pivot table. This is shown in the following screenshot (filter to visit count = Zero).

However, the drill through action would still return all customers (due to the definition of my measure) as the value is only based on a computation. So the drill through should be disabled here.

In this case, I simply checked the measure in order to enable the drill through action:

Of course, the example is still a very simplified one. Usually you would need to know the customer base, so you’re not counting new customers as not being visited during the last years.

Erst kürzlich habe ich in einem Blog von dem Microsoft Projekt “Dallas” gehört. Es ist ein Codename und derzeit ist die CTP2 öffentlich und wer über einen Windows Live Account verfügt, kann an der CTP2 teilhaben.

http://www.microsoft.com/windowsazure/dallas/

“Microsoft® Codename "Dallas" is a new service allowing developers and information workers to easily discover, purchase, and manage premium data subscriptions in the Windows Azure platform. Dallas is an information marketplace that brings data, imagery, and real-time web services from leading commercial data providers and authoritative public data sources together into a single location, under a unified provisioning and billing framework. Additionally, Dallas APIs allow developers and information workers to consume this premium content with virtually any platform, application or business workflow.”

Aber um was geht es? Dallas ist eine Datenintegrations-Plattform. Es ermöglich den Anbietern von Datendiensten ihre Daten einer breiten Basis von Anwendern zur Verfügung zu stellen. In der momentanen Phase befinden sich bereits einige prominente Anbieter wie NASA, Associated Press (AP), Wheater Channel oder NAVTEQ. Derzeit können alle angebotenen Inhalte kostenlos getestet werden.

Der eigentliche Clou ist aus meiner Sicht aber die Verbindung dieser Daten mit PowerPivot (freies Excel-Addin für Office 2010). Über den Analyse Button lassen sich die “Dallas” Daten direkt nach PowerPivot importieren. Dort angekommen lassen sich diese Daten mit den “lokalen” Daten kombinieren.

Also kurz:

• Daten. (Einige sind frei, andere kosten per Download. Den Katalog der Datenanbieter findet man hier: https://www.sqlazureservices.com/Catalog.aspx)
• Aufgebaut auf der Windows Azure Plattform
• Anpassbar über eine REST-basierte APIs.

Hier sind verschiedene Szenarien vorstellbar:

• Lokale Abverkaufsdaten können mit dem Wetter am Standort korreliert werden
• Geografische Informationen (z.B. Ländergesellschaften) lassen sich anreichern mit Daten der UNO, wie Einwohnerzahlen oder sonstige soziografische Daten
• Verbindung der Kundendaten mit Daten der Gelben Seiten
• …

Der Startpunkt befindet sich auf der Seite http://www.microsoft.com/WindowsAzure/dallas/.

Wie melde ich mich bei “Dallas” an?

Zunächst benötigt man eine Windows Azure Subscription. Dazu muss man sich derzeit nur mit seiner Windows Live Id anmelden (http://www.microsoft.com/windowsazure/offers/).

Anschließend landet man auf der Katalog Seite und kann hier die verschiedensten Daten “subscriben”. Exemplarisch sei hier der Dienst von Associated Press (AP) dargestellt.

Auf der folgenden Seite lassen sich dann die Service Parameter einstellen. Hier werden dann die für den Zugriff benötigten Informationen dargestellt:

• Account Key
• Unique User ID
• Service URL

Diese werden für einen Zugriff benötigt.

Der Analyse Button öffnet das PowerPivot AddIn von Excel 2010.

Anschließend stehen die Daten im Addin zur Verfügung und können mit den lokalen Daten verknüpft werden. Auch die Aktualisierung der Daten ist so möglich.

Weitere Beiträge:

• PowerPivot und Codename “Dallas”
• PowerPivot
• Consuming “Dallas” Data with PHP (Extern)