BI Future Blog

SSAS: optimizing processing query using reference dimension relationship

2012-02-08T12:04:00.000+01:00

Introduction
In one of my former posts titled "Select Facts with Reference and Many2Many relationships" I blogged about the usage of reference dimension relationships in cubes. In my current clientsituation i'm experiencing a major slowdown while processing the cube. Some investigation learned that the reference relationsshiop seems to be the problem. In this blogpost i'll explain the problem, a solution (by replacing the load query) and a conclusion.

The problem
I'll be using the labsituation that i've describedin my former blogpost "Select Facts with Reference and Many2Many relationships". In this blogpost i've shown how to build a reference dimension relationsship and a many2many relationsship. For this partiular problem i'll focus on the reference dimension relationsship because this is experiencing performance slowdon at the client i'm currently operating.

Suppose i've the following situation (see diagram below). In this situation i've three reference relationsships: Customer, Department and Date. These dimensions are the normal dimensions of the Orders fact. Because we want to influence the numbers on the OrderLines fact we have to create a Reference Dimension relationship.

The problem is that i'm experiencing a slowdown in performance during processing the cube because of the usage of the reference dimension relationsshipin the cube. This (the processing query) will result in the following load query of the cube:

SELECT
[dbo_tblFactOrderlines].[OrderLines_Count] AS [dbo_tblFactOrderlinesOrderLines_Count0_0],
[dbo_tblFactOrderlines].[Product_ID] AS [dbo_tblFactOrderlinesProduct_ID0_1],
[dbo_tblFactOrderlines].[Category_ID] AS [dbo_tblFactOrderlinesCategory_ID0_2],
[dbo_tblFactOrderlines].[Orders_ID] AS [dbo_tblFactOrderlinesOrders_ID0_3],
[dbo_tblFactOrders_4].[Customer_ID] AS [dbo_tblFactOrdersCustomer_ID4_0],
[dbo_tblFactOrders_5].[Department_ID] AS [dbo_tblFactOrdersDepartment_ID6_0],
[dbo_tblFactOrders_6].[Date_ID] AS [dbo_tblFactOrdersDate_ID8_0]
FROM [dbo].[tblFactOrderlines] AS [dbo_tblFactOrderlines],
[dbo].[tblFactOrders] AS [dbo_tblFactOrders_4],
[dbo].[tblFactOrders] AS [dbo_tblFactOrders_5],
[dbo].[tblFactOrders] AS [dbo_tblFactOrders_6]
WHERE
(
([dbo_tblFactOrderlines].[Orders_ID] = [dbo_tblFactOrders_4].[Orders_ID])
AND
([dbo_tblFactOrderlines].[Orders_ID] = [dbo_tblFactOrders_5].[Orders_ID])
AND
([dbo_tblFactOrderlines].[Orders_ID] = [dbo_tblFactOrders_6].[Orders_ID])
)

As you can see that for every "Reference Dimension" relationship an extra join is created to the same table (?!). The WHERE clause uses 3 joins for Customer, Department and Date. At my client i'm having 5 joins and it takes now about 15 minutes to load a specific measuregroup. When i replace the 5 joins with 1 join the processingtime drops drammatically, from 11 minutes to 35 seconds.

The solution
I've replaced the OrderLines tabel in the datasource view by the following view

DROP VIEW vwOrderLinesCube
GO

CREATE VIEW vwOrderLinesCube AS
SELECT
[OL].[OrderLines_Count] AS [OrderLines_Count],
[OL].[Product_ID] AS [Product_ID],
[OL].[Category_ID] AS [Category_ID],
[O].[Orders_ID] AS [Orders_ID],
[O].[Customer_ID] AS [Customer_ID],
[O].[Department_ID] AS [Department_ID],
[O].[Date_ID] AS [Date_ID]
FROM [dbo].[tblFactOrders] AS [O]
INNER JOIN [dbo].[tblFactOrderlines] OL ON [O].[Orders_id] = [OL].[Orders_id]

So i had to draw some extra lines in the datasource view and that is a drawback of the solution, unfortunately. The model is now less intuitive and less clear. Too bad...

With this new setup the following processing query is executed:

SELECT
[dbo_tblFactOrderlines].[OrderLines_Count] AS [dbo_tblFactOrderlinesOrderLines_Count0_0],
[dbo_tblFactOrderlines].[Product_ID] AS [dbo_tblFactOrderlinesProduct_ID0_1],
[dbo_tblFactOrderlines].[Category_ID] AS [dbo_tblFactOrderlinesCategory_ID0_2],
[dbo_tblFactOrderlines].[Orders_ID] AS [dbo_tblFactOrderlinesOrders_ID0_3],
[dbo_tblFactOrderlines].[Customer_ID] AS [dbo_tblFactOrderlinesCustomer_ID0_4],
[dbo_tblFactOrderlines].[Department_ID] AS [dbo_tblFactOrderlinesDepartment_ID0_5],
[dbo_tblFactOrderlines].[Date_ID] AS [dbo_tblFactOrderlinesDate_ID0_6]
FROM [dbo].[vwOrderLinesCube] AS [dbo_tblFactOrderlines]

Now you can see that the three joins has been replaced by the view, which contains now the one and only join to the Orders table.

Conclusion
Mainly this solution is needed when the processing time takes longer because of the usage of the reference dimension relationsship in the cube design. In my opinion because of bad design of the load quey. Too bad but sometimes you have to use the pragmatic solution.

Greetz,
Hennie

SSAS : Error with many-to-many dimension and level of granularity

2012-01-28T09:55:00.000+01:00

Introduction
In this blogpost a brief explanation of an error i've encountered during a implementation of a 'many2many relationship' in a SSAS cube. Although i haven't found the rootcause of this problem i do want to share with you the solution. I did only found one relevant website of Melissa Coates with the same sort of problem. So hopefully this will help the MS BI community a bit futher.

Problem
In my former post i've worked out an example with Referenced Dimension relationship and a Many2Many relationship. As you have seen in this blogpost everything worked fine but when i implemented this solution at my client i got the following error(off course i replaced the names used at the client with the names used in the example) :

Errors in the metadata manager.

The 'Category' many-to-many dimension in the 'Orders' measure group requires that the granularity of the 'OrdersDD' dimension is lower than that of the 'OrderLines' measure group

Solution
The situation at the client is much more complex than the labsituation that i've descibed in the blogpost. I have about 15 measure groups and about 75 dimensions. So debugging the problem is hard to do. I had a couple options to debug this problem:

Downgrade the complete solution to a certain level where the problem is more manageable. Break everything out of the solution that is not needed for understanding the problem. Off course use a copy of your development environment ;-).
Trail and error. In this approach you tweak the solution a bit and see whether the error disappears.
Reproduce error in labsituation. As already said, í've created a labsituation where everythings works fine. A manner could be to change some settings in the project and see whether the error appears.

I decided to take a gamble on option 2 and found a solution very quickly, very fortunately!!

The solution was to change the Fact dimensionship relationship of the OrdersDD dimension with the Orders Measure group to a regular dimension relationship.

Conclusion
A short post about a problem i've encountered during implementation of a many2many relationship in a SSAS Cube. I've presented a solution that helped solving the error. Currently testing whether everything works fine but everythings seems working very fine.

Greetz,
Hennie

SSAS : Select Facts with Reference and Many2Many relationships

2012-01-24T08:38:00.000+01:00

Introduction
In this blogpost i'll explain how to connect two starschemas with each other and how to setup the cube in SSAS in case you want to use the dimensions of the two facts selecting the measures in both facts. In other words, suppose i've two facts that have a 1:N relation (eg. Orders and OrderLines) and there are a couple of dimensions (eg. Customer, Department, Date, Product and Category). And, sometimes because of naming conventions (?) a referenced dimension relationship is automatically created between a 1 fact and a N fact. This will enable selecting the measures of the N fact with the dimensions of the 1 fact. Using a Many-2-Many (M2M) relationship will enhance selecting measures in the 1 fact with the dimensions of the N fact.

The blogpost is ended with leasons learned part and conclusions.

The problem
Let's elaborate this example to a lab situation. Suppose i've the following situation:

There are two starschemas: Orders and Orderlines (OrderDetails) and they have a 1: N relationship. OrderDD is the connecting dimension that actually is a 'fact dimension' in SSAS (not materialized but could be if you want). Physically there is an Order_id attribute in the Orderlines tables present to enhance the Fact dimension relationship in the cube in SSAS.

The Order Fact has three real dimensions : Customer, Department and Date and Order Lines has a Category and a Product dimension.

Mostly, the problem is that by default relationships are not created automatically, meaning that using the dimensions doesn't have effect on the facts of both starschemas (when you browse the cube). There are additional steps needed to enable this. This will be explained in this blogpost.

There are two possible directions to influence the measures on both facts:

Selecting measures of the N fact (eg. Orderlines) with dimensions (eg. Customer, Department or Date) of the 1 fact (eg. Orders).
Selecting measures of the 1 fact (eg. orders) with the dimensions (eg. Categroy or Product) of the N fact (eg.Orderlines)

In the solution described below i'll start with an initial setup where there is no relationship, only the Fact dimension OrdersDD, then i'll add an extra relationship 'Referenced Relationship' and finally the end solution with the 'Referenced Relationship' and the 'Many2Many relationship', together.

Solution
In this solution i've identified 3 steps:

Possible initial situation.
Reference dimension relationship solution
Endsolution with Reference and Many2Many relationship

1) Suppose you build a cube on the described starschema, the following structure could be the result. Could because because of namingconventions a 'reference dimension' relationships is sometimes automatically gernerated (see also step 2).

If the cube is browsed you can observe that the selection with dimension "Customer" selects the measures of the orders fact but not the orderlines measures. The OrderLines count is not controlled by the dimension "Customer". For every dimension value the same (total value) is shown. This is not possible by default.

The other way around, selecting measures of the Orders fact by the dimensions that belongs to the Orderline fact shows also the same problem. For every dimension value the same (total value) is shown.

2) In step 2 of the solution the reference dimension relationship is set up with the customer, Department en Date dimension. The OrdersDD functions as an intermediate dimension that directs the cube to relate the information from the Orders to the OrderLines starschema.

Below a screendump and it shows that the dimensionvalues has an imnpact on the measures on both starschemas : Orders and Orderlines.

Selecting values in the Orders starsschema with the product dimension still shows incorrect values.

3) In this final step i've added the Many2Many relationship to the cube. The Product and Category dimension now has a Many2Many relationship with Order.

The dimensions of the Orders starschema has an impact on the measures of OrderLines.

And now the dimensions of the OrderLines starschema has an impact on the measures of the Orders starschema.

Conclusion
In this solution i've shown how to connect two starschemas (with a 1:N relation) and how to tune the cube in a way that all dimensions has an impact on the measures on both starschemas. The steps that are needed are:

Create a common dimension (physical or by a fact dimension relationship) that reprensent the 1:N relation between the starschemas.
Create a reference dimension relationship for the dimensions that belong to the 1 Fact (Orders).
Create a Many2Many relationship for the dimensions that belong to the N Fact (OrderLines)

Greetz,
Hennie

Could not update the metadata that indicates database AdventureWorks2008R2 is enabled for Change Data Capture

2012-01-17T20:12:00.003+01:00

Introduction
Currently investigating the CDC in SQL Server 2012 RC0 Denali and i ran into an error, which i want to discuss with you. The error happens when i want to turn on CDC. In this blogpost i'll present the error, a cause of the problem, a solution and conclusion.

The error
The error occurs when i want to execute the following statement:

EXEC sys.sp_cdc_enable_db

It returns this error:

Could not update the metadata that indicates database AdventureWorks2008R2 is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.

Cause of the problem
I've downloaded the RC0 databases from the Microsoft site and restored them on my SQL Server 2012 RC0 Denali VM. Then, i executed this stored procedure: sys.sp_cdc_enable_db. In this article, http://support.microsoft.com/kb/913423, more information is available. This error message is shown when you are trying to enable CDC on a SQL Server 2008 R2 database for which the owner is not "sa". According to the article, this problem occurs when all the following conditions are true:
• You back up a database from an instance of SQL Server 2005 (e.g. SQLSErver 2008 R2 database). Then, you restore the database to an instance of SQL Server 2005 that is installed on another computer.
• The statement or the module is executed as the dbo (database owner) user.
• The owner of the database is a domain user or a SQL Server authorization login.

Solution
Execute the following statements:

USE AdventureWorks2008R2
GO

EXEC sp_changedbowner 'sa'

and the database owner changes to sa:

And the stored procedure sys.sp_cdc_enable_db executes successfully.

Conclusion
In this blogpost a problem is identified with databaseownership of the database and a solution is presented.

Greetz,
Hennie

BI and Enterprise Architecture (EA)

2012-01-15T10:10:00.000+01:00

Introduction
In some linkedin groups I was reading about that BI should be a Enterprise Architecture (EA) implementation for organizations and i was curious what is meant by that. What is an Enterprise Architecture implementation and how can BI be a part of it? So in order to understand more about the implementation of a Enterprise Architecture i decided to study TOGAF. Currently studying for the Foundation certification (level 1) and it's a huge book (sigh!). It takes a lot of time to understand the methodology, the phases, terminology, keywords, etc. But interesting though!

TOGAF is well-known Enterprise Architecture Framework and can be seen as a tool for assisting in acceptance, production, use and maintanance of enterprise architecture. In this blogpost i'm trying to translate the TOGAF framework to a plan to implement BI as an Enterprise Architecture.

Enterprise Architecture according to TOGAF
So what is an Enterprise Architecture? What is an Enterprise? What is a Architecture?

Definition Enterprise: "Collections of organizations that has a common set of goals". Organizations are getting smaller and are functioning in neworkorganizations. Think about an airliner that farms out the luggage handling or ticket selling. Therefore in case of network organizations you should look beyond the boundaries of an organization.

Definition Architecture (ISO) : "The fundamental organization of a system, embodied in its components, their relationships to each other and the environment and the principles governing its design and evolution".

Defintion Enterprise Architecture (SearchIO): "An enterprise architecture (EA) is a conceptual blueprint that defines the structure andoperation of an organization. The intent of an enterprise architecture is to determine how anorganization can most effectively achieve its current and future objectives."

TOGAF makes also a distinction between an architecture and a solution. The distinction is that a architecture is like a blueprint of the organization and a solution is like an instance of the architecture. You could compare these between a logical model and physical model (in my opinion).

There are four points of views regarding TOGAF :

Business perspective. The business perspective defines the processes andstandards by which the business operates on a day-to-day basis.
Application perspective. The application perspective definesthe interactions among the processes and standards used by the organization.
Informationperspective.The informationperspective defines and classifies the raw data (such as document files, databases, images,presentations, and spreadsheets) that the organization requires in order to efficiently operate.
Technology perspective. The technology perspective defines the hardware, operating systems, programming, and networkingsolutions used by the organization.

In an Enterprise Architecture framework all of these viewpoints are worked out: a baseline is created, a target architecture, gap analysis, a roadmap, review and an implementation is executed.

Translate TOGAF to BI (or BI to TOGAF)
Generally spoken, there is low interest in BI projects regarding working from within an Enterprise Architecture viewpoint. In most of my projects done the solution is targeted at a certain area of interest: for instance finance. Another issue is that BI projects are under time pressure meaning that BI projects are driven by need for a solution for more or less a specific problem and clients wants it fast.

I don't think about BI as an holistic Enterprise Architecture approach but more as part of the Enterprise Architecture. The ideal situation would be that BI is part of a Enterprise Architecture Program where BI should be adopted at all kind of different levels of the enterprise internally and externally (think about the network organisations).

Where is BI positioned in TOGAF? You need to understand the business perspective (baseline and target) and you need to understand how BI could aid the target business perspective in such a way that it can benefit from BI. And BI is much about data and there the data perspective is also specific area of interest in case of BI.

Conclusion
The BI Enterprise Architecture can be positioned on the business perspective and on the data pespective of the TOGAF Framework. The BI Enterprise Architect must understand both perspectives and should be aware and understand the overall Enterprise Architecture picture (baseline and target in order to make a flexible, easy to replace building and easy to maintain (BI?) architecture and solution building blocks (ABB and SBB).

Make sure business intelligence is a core part of your Enterprise Architecture Planning!

Greetz,
Hennie

SQL Server Data Quality Services Cliensing component

2012-01-08T21:25:00.000+01:00

Introduction

In a former post I already introduced the DQS Quality Client and the installation of DQS of SQL Server 2012 RC0 Denali. The main advantage of DQS lies in the usage of the SSIS DQS component "DQS Cleansing" in SQL Server Integration Services. You can implement the DQS component in an ETL process in order to cleanse the information from source systems.

James Beresford investigated already the performance of the DQS component and it seems that usage of the component cost quite some performance and therefore you shouldn't use it on a large tables (yet?).

You can find more info, here:

This blogpost is based on SQL Server 2012 RC0 Denali.

DQS Cleansing component
In this blogpost i'll discover the DQS SSIS component. I'll show you the steps that i took in order to build a SSIS package with the DQS Cleansing component. The first thing i did was creating a package and a drop the DQS Cleansing component on the pane.

An input stream and an output stream is needed.

With the source- and the destination assistent you can create a source and destination adapter. When i double click on the server for connecting to the Knowledge base an error occurs.

I tried some some potential solutions like reopening the component and reopening the package. This didn't help. So i decided to reboot SQL Server instance and the error disappeared.

Connection succeeded:

Something is wrong here. After some adjustments and clicking the test connection button i ran in an error, again. The error below is shown when the test connection button in the connection manager is pressed.

Again, I restarted the SQL Server service and now it seems to be working again. Next, i'm setting up the mapping.

Below the tab with some checkboxes.

In the destination adaptersome fields are not listed in the SSIS pipeline.

I remembered that on the advanced tab there were some checkboxes: Confidence and Reason. I enabled this checkboxes.

Now these fields are selectable in the Destination adapater.

Below you can see the new progress icons. Nice!

Finished and succeeded

And below you can see the result of this little exercise.

Conclusion
This seems an useful component of SSIS but when i read the blogs of James Beresford i'm getting less enthusiastic. The main reason is the perfomance of the DQS component. He calculates that for instance 1 million rows will take about 4 hours of loading and that is not acceptable in my opinion. In an other blogpost he states that performance can be optimized by parallelism but it's still not very usable for large tables (dimensions).

In my current project i'm working with a 2 million customer table (dimension) and this seems too large for this DQS SSIS component. So James advices to use the DQS for midsized tables. In my assumption this is like around 100 K - 500K rows.

There is some work to do for the development team:

The error connecting to the Server.
Hopefully they can improve the performance to higher level

Greetz,
Hennie

SSAS : Change management in ten steps

2011-12-21T22:00:00.000+01:00

Introduction
In my current project i'm developing a cube. In this Self Service BI project i'm cooperating with Self Service BI teams, IT and end users, in order to build successful dashboards, trends and statusreports. Because of the different progress of the teams, the client wants a partial deployment of the cube. Therefore, I had a challenge because it isn't possible to deploy a cube partially easily, unfortunately. So far i haven't found anything that could do this. No 3rd party software seems to be available. So i had to come up with a pragmatic solution.

Situation
Suppose you have the following situation. There are a couple of stars in a development, acceptance and production environment (this is my current situation). The stars in the diagram below represents starschemas. The blue stars were equal between the different environments at a certain moment (t0). So the first blue star in the development area is equal to first blue star in the acceptance area and is equal to the first star in the production environment. So at one point in time the blue stars were equal. But, as time went on, some dimensions were added to the stars in the development area, new stars were created and some measures changed (t1). These are represented by the red lines. The red lines indicates a change or is new in relation to the initial situation. The diagram below is snapshot at some point in time. Some dimensions were transferred to the acceptance area, a new star has been developed in the dvelopment area and production is still equal to initial situation.

So how are you going to manage this? Well with caution, i can tell you. As said earlier the problem is the cube. The cube file is an XML file and an idea could be transferring pieces of the XML file (snippets) but in my opinion a 'big' risk and you need a thorough understanding of the structure of the XML file. I decided to solve this with manual actions and some features of BIDS helper. Below i'm going to show you how i did this.

I'm not using all the fancy stuff of a SSAS cube. I've created stars with facts and dimensions, measures (with measure groups, some calculations in the cube and a some security roles. In the solution below i'll focus on the facts, dimensions and the measures.

Solution
Below are the steps written that was needed for a successful implementation of a piece of a cube in new environment.

1. Compare the database with database diff tool (SQL compare, Apex).
In case you didn't keep track of changes in a source control software you can use a diff tool to compare databases and move the different parts to the new environment. Copy the change script into a separate folder and create a deployment script. This will be used when the code of the acceptance environment is transferred to production.

2. Load the tables in the acceptance environment.

3. Start SSAS and refresh the datasource view.

4. Copy the dimensionfiles form the SSAS project
After refreshing the datasourceview copy the dimensionfiles in the development environment and add (add exisiting item) them to the SSAS project in the acceptance environment. The dimensions are just files in he SSAS project.

The case that dimensions are stored in separate files is a big advantage because manual creating dmension in the SSAS project will very likely lead to errors. One error and a report will not work.

5. Manually change the relations in the dimension usage tab.
Make the appropriate changes. Experience learn that this an erroneous process because when a lot of dimensions and measuregroups are present it's difficult to overview the whole project. In the beginning you have to work iterative with the BIDShelper 'Printer friendly Dimension Usage' export. This explained in the following paragraph.

6. Use BIDSHelper and especially the option 'Printer Friendly Dimension Usage'.
This option prints a definition of the cube. You can find this in the context menu when you click on a cube.

It will show the following window.

And very useful is the export to excel. This enhances the comparison between two SSAS projects in the Development and Acceptanceenvironment.

7. There is also a 'Smart Diff' option available. This option compares two .cube files and analyzes the differences. You need to install Visual Studio Team System 2008 Team explorer (384 MB!) to get this working (in my case).

Below a screendump of a comparison between two cubes.

Look for changes in the measures:

8. Compare the dimensions in SSAS too
We ran in one problem that a report didn't work in the acceptance environment but did work in the development environment. Checked the cube again, try to debug the report and we were getting strange errors. Then i remembered that i changed the hierarchies in the existing dimensions (and we didn't moved them to the acceptance environment). So compare all dimensions of a star too with Smartdiff.

9. Process!
If the comparison of the cubes between development and production is successful the cube is ready for processing. Hopefully the process will succeed now.

10. Move from acceptance to production
When all scripts are gathered and are stored in a proper place, collect them and execute it on a production environment. Load the tables again and deploy the acceptance project on production and process the cube. Don't forget to set the right datasource connectionstrings. If you have a large cube deploy the cube to a test cube (in production) first. This way you can test whether the process of the cube is successful and this will not interfere with the productioncube. An option could be renaming the cube in order to minimize the downtime (didn't test this).

Conclusion
In this blogpost i've explained a partial deployment of SSAS cube from the development environment into the acceptance environment and production environment. This a manual process and there is no standard functionality available to deploy a cube partially. Most of the steps can be done by copying databasecode, dimension files and refreshing the datasourceview. The main problem is updating the cube, itself. That is a labour-intensive process.

I do realize that the method is not 100% accurate but for my current project it is sufficient for now.

If you have remarks about this approach let me know.

Greetz,

Hennie

Installation of SQL Server 2012 RC0

2011-12-17T21:30:00.000+01:00

Introduction

In my former post about the installation of SQL Server 2012 CTP1 (Denali) i wrote down a walkthrough of the installation process of SQL Server 2012 CTP1 (Denali) . In this post i'll blog about the same walkthrough but for RC0. I'll show you some differences, an error and how to solve this error.

Installation

The installation starts with downloading the software from Microsoft. Copy this in a folder and execute SQLFull_x86_ENU_Install.exe.

After clicking on the .exe file the extraction starts

The content of the folder created:

Starting setup will show this window:

Click on installation.

The next window appears.

Press ok.

The next window.

The set up role window.

Clicking on the features. Select all.

And when i press the following error occurs. The installation of CTP1 wasn't that big and it seems that there is about 2 GB more software now than CTP1.

Enlarge the disk in VM and off we go..

Server configuration window:

Then an error happens.

On Aaron Bertrand's blog i understand that the distributed replay controller and client is the problem because it needs an Active Directory to verify the account. The account i've entered was a local account and I don't have AD installed. Below the error:

SQL Server Setup has encountered the following error: There was a failure to validate setting CTLRUSERS in validaton function ValidateUsers. Error code 0x85640004.

So i corrected the error by deselecting the replay controller and client

And now we seems to have more success

The installation process starts and succeeded

Conclusion

Nothing news about the installation, only there is an error when the reply software is selected and a lot more space is needed in contrast with CTP1.

Hennie

Data Quality Services (DQS) in SQL Server 2012 (Denali) RC0

2011-12-12T19:06:00.000+01:00

Introduction
One subject that I'm interested in is Data Quality Services together with the features like Master Data Services. In this blogpost i'll try to discover the basics of DQS. I investigated this feature in CTP1 already but it wasn't ready yet, unfortunately. In CTP 3 (and RC0) the DQS seems more and more a finished product and i think it's time to discover some of the features in this post.

DQS is all about the quality of data in databases. The reason why this is a interesting feature, is that it can be used in a ETL for a datawarehouse. If a source system has a lack of good dataquality, DQS can improve the quality of data during the load into the datawarehouse.

On MSDN i learn that DQS contains three technical components:

DQS Server. Two databases :

DQS_MAIN. DQS_MAIN db contains all the DQS stored procedures, its engine and the published knowledge bases.
DQS_PROJECTS. The DQS_PROJECTS db contains all the data quality projects information.

DQS client. The DQS client is a standalone application that enables you to connect to a DQS server, providing you with a GUI to perform your data-cleansing tasks, as well as other administrative tasks related to DQS.
DQS Cleansing SSIS. The DQS Cleansing SSIS component enables you to integrate the DQS functionality into an SSIS data flow, for batch execution.

Installation

1. Install DQS Server
The first thing we have to execute is installing the Data Quality Server. The installer seems to be available in the start menu. A more obvious place would be the installer of SQL Server 2012 but perhaps this is a temporary solution.

A master key is needed for installing.

And a succes is reported:

Let's start the SQL Server Management Studio and see whether the databases have been installed:

And.....

There seems to be another database present : DQS_STAGING_DATA.

2. Grant DQS roles

According to the video i need to grant a user administrator rights.

3. Turn on TCP/IP with configuration tool

No window available.

For installing this on a VM step 2 and 3 are not necessary.

Using DQS client
Now its time to use the DQS client. As said earlier, the DQS client is a stand alone application that can be used by power users to create a knowledge base and business rules to check the dataquality.
1. DQS Client
First thing we have to do is starting the DQS client.

Click on the Connect button and the following window is presented:

There are three areas that are interesting:

Knowledge base Management. This is a important part of the DQS infrastructure. In this area we define the rules when the data is validated.
Data Quality projects. Create a new DQS project.
Administration. This covers Activity Monitoring and Configuration and is used for monitoring the usage of the knowledge base and connecting to 3rd party reference databases.

2. Open a Knowledge base
Let's take a look whether this a pre installed Knowledge base.

Here you can see an example of a table with domain values that should be converted to the right country.

Okay let's try to create our own new knowledge base and for this test i'll be creating a domain list of two dutch places : Amsterdam and Utrecht.

And the following window appears:

Once the above step is completed we will proceed with the Domain Management Section. We need to create a new domain.

Enter a Domainname. The window below is more extended than the CTP3.

Press Ok and the following window appears.

Create a new domain

Enter the values. There is a distinction between the valid value and the incorrect values that needs to be converted to correct value. For instance 'Ams' is converted to 'Amsterdam'.

You need to publish this to the DQS Server.

Succeeded as the window shows:

The new knowledge base is present in Recent Knowledge base window

When this is finished it's possible to create a DQS project. This seems an extra possibility for the poweruser to correct some values for themselves. As stated earlier, there is also a DQS SSIS component that can be used for quality checks in the ETL proces.

Give the project a proper name:

Define the DQS project in more detail like where is the source file and where do you want to store the results:

This is the source table:

This is the window when all information is entered.

Next.

After pressing the start button the following window appears.

Categorizing the values.

And here are the results:

And the table in SQL Server shows the following information:

Conclusion
DQS seems a great tool in the Self Service BI approach. The poweruser can maintain the knowledge base with little help of IT and the ETL developer can use this DQS functionality in the ETL process. This will aid the dataquality of the data. So the ETL developer will be more responsible for building the ETL package and the poweruser is content responsible.

In one of the following blogposts i'll investigate the SSIS DQS component.

Hennie

Handling NULL in dimensions and measures

2011-11-27T14:11:00.000+01:00

A small post about 'Unknown' and 'Not Available' values in datawarehouse solutions. I've a requirement of my customer about this issue. The customer wants to see whether a value of a field in the sourcetable is empty (= NULL) or whether the value is not present in the dimension. There is a distinction between the two types of mismatching. The same problem is there with measures. They also want to see whether a measure value is NULL or whether it has a valid value. This way it's possible to monitor the dataquality of the source in a more sophisticated manner.

Normally you create one 'empty' row in your dimension called 'Unknown' (for handling NULLs and false dimension key lookups) and this a good solution under normal circumstances.But as said in my introduction, a more refined solution is needed. In my opinion the following distinction can be made:

Dimension.

-2 , "Not available" like in NULL. There is no value present in the source. In case of date dimension use a value like 1898, 1899 or 2098,2099 as a default value for unknown and/or not avaliable dates. In some sourcesystems i've seen that a '' (two single quotes) is implemented. You could include this check in the same logic for the "Not available" code too.
-1 , "Unknown" when the application key is not present in the dimension application lookup field. In this case there is a value present but it is not (yet) present in the dimension.

Measure.

When not filled in, use 0 because this won't bother aggregations like summing.
Use an extra indicatorfield <field>_AvailableYN to indicate whether the measure is available or not (NULL). Create a DD dimension and drop this field in here.

Below a diagram is shown about a dimension lookup scenario. One NOT AVAILABLE and one UNKNOWN scenario.

When the column in the sourcetable is NULL it will insert a -2 into the fact table. When a value is present in the column in the source but the dimension lookup failed an unknown value (-1) is inserted in the fact table.

Powerusers should be aware of these choices when they build reports.If they are used to 'UNKNOWN' values, now they have include some logic for the NOT AVAILABLE values in the reports too.

An interesting article about dummy values can be downloaded here

Greetz,
Hennie

SSRS : Adding process date of a cube to your report

2011-11-11T21:44:00.000+01:00

Introduction
In this blogpost i'll explain how to add a date to your report when a cube is processed. I've a requirement of my customer that they want to show a date on the report when the cube is processed. In this blogpost i'll use a query that can be used for determining the date when the cube is processed.

The problem
The problem is that using the query :

SELECT LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES WHERE CUBE_NAME = '<cube>'

can't be done in a standard manner, like using MDX queries in the MDX query window in reporting services

Below i'll show the failed attemps and a successful attempt.

Attempt 1 : Query designer
I'll show what happens when i try to add the query to the MDX query designer. First step is to open the query designer in the report and press the designbutton:

The next step is going to Query mode and add the query to window. When Ok is pressed the following error occurs:

So this a blind alley!

Attempt 2 : Expression
The next attempt i tried was using the expression button and that seems to work

And resulting in a value:

But when i try to open de expression designer again:

An error occurs:

And i have to select a cube:

And again an error occurs

Again a blind alley....

The solution
An solution i've found is descibed below. Go to the query designer and press the DMX editor:

Go to the designer button and press on it:

Copy the query in it and press OK.

And we have a working last cube processed date:

Conclusion
I don't understand how and why but it seems to work and i've implemented this in my reports. But i've to say it's a bit strange.

Greetz,

Hennie

SSAS : Selecting facts with reference dimension

2011-11-05T20:49:00.000+01:00

Introduction
In this blogpost i'll explain a problem i have at a customer. This problem is about relations between stars, facts and dimensions. To be more specific: selecting values of a fact with a (non conformed) dimension that is not directly linked with the fact but to another fact. In this blogpost i'll try to explain some backgrounds, the problem, a solution and finally some conclusions about what i have encountered during this investigation.

This is the second blogpost about this issue in a serie of 3. In this blogpost i'll describe a solution that is a better solution than i've described in a former post.

The problem
In this section i'll decribe the problem in more detail. There is a wish from my customer about selecting values of a fact with a dimension that belongs to another fact. This is not a conformed dimension and i think this is the cause of the problem. As i'm reading the book "The Microsoft data warehouse toolkit, second edition" about conformed dimensions it says : "In order to combine information from two facts it is requirement to have a common dimension with the same keys. This is called drilled accross".

Suppose i've the following situation (SQL Server tables):

The Type dimension is not a conformed dimension because it is only shared by one fact and not more facts. A key of FactA is also present in FactB and therefore a dashed line is drawn. This will be the base for drawing connectin the Type dimension with FactB.

The diagram above typically descibes a situation as a 1:n relation between facts, for instance order (FactA) and orderlines (FactB). For this purpose a degenerated dimension table is created, which is a 1:1 dimension of FactA (order) and a 1:n with FactB (orderlines) A date dimension is also included.

Now, the problem is about selecting FactB records with the Type dimension.This can't be done by standard operating of the SSAS engine, unfortunately.

In SQL query:

The solution
On the MSDN forum i was pointed to usage of the reference dimension. The referenced dimension is one of the six different types of relationships as i can see on the MSDN page regarding Referenced dimension. The first lines about a referenced dimension: "A reference dimension relationship represents the relationship between dimension tables and a fact table in a snowflake schema design. When dimension tables are connected in a snowflake schema, you can define a single dimension using columns from multiple tables, or you can define separate dimensions based on the separate dimension tables and then define a link between them using the reference dimension relationship setting."

So i decided to implement a reference dimension in a different way than i have descibed in my former post. Now i've removed the materialized DD dimension table in SQL Server and use a direct relation from FactA to FactB.

The cube structure is shown in the screenshot below. As you can see now there is a direct relaltion between FactA and FactB. Field FK_FactA_DDkey is present in both fact tables FactA and FactB and is set as a foreign key in FactB.

Below a screenshot from the dimension usage tab of the cube. Here you can see the usage of the reference dimension relationship. The DimType is connected with the FactB because of the reference dimension FactA DD.

Below the "Define relationship" window that pops up when a you click on the box of the intersection of measure group and the Type dimension on the cubestructure tab.

In order to get the reference dimension relationship working, I had to add the FK Type Key to Fact A DD dimensio too.

And the results are:

Correct again!

Conclusion
In contrast with the first solution i've described in the blogpost titled: "SSAS: Selecting facts with different dimensionality" this is a neat solution without a extra physical SQL table, the FactDD dimension. Because of this implemented solution, dimensions from FactA can benefit of the reference relationship between FactA and FactB and therefore a powerful solution it is.

Advantages :

No materialized table used and therefore:

Less maintenance.
Less storage.
Less joining.

Easy to build.

Disadvantages

Perhaps that the cube structure is a bit different than the physical tables and less debug opportunities(?).

Greetz,
Hennie

SSAS: Selecting facts with different dimensionality

2011-10-30T19:34:00.002+01:00

Introduction
Back from holiday and all together with going live at a customer it has been quite busy the last month. But for now there is some more time to blog about certain issues. In this blogpost i'll explain a problem i have at a customer. This problem is about relations between stars, facts and dimensions. To be more specific: selecting values of a fact with a (non conformed) dimension that belongs to a another fact. In this blogpost i'll try to explain some backgrounds, the problem, a solution and finally some conclusions about what i have encountered during this investigation.

This is the first blogpost about this issue in a serie of 3. In this blogpost i'll descibe a solution that is a nice solution but in the next blogpost i am describing an even better solution.

The problem
In this section i'll decribe the problem in more detail. There is a wish from my customer about selecting values of a fact with a dimension that belongs to another fact. This is not a conformed dimension (related) and that is the cause of the problem as i' ve read in the book "The Microsoft data warehouse toolkit, second edition" about conformed dimensions it says : "In order to combine information from two facts it is requirement to have a common dimension with the same keys. This is called drilled accross". In other words there are dimensions that are unrelated to a measuregroup

Suppose i've the following situation (SQL Server tables):

The Type dimension is not a conformed dimension because it is only shared by one fact and not more facts (there are more rules, offcourse). The FactA DD dimension is a shared dimension and therefore a conformed dimension.

The diagram above typically descibes a situation between a 1 : N relation between facts, for instance order (FactA) and orderlines (FactB). For this purpose a degenerated dimension table is created, which is a 1:1 dimension of FactA (order) and a 1 : N with FactB (orderlines). FactB also contains the same technical key as FactA for this degenerated dimension (FactA DD). A date dimension is also included.

Now the problem is about selecting FactB records with the Type dimension.This can't be done by standard operating of the SSAS engine, unfortunately.

Setting up the lab situation
In order to study this situation i've created a small labsituation in SQL Server. Below you can see the script i've used.

--FactA (eg. Order)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactA]') AND type in (N'U'))

DROP TABLE [dbo].[FactA]

CREATE TABLE [dbo].[FactA](

[FactAID] [int] IDENTITY(1,1) NOT NULL,

[FK_Date_Key] [int] NOT NULL,

[FK_Type_Key] [int] NOT NULL,

[FK_FactA_DDKey] [int] NOT NULL,

[FactACount] [int] NOT NULL

) ON [PRIMARY]

--FactB

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactB]') AND type in (N'U'))

DROP TABLE [dbo].[FactB]

CREATE TABLE [dbo].[FactB](

[FactBID] [int] IDENTITY(1,1) NOT NULL,

[FK_Date_Key] [int] NOT NULL,

[FK_FactA_DDKey] [int] NOT NULL,

[FactBCount] [int] NOT NULL

) ON [PRIMARY]

--DimDate

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))

DROP TABLE [dbo].[DimDate]

CREATE TABLE [dbo].[DimDate](

[Date_dim_key] [int] NULL,

[Date_Year] [int] NULL

) ON [PRIMARY]

--DimType

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimType]') AND type in (N'U'))

DROP TABLE [dbo].[DimType]

CREATE TABLE [dbo].[DimType](

[Type_dim_key] [int] NULL,

[Type_Code] [int] NULL,

[Type_Description] [varchar] (25) NULL

) ON [PRIMARY]

--Date

INSERT INTO dbo.DimDate

VALUES (1,2000),

(2, 2001),

(3, 2002),

(4, 2003),

(5, 2004),

(6, 2005),

(7, 2006),

(8, 2007),

(9, 2008),

(10, 2009),

(11, 2010),

(12, 2011),

(13, 2012),

(14, 2013),

(15, 2014),

(16, 2015)

--Order

INSERT INTO [dbo].[FactA]([FK_Date_Key],[FK_Type_Key], [FK_FactA_DDKey], [FactACount])

VALUES (12, 1, 1, 1), (12, 1, 2, 1), (12, 2, 3, 1),(12, 3, 4, 1), (12, 2, 5, 1)

--Orderlines

INSERT INTO [dbo].[FactB]([FK_Date_Key], [FK_FactA_DDKey], [FactBCount])

VALUES (12, 1, 1), (12, 1, 1), (12, 2, 1),(12, 3, 1), (12, 3, 1)

--Type

INSERT INTO [dbo].[DimType]([Type_Dim_Key], [Type_code], [Type_Description])

VALUES (1, 1, 'Type 1'), (2, 2, 'Type 2'), (3, 3, 'Type 3')

SELECT

Type_Code,

Type_Description,

FA.[FK_FactA_DDKey],

FactACount,

FactBCount

FROM DimType T

LEFT OUTER JOIN FactA FA ON T.Type_dim_key = FA.FK_Type_Key

LEFT OUTER JOIN FactB FB ON FA.FK_FactA_DDKey = FB.FK_FactA_DDKey

I know that it's not a best practice querying factables directly but this is a labsituation and the degenerated dimension will be created in the cube. The last query of the script results in :

So, now i can query these tables and i can filter the records of FactB with some of the Type dimension values so this would also be possible in the cube right? The next step is building the cube :

When we browse the cube the following information is given:

The results doesn't seem right because i would have expected a value for Fact B Count, Type 1 = 3, for Type 2 = 2 and for Type 3 = 0.

Solution
In order to use a filter on both facts we need to change the structure of the table (for the first solution). Let me draw the first solution. In this solution the Type dimension is connected with the DD dimension.

In the cube designer the solution looks like this:

On the MSDN forum i was pointed to usage of the reference dimension. The referenced dimension is one of the six different types of relationships as i can see on the MSDN page regarding Referenced dimension. The first lines about a referenced dimension: "A reference dimension relationship represents the relationship between dimension tables and a fact table in a snowflake schema design. When dimension tables are connected in a snowflake schema, you can define a single dimension using columns from multiple tables, or you can define separate dimensions based on the separate dimension tables and then define a link between them using the reference dimension relationship setting."

In case of a referenced dimension there are a couple of settings that needs to be set:

Reference dimension information.
Intermediate dimension.

In the diagram below you can see (one the two) reference relationship window:

Below, specifically the design of the DD dimension is shown:

And below you can see the results of this solution:

And that is correct!

Conclusion
Somehow it sounds a bit strange that a measure of fact (B) can't be selected by a dimension (type) of another fact (A) when the SSAS engine is operating in it's default behaviour. In SQL can quickly create a query that joins the tables and that gives the desired result. In order to make this happen in the SSAS engine you need to do more than using the default settings of the cube structure. In my next blogpost i'll explain a better solution for this problem.

Greetz,

Hennie

SSRS: 5 Attempts and 2 solutions with reportfilter and multiple conditions (grmph)

2011-09-20T20:54:00.004+02:00

Introduction
This is annoying me for a long time now: filter records based on multiple conditions in Reporting Services (SSRS). I'm working with power users and they are using reportbuilder 2.0 and they want to select multiple conditions for rows in a tablix. Simple? Intuitive? Easy for endusers? Not quite (in my opinion).

The problem

Attempt 1
Suppose i've a simple fact with a department dimension and i've 5 factrecords. I know that department A and E has a factrecord. Therefore i'm using them to test this filterbehaviour. Now i want to select two departments with the report filter. On Technet i've found the following example :

The first thing (as most of us will do and as my poweruser were doing) is trying Department_Description = Department A, Department E

and no results appear. Okay, well i know something about SQL and there is an IN operator available for this kind of filtering. In SQL you willl type something like this :

WHERE Department IN ('Department A', 'Department B')

Attempt 2
So the next thing i tried was the IN operator

But no luck here too.

Attempt 3
The next step is using quotations

This gives me some errors. Now what? Coming back to the Filter window shows this:

Reporting Services has changed the constraint to this : """"Department A, Department B"""". That seems a bit awkward. I think that three " are needed for escaping other " characters. When i'm building dynamic sql i need to use these too.

Attempt 4
Perhaps single quotes?

Nope!

Attempt 5
Round Brackets perhaps?

Nothing again... grmph...Getting desperate here! When i return back to the window every time """" are added to the condition.

The solutions (?)

Solution 1
On MSDN i've found an example. I need to set the expression to =(Fields!Department_Description.Value = "Department A" OR Fields!Department_Description.Value = "Department E")

and set the Filter to boolean.

Solution 2
Another solution i've found on a site of data inspirations :

And the expression is : =SPLIT("Department A" + "," + "Department E", ",")

Conclusion
The solution proposed on the MSDN forums is not very intuitive. The other example from the site data inspirations is a bit more intuitive but you have to use the SPLIT function.

Reportbuilder is used by end users for building Self Service BI reports, but this filter functionalitity is hard to understand. I hope Microsoft do a better job building the successor of Reportbuilder.

Greetz,
Hennie

SSAS : Field (Y/N) 'a' in Fact A impacts on rows Fact B

2011-09-18T10:08:00.001+02:00

Introduction
I've a requirement from a customer about combining facts and selecting them with a Y/N field. This blogpost descibes this requirement and the translation in a solution. This blogpost is simplified version of the 'real world' situation. Suppose you have two facts : 'A' and 'B'. Fact A has a field 'a' and this field can be Yes or No. The user wants to use this field to limit the rows in Fact B, somehow.

Options
When i started to investigate this problem, i came to three options:

Put the Yes/No field a in a separate dimension and use the dimension in both facts
Load the Yes/No field during the ETL in both facts.
Create an view where the two facts and the field are combined.

In my opinion option 1 is more rigid option than option 2. Option 2 is more flexible because i've to ability to set field a in Fact A and B independant. I've not investigated option 3, furthermore.

In this post i'll explain option 1.

The problem

First i'll describe the initial situation i've created. Below you can see my cube with the two facts A and B, two dimensions DimDepartment and Dimdate and the involved field ShowAllYN that should impact on both facts

When i browse the cube in SSMS, select field Fact A Count and Fact B Count and watch the result per department :

The results of Fact A are limited, but the results in Fact B aren't (off course). So, how can i achieve that field "Show All YN"impacts both on FactA and FactB?

Solution (option 1)
The solution that is described in this blogpost is option 1. In option 1 a separate dimension is created, named DimFactA_DD dimension. The ShowAllYN field is not available anymore in fact A, but moved to this DD dimension. Normally you would create a Fact cubedimension for this kind of degenerated dimension fields. But in this case we need to create a separate (regular) dimension.

So the cube structure is now changed to the following structure:

And how does this look like in the cube browser? First let's look at the whether everything works ok when i don't use the DD dimension:

The next step is using the DD dimension for both facts:

As you can see selecting the Y/N field has an impact on the measures now, just as we wanted.

Conclusion
In this post I've described one option that can be used for selecting multiple facts based on one field. There are many variations on this topic. One variation could be that you leave the Y/N field in the FactA, add one in Fact B and use the combined DD dimension. This way a very flexible solution can exist, but you have some redundancy.

I've implemented option 2 at the customer and this gives me more flexibility than option 1 but the solution descibed in this blogpost is a much nicer solution in my opinion.

Greetz,
Hennie

SSAS : Setup dynamic dimension security in Analysis Services

2011-09-12T17:31:00.001+02:00

Introduction
In SSAS implementations there is always a demand for implementing security. There are multiple possible security options and this blogpost focuses on data level security or row level security : security that depends on the useraccount and connects users to certain rows in a table. Users needs to be managed as new users appear, some will disappear and from some users may change their role.

In this post i'll descibe the dynamic dimension security in SSAS i've built at a customer. It's also called 'attribute hierarchy security'. Dynamic dimension security is about allowing restrictive number of users to a certain set of information in your cube. So you need to create an allowed set of members that users in a role are permitted to access. In this blogpost i'll be defining a set by building a MDX query for showing departments for certain users.

In this post we will build a security based on a department dimension. The cube is secured by the department dimension, a bridge tabel and a user table. In addition, i have leveraged two local user accounts: SSASUser and SSASUser2.

Here are the steps that needs to be taken for a successful implementation of dynamic dimension security:

Create a table users with username field that should store the Windows accounts.
Create a bridge table that models the M2M relationship between users and department.
Fill the tables with appropriate values.
Add the tables to the datasourcce view of the SSAS project.
Create a user dimension.
Create a measure group for bridging the users and department.
Create regular relationships between users and the bridge userdepartment.
Create a new role in BIDS/SSMS.
Add MDX statement to the Allowed member set and set the Enable visual totals checkbox.
Deploy and test.

Setup
Below are the steps described that i've taken to build a dynamic dimension security.

1) Create two SSAS users. I'll be using these users for testing the dynamic dimension security. I've added these to the SSASusers and the SSASUsers2 group.

2) Create some tables in SQL and build a cube on the tables with the following structure:

There a couple of sections involved in this diagram: a fact called, FactTest, a M2M dimension and a bridge for the security. The FactTest is a small example with two dimenions Date and Location. The M2M dimension setup is a bridge (BridgeLocationDepartment) and a Department dimension. The requirements of my customer tells me that there is a n:m relation between location and department. For this blogpost i've added two tables : BridgeUserDepartment and user. Most of these tables are described in more detail in other blogposts about SSAS M2M dimensions. You can find them here:

3) So there is n:m relation between location and department, a fact table, a date dimension and especially for this blogpost i've added two extra tables and these controls the security on the cube: BridgeUserDepartment and Users. The BridgeUserDepartment table handles the n:m relation between department and users. The first thing to check is the relation between User and Department:

User_Dim_Key 1 is the SSASUser and 2 is the SSASUser2.

Querying with MDX the Bridge User Department, SSASUser and SSAUser2 shows the following results:

4) In this step w'll setup the dynamic security for this cube. We will create a new role, Dynamic Security, with read access to the ResearchAverage cube.

Add the SSASUser and SSASUser2 users to the membership tab.This step is a pitty one, in my opinion. Now i've to set something at two places: in the database and in the membership tab in Analysis.

Give read access to the cube:

The next steps are done in BIDS (denali) but it makes no difference where you setup the security (in SSMS or BIDS). Only when you setup the security in SSMS, it will be overwritten when the cube is deployed from BIDS. The next step took me a while to understand when i was working at the customer. In this customer situation there are about 50 dimensions and a lot of role playing dimensions. One error i keep on getting was

"An error occurred in the MDX script for the dimension attribute permission's allowed member set: The dimension '[User]' was not found in the cube when the string, [User].[UserName].[UserName].&[<user>], was parsed."

Because i had so many dimensions i didn't see the cube dimensions, at first. So i implemented the security at databasedimension level and not at cube dimensionlevel. The combobox "dimension" stores both database dimensions and cube dimensions. This is very confusing. I'm not completely sure that this also needed in this every situation. Anyway i've implemented the security at cubedimension level.

The following window does the trick. It limits the allowed set because of the usage of the UserName() function. This function returns a windows useraccount like Laptop\Hennie or Laptop\SSASUser.

I've left open the "enable visual totals" (lower left) intentionally and pressed on check (not shown in the window) and we have a....successful check!

The next step is running SSMS with the SSASUser account

And we can only see the departments that are authorized for the SSASUser:

The next step is running SSMS with the SSASUser2 account

And we can only see the departments that are granted by SSASUser2:

Okay, let's try another dimension like date and ...Now we can see all the information. That was not supposed to happen.

You can solve this with "enable visual totals" option.

And now only the facts are shown that belongs to a department:

Other things to do are making things invisible. The Bridge User Department measure and the User dimension provides a base for the security of the dynamic dimension security. These tables are only needed for the security and not for end user querying. So these two should be set to invisible. Important note: these are only hidden and when the end user is familiar with the presence of them, still they can query them.

Conclusions
There some things that are a bit annoying in my opinion:

It's not fully dynamic because i've to add the user to the membership tab also. To grant a user i've to add the user to the database and to the memership tab.
The distinction between regular and cube dimension did cost me a lot of time to find out, because i didn't see the cube dimensions at first sight (because the customer had about 50 dimensions).
Another problem i've encountered now is that i've a benchmark graph with all departments and the user can compare his department with all of the other departments. Now with this security only the departments which are granted to the user are visible. I'm still working on a solution for this. When i've a proper solution i'll blog about this.

So for securing the cube, dynamic security is a great solution for letting the users play with the cubes with Excel.

Greetz,
Hennie

SSAS : Set up simple security in Analysis services

2011-09-07T07:38:00.000+02:00

Introduction
In this post i will describe the basics of the security of a cube. For this purpose i'll be using the ResearchAverage cube i've worked before and is described in the following posts:

Security in SSAS is controlled by roles which allows you to group windows users according to their needs. (windows) Users are members in roles and that determines whether a user can access a cube (and how).

Setup
1) First set up a environment with the scripts i've used before (see former posts)
2) The next thing we need to do is creating a user with Computermanagement (Right click computer and manage):

3) Create a group SSASUsers and add a user to the group:

4) Create a role in Analysis Services

5) Add the user to the role

6) Select "Read" in the Access field:

7) Run as SSASuser a SQL Server Management Studio session:

8) Connect with the Analysis server with the SSAS user:

9) let's look at the databases :

No databases?

10) Let's try a query to check whether we can query the database. The first thing that happens when i press new query is that Database is automatically selected:

And let's try a query:

It seems that we can query the database without viewing the definition of the database.

11) let's try whether we can connect to the cube with Excel

12) Excel

Why can't we see the cube in SSMS? On the General tab there is a Read defintion checkbox

Can we see the cube now in SSMS?

Conclusion
This blogpost is about a simple security model. This way you can setup quite easily a basic security in a early stage of a project or in case of a small project.

Gr,
Hennie

SSAS : Selecting a dynamic period in a MDX query

2011-09-02T08:17:00.000+02:00

Introduction
In many customer wishes i've encountered so far, they want a dynamic report that shows the data until now. In SQL there is a GETDATE() function avaliable to build dynamic queries with dates. IN MDX there is a NOW() function available. In this small post i'll show you an example of a MDX query with a sliding window principle.

Solution
How could you solve this with MDX? For this post i've used a cube from a former post.

The most important part is the following snippet:

STRTOMEMBER('[DimDate].[Date Year].&[' + CSTR(YEAR(now())-3) + ']'):STRTOMEMBER('[DimDate].[Date Year].&[' + CSTR(YEAR(now())) + ']')

The NOW() function calculates the current date and the CSTR() funtion turns it into a string which in turn can be concatenated with the other string. The : is a range operator that returns a window from 2008 (2011 -3) until 2011 (now).

Conclusion
Very handy for building dynamic period reports with MDX.

Greetz,
Hennie

SSAS/SSRS : Calulating sums and averages with M2M dimensions

2011-08-28T21:29:00.004+02:00

Introduction
Today a post about calculating averages of departments and these measures are compared to a specific department. I have a requirement to build a report with a graph with values for a specific department. Another demand is to build a line in the graph with averages of all (?) of the departments of the organization. The report must allow the user to select the department from a dropdown list in a report parameter. Based on the selected department the report should show measures for the selected department and an average of all departments.

There is an extra 'dimension'(!) in this investigation and that is how an average is calculated when we are dealing with a M2M dimensions. As you may know from my former posts M2M dimensions are specific parts of the star schemas methodology and SSAS:

Before we begin
For this post i've created a lab environment for studying the average calculation of factrecords regarding M2M dimensions. I've used the same script from the post:"SSAS/SSRS : Building a graph for analyzing a subset related to the whole set". I've build a cube on these tables and it has the following structure:

SQL Analysis
Let's investigate the values in the relational tables before we investigate the OLAP cube with MDX. For this investigation w'll focus on department G and the year 2009.

1) Aggregating the results by year
In this first investigation of the totals by year shows that the FacTestCount is summarized to 35. This is a simple query and there is no involvement of the bridge table and the department dimension.

2) Aggregating the results by year by location
In this testquery i've added an extra dimension to the query: DimLocation. As you may have expected nothing changes because the location dimension is a 'normal' dimension. No cartesian product takes place (as will happen when we include the DimDepartment dimension in the next query).

3) Aggregating the results by year by location by Department
In the next query i've included the table DimDepartment and the bridgetable BridgeLocationDepartment and now something happens to the results: for the year 2009 the results have changed from 35 to 62. Because of the cartesian product we now have 62.

4) Aggregating the results by year by Department G
As earlier mentioned in this post w'll focus on Department G and the year 2009. In the query below you can see that we have 14 as the result of the query (for the year 2009):

Building the MDX statements
When building reports based on cubes you need to write MDX to get the data from the cube. This paragraph starts with an easy MDX query :

That is the result we would have expected because the SQL query returns also 14 for the year 2009 for department G. Now we need to write an MDX query that calculates the average (and summarization) of all departments by year. Below you can see the query i've written to calculate the following items:

[Measures].[Fact Test Count]. This measure is the count field in the fact and is used for summarizing (rolling up).
[Measures].[SUM Fact Test Count]. This a measure where the Fact Test Count is summarized by year and by department.
[Measures].[AVG Fact Test Count Found Departments]. This measure is calculated by taking the average of the departments that have a factrecord (and not all of departments present in the department dimension).
[Measures].[AVG Fact Test Count All Departments]. This measure calculates the average over all of the departments that are available in the Department dimension. Even when there a less records in the fact present related to a department.
[Measures].[AVG Fact Test Count All Departments2]. This measure is just a test of mine whether i could call a another Calculated member.

Okay what do we see now? We can see that the total of all departments in the year 2009 is equal to the cartesian product SQL query above (62). So that's is a desired outcome. The average by found department is 7.75 (62/8) and these are departments that do have a factrecord. The next measure is 6.2 and that is 62/10 because there 10 departments in the department dimension.

Building the report
The next thing to do is building a report with this MDX query. In this report i want to show the selected parameter in a graph and compare this to the average of departments and the sum. Perhaps not a best usability practice of building a graph but this just for showing what is possible with MDX averages, sums and graphs in reports. First build a report, create a datasource to the cube and build a dataset based on the query above. And the next thing is dragging a graph from the toolbox in the report and customize it as below. I've used the average by all departments for comparison of the chosen department in the parameter.

When you run the report it will look like this:

I'm not fully convinced whether i've used the right presentation of the values. I've to think about this ;-) What can we learn from such a diagram? Well, there are two years that the measure was below average and the other years showed a higher level than the select department. It depends on the context of the data what this mean, offcourse. If you're talking about profits then that it's a good thing. If you're talking about a number defects then it's a bad thing.

Conclusion

This post is about investigating the calculation of averages in relation with M2M dimensions. This post helped me understanding what is going on when i calculate an average of departments in MDX with M2M dimensions. In a discussion with my customer we talked about it and we were curious about what is being calculated in a report when an average is calculated. Does it calculate an average of the unique values or the values that multiplied by the cartesian product, does it calculate the averages over the found departments or the departments that are present in the department dimension.

Hennie

SQL : joining tables with the OUTER APPLY

2011-08-17T08:04:00.002+02:00

Introduction
In many of my projects i've done sofar, i had to combine tables in order to retrieve keys for my fact tables. When there is a perfect relation available, no worries, no problem. If this perfect relation is perfectly modelled then linking and retrieving proper keys is much more easier then when there is a relation that is not based on proper relation modelling. Sometimes there are cases when you need to link sources from different departments that do not have a common primary-foreign key. Sometimes you need to link tables based on some descriptive fields that are used in both tables. The second problem is that when you do have some fields available for 'fuzzy linking', they return multiple rows back. Returning multiple rows back can cause wrong counts in your fact table. Therefore you need to align the counts of the "default fact source table" with the 'referenced table'.

For this post i've developed a small example in such a way that it represents the problem i want to examine. In the diagram below the order table is the table that is used as a source for the fact table and i need the department from the order log table to incorporate this in the (to be build) fact table.The relation is based on ''fuzzy linkage" : customer code and a date. And, i need the department key in my fact table. The problem is that there are (for the same customer and date) multiple records with different departments. Therefore a simple distinct won't work.

In the past i've solved this with a double subqueries and this is not a good solution because the performance is bad. I decided to investigate this problem in more dept in this post. In this post i'll discover the different options that are available to solve this problem.

I've four queries developed and these are investigated in this blog.

Option 1 : double subquery.
Option 2 : joining with subquery.
Option 3 : CTE solution.
Option 4 : OUTER APPLY solution.

The case
This is the script i've used for analyzing the queries:

USE [FuzzyLinking]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblOrder]') AND type in (N'U'))
DROP TABLE [dbo].[tblOrder]
GO

CREATE TABLE [dbo].[tblOrder](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [Datetime] NOT NULL,
[CustomerCode] [varchar] (10) NOT NULL,
[Amount] [int] NOT NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblOrderLog]') AND type in (N'U'))
DROP TABLE [dbo].[tblOrderLog]
GO

CREATE TABLE [dbo].[tblOrderLog](
[OrderLogID] [int] IDENTITY(1,1) NOT NULL,
[OrderLogDate] [datetime] NOT NULL,
[CustomerCode] [varchar] (10)  NOT NULL,
[DepartmentCode] [int] NOT NULL
) ON [PRIMARY]
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDepartment]') AND type in (N'U'))
DROP TABLE [dbo].[tblDepartment]
GO

CREATE TABLE [dbo].[tblDepartment](
[DepartmentCode] [int] NOT NULL,
[DepartmentDescription] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
---

INSERT INTO dbo.tblDepartment ( DepartmentCode, DepartmentDescription) 
VALUES 
(1, 'Department A'),
(2, 'Department B'), 
(3, 'Department C'), 
(4, 'Department D'), 
(5, 'Department E')



INSERT INTO [dbo].[tblOrder] ([OrderDate],[CustomerCode],[Amount])
VALUES 
('2011-08-15 19:40:14.163', 'A', 20.95),
('2011-08-15 05:40:14.554', 'B', 99.54),
('2011-08-16 18:35:25.143', 'B', 12.55),
('2011-08-17 17:23:11.123', 'C', 15.00),
('2011-08-18 23:12:18.434', 'A', 25.00),
('2011-08-19 17:34:12.345', 'A', 27.00);
           

INSERT INTO [dbo].[tblOrderLog] ([OrderLogDate],[CustomerCode],[DepartmentCode])
VALUES ('2011-08-15 20:40:14.163', 'A', 1),
('2011-08-15 22:12:11.163', 'A', 2),
('2011-08-15 05:55:14.554', 'B', 2),
('2011-08-16 19:35:25.143', 'C', 3),
('2011-08-17 18:23:11.123', 'C', 3),
('2011-08-19 00:12:18.434', 'A', 2),
('2011-08-19 18:12:18.434', 'A', 3),
('2011-08-19 18:12:18.434', 'A', 4),
('2011-08-20 00:12:18.434', 'A', 5);

Analysis
When i write a simple join between the Order and Orderlogtable based on 'customercode' and 'day'



SELECT 
O.OrderID,
O.OrderDate,
O.CustomerCode,
OL.OrderLogID,
OL.OrderLogDate,
OL.DepartmentCode
FROM tblOrder O
INNER JOIN tblOrderLog OL ON O.CustomerCode = OL.CustomerCode 
AND CONVERT(varchar(8),O.[OrderDate], 112) = CONVERT(varchar(8),OL.[OrderLogDate], 112)

this is resulting in :

As you can see we have multiple OrderID's and this is not a good situation for using this in a fact table. Instead of 6 fact records we now have 9 records and this is caused by the cartesian poduct. This is not a good base for a fact table. So we need some additional work here.

The queries

Option 1
Okay, the first solution here is the one i've used in some of my projects (i've to admit) and that is this query:

SELECT 
O.OrderID,
O.OrderDate,
O.CustomerCode,
D.DepartmentCode
FROM tblOrder O
LEFT OUTER JOIN (
        SELECT OrderLogDate, CustomerCode, DepartmentCode
        FROM tblOrderLog
        WHERE OrderLogID IN
                (SELECT Min(OrderLogID)
                FROM tblOrderLog
                GROUP BY CONVERT(varchar(8),[OrderLogDate], 112) , CustomerCode)) D ON CONVERT(varchar(8),O.[OrderDate], 112)  = CONVERT(varchar(8),D.[OrderLogDate], 112) 
AND O.CustomerCode= D.CustomerCode

and this returns :

And this is ok. Now i've 6 orders back again. I choosed to take the first orderlog record and the related department. This depends on the businessrules, off course.

I'm not very satisfied with this solution because two subqueries doesn't seem a good (and a fast) solution to me.

Option 2
Another option i've researched is the query below. In this query i've reduced the subqueries to one.


SELECT 
O.OrderID,
O.OrderDate,
O.CustomerCode,
OL.DepartmentCode
FROM tblOrder O
LEFT OUTER JOIN tblOrderLog OL ON OL.OrderLogID =
        (SELECT TOP 1 OrderLogID
        FROM tblOrderLog OL1
        WHERE CONVERT(varchar(8),O.[OrderDate], 112)  = CONVERT(varchar(8),OL1.[OrderLogDate], 112) 
        AND O.CustomerCode= OL1.CustomerCode
        ORDER BY   O.CustomerCode, CONVERT(varchar(8),OL1.[OrderLogDate], 112))

And this returns the same values as the query in option 1:

Option 3
Yet another option i've found is the query written down below. In this query the subsubquery is now vanished. Instead we have now a Common Table Expression (CTE) used.

WITH CTE_order AS
(SELECT 
O.OrderID,
O.OrderDate,
O.CustomerCode,
OL.DepartmentCode,
ROW_NUMBER() OVER (PARTITION BY O.OrderID ORDER BY O.OrderDate) AS RowNo
FROM tblOrder O
LEFT OUTER JOIN tblOrderLog OL ON CONVERT(varchar(8),O.[OrderDate], 112)  = CONVERT(varchar(8),OL.[OrderLogDate], 112) 
AND O.CustomerCode = OL.CustomerCode)
SELECT
OrderID,
OrderDate,
CustomerCode,
DepartmentCode
FROM CTE_order
WHERE  RowNo = 1 OR  OrderDate IS NULL;

Also resulting in the same results as we've seen before.

Option 4
In the next option i'm using an OUTER APPLY. The OUTER APPLY operator is used for combining tables when there is not a direct JOIN ON possible and you need to join tables based on some fields that are not keys, like in this case. So this seems a perfect match, solving this problem with the OUTER APPLY. This is the query:

SELECT 
O.OrderID,
O.OrderDate,
O.CustomerCode,
O1.DepartmentCode 
FROM tblOrder O
OUTER APPLY 
( 
SELECT TOP 1 OrderLogID, DepartmentCode 
FROM tblOrderLog OL1
WHERE CONVERT(varchar(8),O.[OrderDate], 112) = CONVERT(varchar(8),OL1.[OrderLogDate], 112) 
AND O.CustomerCode= OL1.CustomerCode
ORDER BY O.CustomerCode, CONVERT(varchar(8),OL1.[OrderLogDate], 112)
) O1

Resulting in.....Yep the same as before :

Comparing the queries
As i've read about the OUTER APPLY it has a better performance than subqueries or other solutions. Is this really true? Let's find out. I took the four queries and included the actual query plan and compared the costs. Studying the actual execution plan indicates that the OUTER APPLY is best option (of the four). As i already assumed the double subquery is a bad performer.

Conclusion
I've compared 4 options regarding this specific problem. The problem was about (what i call) 'fuzzy linking' between two tables and retrieving an appropriate key for the fact table. Below you can see the results in a graph and as i already thought : the double subquery is worst option (option 1) and the best solution (of the 4) is option 4 (the OUTER APPLY):

I've learned a bit more about SQL Server and the OUTER APPLY. I do think that i'll be using this feature more often in tasks like joining tables based on some not key fields.

Greetz,
Hennie

SSRS: Building a report on an excel datasource with Reportbuilder 2.0

2011-08-12T08:01:00.001+02:00

Introduction
Today a small tutorial about using an excel file as a source for a report with reportbuilder 2.0.The following steps should be followed when you're creating a report based upon an excel file.

1) Create an excelsheet with som testdata.
2) Create an ODBC DSN to access the excel file.
3) Create a datasource in reportbuilder.
4) Build a dataset and a report.

1) Create an excelsheet with some testdata
First, create a simple excel sheet with data with Excel 2007. We will build a report based on this data. Save this file under the name ImportantInformation.xlsx.

2) Create an ODBC DSN to access the excel file
As the title of the post suggested we are gonbe build a report based on an Excel sheet based on an ODBC connection. The details of how to do is listed here:

Click on Start, All Programs, Control Panel, Administrative Tools, Data Sources (ODBC). If the tabbed page is not in User DSN, change the tab to User DSN and click on the Add button.

3. Scroll down and highlight Microsoft Excel Driver (*.xlsx) and click on the Finish button.

This opens the ODBC Microsoft Excel Setup window as shown.

Press on "Select Werkbook" and select the Excel sheet

Press Ok and the excel file will appear in the User DSN screen

Click on OK and the window closes.

3) Create a datasource in reportbuilder
The ODBC connection has been created in the last step and now it should be possible to create a datasource in reportbuilder and connect it to the ODBC connection. Lets create a report based on this ODBC. Choose New Data source.

Set the properties Name (i didn't) and choose a connection embedded in my report. Press Build in order to create the connection string.

Choose "Use connection string" and press build (again(?)) and select the ImportantInformation.xlsx

Press OK and the Select Workbook will come up. Locate the excel sheet (i've stored mine on E:\Excel)

Press ok and Press "test connection"

And the connection string is build

The connection string is as follows:

Dsn=ImportantInformation.xlsx;dbq=E:\Excel\ImportantInformation.xlsx;defaultdir=E:\Excel;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5

Click on Ok en we're entering the next universe.

4) Build a dataset and the report
The next step is create a simple report based on the data in the Excel sheet. Select "Table or Matrix".

The following window appears and you're asked for selecting the right datasource. In our case only one is created and we can't choose the wrong one here.

The next step is the most important one. Took me some trails before i got it right. Type SELECT * FROM [Sheet1$] (if you named the tab "Sheet1"). Press ! and the query is executed.

The wizard continues...

Put some fields in the details section and Column1 in the row groups. I deselected the sum.

Choose appropriate layout.

Choose appropriate style

Press finish

Conclusion
Easy to do and this is enabling power users building reports on Excel data. Opening and closing the excel file when building the report result also in error messages in Report builder 2.0 (Something like connection lost with the excel file). This is an error i recieved when i tried to open the excel file when Reportbuilder is using the excel file:

Greetz,
Hennie

SSAS/SSRS : Building a dashboard

2011-08-10T07:59:00.003+02:00

Introduction

Currently reading "Information dashboard design" of Stephen Few. A book i can highly recommend to you when you are interested in hands on advice, best practices about building dashboards. We all know the dashboardhype: everybody wants a dashboard. Dashboards are sexy. Building a good dashboard is about giving the users a unique and powerful means to presenting information, but they rarely fullfill their promisses. Too bad, because building a dashboard according to the usability rules can greatly enhance insights. During my study i became interested in building applications according to the usability laws that were researched by Don Norman, for instance. One book of him, that is very easy to read and give you great insights of the (bad) design of everyday things, is his famous book : The Design of Everyday Things. In this book he explains some bad examples and some good examples of design of normal things that surrounds us. Great book and when your designing dashboards it could give you some background information.

In this post i'll describe building a piece of a dashboard. In the coming weeks, months i will regularly post some blogs about building pieces of dashboards according to best practices or some great ideas that i've seen in the book of Stephen Few. While reading the book of Stephen Few i got interested in building a sober dashboard and i would like rebuild them in Reporting Services. One example i find particularly interesting. You can find this on page 201, left under. Below you i will show you the end result that i've build with Reporting Services.

First
At first we need to set up a environment in which we can build a dashboard. For this purpose i've created the following SQL Script :


USE [dashboard]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactSales]') AND type in (N'U'))
DROP TABLE [dbo].[FactSales]
GO

CREATE TABLE [dbo].[FactSales](
[FactSalesID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Department_Key] [int] NOT NULL,
[FK_Product_Key] [int] NOT NULL,
[FactSalesCount] [int] NOT NULL,
[SalesAmount] [money] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDepartment]') AND type in (N'U'))
DROP TABLE [dbo].[DimDepartment]
GO

CREATE TABLE [dbo].[DimDepartment](
[Department_Dim_key] [int] NOT NULL,
[Department_Code] [int] NOT NULL,
[Department_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimProduct]') AND type in (N'U'))
DROP TABLE [dbo].[DimProduct]
GO

CREATE TABLE [dbo].[DimProduct](
[Product_Dim_key] [int] NOT NULL,
[Product_Code] [int] NOT NULL,
[Product_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactPurchaseOrder]') AND type in (N'U'))
DROP TABLE [dbo].[FactPurchaseOrder]
GO

CREATE TABLE [dbo].[FactPurchaseOrder](
[FactPurchaseOrderID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Department_Key] [int] NOT NULL,
[FK_Product_Key] [int] NOT NULL,
[FactPurchaseOrderCount] [int] NOT NULL,
[FactPurchaseAmount] [money] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]
GO

CREATE TABLE [dbo].[DimDate](
[Date_dim_key] [int] NULL,
[Date_Year] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.DimDepartment (Department_Dim_key, Department_Code, Department_Description) 
VALUES 
(1, 1, 'Department A'),
(2, 2, 'Department B'), 
(3, 3, 'Department C'), 
(4, 4, 'Department D'), 
(5, 5, 'Department E')

INSERT INTO dbo.DimProduct (Product_Dim_key, Product_Code, Product_Description) 
VALUES 
(1, 1, 'Product 1'),
(2, 2, 'Product 2'), 
(3, 3, 'Product 3'), 
(4, 4, 'Product 4'), 
(5, 5, 'Product 5')

INSERT INTO dbo.DimDate
VALUES (0,2000),
(1, 2001),
(2, 2002),
(3, 2003),
(4, 2004),
(5, 2005),
(6, 2006),
(7, 2007),
(8, 2008),
(9, 2009),
(10, 2010),
(11, 2011),
(12, 2012),
(13, 2013),
(14, 2014),
(15, 2015)


INSERT INTO [dbo].[FactSales]([FK_Date_Key], [FK_Department_Key], [FK_Product_Key], [FactSalesCount], [SalesAmount] )
VALUES

(8, 5, 2, 1, 6.25),
(8, 2, 3, 1, 5.96),
(8, 3, 1, 1, 2.95),
(8, 1, 1, 1, 4.98),
(8, 3, 5, 1, 20.00),
(8, 5, 5, 1, 100.00),

(9, 3, 1, 1, 12.70),
(9, 2, 1, 1, 22.67),
(9, 4, 4, 1, 56.16),
(9, 5, 2, 1, 23.16),
(9, 2, 3, 1, 22.13),
(9, 1, 1, 1, 100.12),

(10, 1, 3, 1, 50.0),
(10, 2, 1, 1, 39.95),
(10, 3, 2, 1, 66.05),
(10, 5, 1, 1, 40.95),
(10, 3, 4, 1, 6.05),
(10, 5, 5, 1, 1.95),

(11, 1, 3, 1, 50.0),
(11, 3, 1, 1, 39.95),
(11, 3, 5, 1, 66.05),
(11, 1, 2, 1, 23.0),
(11, 3, 4, 1, 55.95),
(11, 3, 2, 1, 78.05)


INSERT INTO [dbo].[FactPurchaseOrder]([FK_Date_Key], [FK_Department_Key], [FK_Product_Key], [FactPurchaseOrderCount], [FactPurchaseAmount] )
VALUES

(8, 3, 5, 1, 66.67),
(8, 5, 1, 1, 25.00),


(9, 1, 2, 1, 21.95),
(9, 2, 5, 1, 80.45),

(10, 3, 2, 1, 51.02),
(10, 5, 1, 1, 30.05),

(10, 1, 3, 1, 21.00),
(10, 2, 1, 1, 19.95),
(10, 3, 2, 1, 21.05),
(10, 5, 1, 1, 15.95),

(10, 1, 3, 1, 60.0),
(11, 3, 1, 1, 30.95),
(11, 3, 5, 1, 31.05)

---
This script will create some tables and will insert some data into the appropriate tables.

Building the cube
The next thing we need to do is building a cube based on this datamodel. Below you can see the structure of this cube.

An image can you tell more than the SQL script above. So i'll explain the case with aid of this diagram. There are two facts Salesorder and purchase order. There are a couple of conformed dimensions :Department, Date and Product. The idea behind this that you need to purchase a product before you can sell it. I don't think i have the purchases in line with the sales. It is just an example. Another dimension POStatus is used for analyzing the status of a purchase order. Below the dimension usage tab:

Building the report
Okay, now it's time to build the report. The first thing is dragging the fields needed in the query designer into the query window.

Just plain and simple.

In the reportinfg services i've dragged a tablix on the report. In the dashboard example i've 7 columns and below you can see how i've created them in the tablix.

1) Product description (Desc)
The product descritption field in the table is simple. It's the product description of the product. This product description is used for grouping the products.

2) Last five years sparkline
I've created a sparkline graph based on the sales amount and a year column.

3) Number (# 2011)
In order to determine the current year i'm using the Globals!ExecutionTime as a rundate. The # column is calculated by summing the counts in tha fact table

=SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Fact_Sales_Count.Value, 0))

4) Amount ($ 2011)
As described at point 3, The salesAmounts are summarized by grouping the products.

   =SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0))

5) Percentage of total (% $/total (2011))
Calculating the perccentage betwee the productsales and the total product sales is done by the following expression

=SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0),   "grpProductDescription")/
SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0), "tblxSalesPerProduct")

6) Since last year
Calculating the percentage of growth or decline is a bit more difficult. Below you can see the expression i've created. Most of the expression is build around the demand that a - (minus) and + (plus) should be shown in case of a decline or a growth.

=IIF(

    SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0)) > SUM(IIF(Fields!DateYear.Value = YEAR(DATEADD("yyyy", -1, Globals!ExecutionTime)), Fields!Sales_Amount.Value,0)),
    "+" + FORMATPERCENT(CSTR(SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0))/
    SUM(IIF(Fields!DateYear.Value = YEAR(DATEADD("yyyy", -1, Globals!ExecutionTime)), Fields!Sales_Amount.Value,0))),0),

    "-" + FORMATPERCENT(CSTR(SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0))/
    SUM(IIF(Fields!DateYear.Value = YEAR(DATEADD("yyyy", -1, Globals!ExecutionTime)), Fields!Sales_Amount.Value,0))),0)
)

7) AVG $ (2011)
The last column that i've created is the avg $ (average sales). That is done by aggregating (sum) and dividing the amounts and the counts.

=SUM(IIF(Fields!DateYear.Value =YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value, 0))/SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Fact_Sales_Count.Value, 0))

Other Tablix adjustments
There are two more important adjustments to the report: sorting and filtering. The sorting is about sorting the products according their sales effort (regarding to the whole sales). The filtering is about the top 3 bestseller products.
Sorting
Sorting the products is possible by clikcing on the grouping of the products (and not tablix level).

Click on sorting.

And use the following expression for sorting the products. I only implemented the amount. That's enough.

Filter top 3
The last last change in the report is filtering the top 3 products. The filter option that is used is also on the group level.

Below you can see the expression that i've used for filtering

And the expression that i've entered in value field is (don't forget the =) :

=3

And end result.....

Conclusion
The example in the book of Stephen Few has a sparkline as the first column. I haven't read the complete book of Stephen and i do not know why he decided to put the sparkline in the first column but from usability view point i do think that product description should be the first colum. Humans read from top left to bottom right and they are interested in productdescriptions, first.

SSAS/SSRS : Building different gauges with MDX.

2011-08-03T08:27:00.001+02:00

Introduction
For one of my customers i've to build a dashboard. This customer wants to improve the quality of service. There are discussions about how do you measure quality, what is good service and what is bad service. The problem is that there are many departments and how do you compare them. Is it recommended to say below 50 is ok and above is not ok? So what is good and what is wrong? Just showing the number is also a bad practice (without any reference).

Another approach i've read in the book of Stephen Few : "Information dashboard design" and he showed an example of a gauge with an organization average and an industry best practice (benchmarking). Below you can see a diagram about combining the department average, organization average and an industry best practice. An other idea pops in my mind about comparing departments peer2peer with same departments of other organizations.

So in theory there are four measures possible for comparing values.

Department average.
Organization average.
Industry best practice.
Peer2peer department average (for later).

Red, orange and green imply a judgement of the figures. So i decided not use these colors. At this moment i'm using more neutral colors like purple and darkblue.

Prerequisites
Okay, let's setup the case. First setup the datamodel and insert some test data. Below i've drawn a star datamodel with two facts a department level fact and a benchmark fact (for showing the industry best practice average). The department level fact is a fact with data from the own organization and the benchmark fact is information that is gathered, stored, processed and distributed by an exernal benchmark organization. For simplicity i'll assume that the one measure of department level fact will be compared with the measure of the benchmark fact. In the real world there will be multiple organizations star schemas and they need to be coupled with the benchmark fact.

So there four dimensions : OrgDepartment, Date, organization and P2Pdepartment (for later blogposts). So i've created a script for this purpose.


USE [Gauge]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeptLevelFact]') AND type in (N'U'))
DROP TABLE [dbo].[DeptLevelFact]
GO

CREATE TABLE [dbo].[DeptLevelFact](
[DeptLevelFactID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Department_Key] [int] NOT NULL,
[DeptLevelFactCount] [int] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDepartment]') AND type in (N'U'))
DROP TABLE [dbo].[DimDepartment]
GO

CREATE TABLE [dbo].[DimDepartment](
[Department_Dim_key] [int] NOT NULL,
[Department_Code] [int] NOT NULL,
[Department_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BenchMarkFact]') AND type in (N'U'))
DROP TABLE [dbo].[BenchMarkFact]
GO

CREATE TABLE [dbo].[BenchMarkFact](
[BenchMarkFactID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Organisation_Key] [int] NOT NULL,
[BenchMarkFactCount] [int] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]
GO

CREATE TABLE [dbo].[DimDate](
[Date_dim_key] [int] NULL,
[Date_Year] [int] NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimOrganisation]') AND type in (N'U'))
DROP TABLE [dbo].[DimOrganisation]
GO

CREATE TABLE [dbo].[DimOrganisation](
[Organisation_Dim_key] [int] NOT NULL,
[Organisation_Code] [int] NOT NULL,
[Organisation_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
---

INSERT INTO dbo.DimDepartment (Department_Dim_key, Department_Code, Department_Description) 
VALUES 
(1, 1, 'Department A'),
(2, 2, 'Department B'), 
(3, 3, 'Department C'), 
(4, 4, 'Department D'), 
(5, 5, 'Department E')



INSERT INTO dbo.DimDate
VALUES (1,2000),
(2, 2001),
(3, 2002),
(4, 2003),
(5, 2004),
(6, 2005),
(7, 2006),
(8, 2007),
(9, 2008),
(10, 2009),
(11, 2010),
(12, 2011),
(13, 2012),
(14, 2013),
(15, 2014),
(16, 2015)

INSERT INTO dbo.DimOrganisation (Organisation_Dim_key, Organisation_Code,Organisation_Description) 
VALUES 
(1, 1, 'Organisation X'),
(2, 2, 'Organisation Y'), 
(3, 3, 'Organisation Z')

INSERT INTO [dbo].[DeptLevelFact]([FK_Date_Key],[FK_Department_Key],[DeptLevelFactCount])
VALUES
(10, 1, 3),
(10, 2, 5),
(10, 3, 8),
(10, 5, 1)


INSERT INTO [dbo].[BenchMarkFact]([FK_Date_Key],[FK_Organisation_Key],[BenchMarkFactCount])
VALUES
(10, 1, 3),
(10, 2, 5),
(10, 3, 7)

Building the cube
Based on the script above i've created a cube. This structure of the cube can be seen in the diagram below:

I've deployed the project and the next step is building the report.

Building the report
Now let's start another session of visual studio (BIDS) and to create a new report. I've added the following query to the dataset.

WITH
MEMBER [Measures].[avg measure by all departments] AS
    AVG({[DimDepartment].[DepartmentDescription].[DepartmentDescription]},
    [Measures].[DeptLevelFactCount])
MEMBER [Measures].[avg measure by department C] AS
    AVG({[DimDepartment].[DepartmentDescription].&[Department C]},
    [Measures].[DeptLevelFactCount])
MEMBER [Measures].[avg measure by all Organisations] AS
    AVG({[DimOrganisation].[OrganisationDescription].[OrganisationDescription]},
    [Measures].[BenchMarkFactCount])
SELECT 
{
[Measures].[avg measure by all departments],
[Measures].[avg measure by department C],
[Measures].[avg measure by all Organisations]
} 
ON COLUMNS
   
FROM ( SELECT ( { [DimDate].[DateYear].&[2009] } ) ON COLUMNS 
    FROM [Gauge]) 
WHERE ( [DimDate].[DateYear].&[2009] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

And i've dragged some gauges on the dashboard.
.

The radial gauge (1) is the one i initially preferred but while reading more about the usability of this diagram the less i prefer (see below for the usability tips). The second is a linear gauge and is like a thermometer. The third is also a linear gauge but with ranges. This would imply that a indicator is judged somehow in right or wrong. The fourth lineair gauge is a gauge with precentages. I didn't manage to change the percentages into absolute numbers. And the last gauge (5) is the same as 2 but horizontal positioned.

Usability tips
From studying (old study) books, internet, etc i can give you some handy tips and best practices when you're developing gauges and dashboards:

The first tip is don't use radial gauges (1) ;-) in your dashboard because they use a lot of space and the added value is low. If do want to use gauge in your dashboard, use the lineair gauge (2, 4 or 5). This diagram uses less space on your dashboard and gives you the same information.
When you do use the radial gauge don't punt the numbers on the inside of the scale. Put them on the outside of the scale. This way the pointer won't overlap the numbers. This will enhance faster readability of the gauge.
If you're using the radial gauge put the markers on the inside.There are three options available and this the least worst option. The other options are outside and cross. The outside option could cover the numbers with the marker and cross will do the same but centers between the inside and the outside option. So i would recommend using the inside option.
In the example i used multiple indicators for an average of all departments and an average of all other organisations. I used different color for these indicators. But one thing i did not manage was adding a label (avg departments or avg organisations) to the indicators. For beter readability i would have liked that. Again this would have enhanced readability. Perhaps i'll find out in the future.
The use of colors is also an important choice. In number 2 and 5 i've used red as a color and red indicates as there is a danger, problem, etc. In this example is the color red not a very good choice.

Conclusion
There is a lot possible with these gauges and you can play for hours with these things. I think that dashboard design is not about putting all kind of gauges, diagrams, graphs on a window but you have to think about how to present the information on the screen for the best usability possible. That's the challenge when you're developing a dashboard.

Greetz,
Hennie

Denali : Installing the SSAS Tabular Model project AdventureWorks

2011-07-26T19:59:00.001+02:00

Introduction
I wanted to take a look at the new tabular model (BISM) and i've already read some things about BISM and the multidimensional mode on the blog of Chris Webb. These two are not working together. The BISM and multidimensional model are two different things. It seems that you need to install the "tabular mode" on a seperate instance. During the new installation of Denali CTP3 (post) i pressed next, next, etc and i didn't noticed a choice for this tabular mode (i've to read my own post in detail to make sure ;-)).

Installing a new instance in Denali
The first thing we have to do is installing a new instance on SQL Server. So start up the setup.exe again an let's go through the installation process again. I'm not showing every window again. You can read more about installing Denali in a former post.

Click on installation.

Press Next, Next until you arrive in this window.

First i thought that i had to enter information about the instance here. But creating a new instance is done later in the installation process.

Press a couple of times Next, Next, Next, etc until you arrive in this window.

etc, etc...

Press Next, Next, etc until you arrive in the next window:

blabla Next, etc.

Done......

Installing the Tabular model project
Okay now it's time to install the BISM project. If you download the examples from Microsoft and extract the files you will see the following files.

The following files are available in the folder "Adventure Works DW Tabular Denali CTP3".

Again, the following files are available in the folder "Adventure Works DW Tabular Denali CTP3".

The first file is the BIM file and the other one is the BIMproject file. The bin folder is empty. Okay lets double click on the projectfile and ....

The message appears : "You cannot open the BIM file. Reason: The workspace database server 'localhost' is not running in Vertipaq mode.". I immediately thought that this has something to do with the instance i've created. On the localhost i've installed the Multidimensional mode and on the BISM instance i've installed the tabular mode. I opened the properties of the project and noticed that the propery Workspace Server was "localhost". I changed this to localhost\bism.

After i changed the property, a verification process is started right away. It's telling me that the connection to server is succeeded. Great.

And yes it seems that we can run the project.

Okay now let's see how it looks in SQL Server Management Studio. I would have expected a choice for connecting to the BISM services or whatever.

So how do i see the deployed projects? Let's try this

Does that work? Yep it seems we have succeeded.

I've to make a remark. As you can see a project is deployed, here. Before i tried to connect to the bism server (?) i played a bit with Adventureworks project. I tried to deploy the project and i got an error:

I assumed that the deployment didn't succeed but looking in SSMS show a deployed project(?!). So is this a bug? Interesting. So i deleted the project in SSMS.

And the project was deleted from BISM server. I tried to deploy the project again, the error occurred again and..... the project is gone/not there! So i assume that the project was already installed during the installing process.

Now, i've to found why the adventureworks project don't install on the server anymore, but that is one for a future post.

Conclusion
One conclusion you can draw from this post is that MDX en BISM don't mix (duh). These two cannot installed on the same instance. So when you're developing mixed projects in MDX and BISM you need to install a seperate instance.

Next time we will go deeper into working with this BISM and the adventureworks example.

Greetz
Hennie

Denali : 10 situations with the EOMONTH() function.

2011-07-22T21:01:00.004+02:00

Hi,

In Denali is a new function is available: EOMONTH() and it returns the last day of the month of specified date, with an optional offset. More information can be found on technet. Return type is the type of start_date or datetime2(7). EOMONTH() is the same name as used in Excel.

So i tried some things with this function. Below you can see the results:

Situation 1

SELECT EOMONTH(getdate())

Returning:

Always nice to try

SELECT EOMONTH('2012-02-12')

Returning:
Situation 2

Yup that works too!

Situation 3
Now lets try somethiing with the offset.

SELECT EOMONTH('2013-02-12', -10000)

Returning:

Thats a lot of years back..

Situation 4
Now let's try to crash the EOMONTH() function.

SELECT EOMONTH('2013-02-12', -100000)

Returning:

Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime2' column caused an overflow.

Situation 5
Let't try some implicit conversion of the function

SELECT EOMONTH('20110721')

Returning

Situation 6
Let's try an impossible implicit conversion

SELECT EOMONTH('20110750')

Returning

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Situation 7
Let's try a another impossible implicit conversion
SELECT EOMONTH(20110750)

Returning

Msg 8116, Level 16, State 1, Line 1
Argument data type int is invalid for argument 1 of eomonth function.

Situation 8
BOMonth()?

SELECT BOMONTH = EOMONTH(getDate(), -1) + 1

Returning

Situation 9

SELECT * 
FROM dbo.FactResellerSales RS
INNER JOIN DimDate D ON RS.OrderDateKey = D.DateKey
WHERE D.FullDateAlternateKey Between CAST('20040501' AS DATETIME) AND EOMONTH('20040501')

Returning

Situation 10
Hmm the last one... Can't think of a new situation anymore. I'm out of inspiration! I suggest that you think about the tenth situation and let me know what you've came up with....

Conclusion
EOMONTH() is an interesting function but as other bloggers suggests, why is the only new Date function that is released. There are so many datefunctions to think about...

Other interesting functions to discover are:

IIF
CHOOSE
CONCAT
DATEFROMPARTS
TIMEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS
TRY_CONVERT
FORMAT

This is not a complete list.
Very interesting link is on beyond relational : http://beyondrelational.com/whatisnew/sqlserver/denali/ where you can find all the features released in Denali.

Greetz,
Hennie

Installing SQL Server Denali CTP3 Walkthrough

2011-07-20T08:43:00.000+02:00

Introduction
In case you missed it: SQL Server Denali CTP3 is out! At last a new CTP version of Denali and this time (relative) more features available than in CTP1. CTP2 was not widely distributed, but only for a happy few. This post is about installing the x86 version on my virtual machine. Created a new clean VM environment ready for installing Denali CTP3. Below i've gathered the most interesting links about CTP3 (so far for this moment):

Downloading the necessary software of Denali CTP3
First downoad the following software:

Denali CTP3.
Sample databases (future post).
Feature pack.

Installing Denali CTP3
So the first thing to do is downloading the CTP3 version. After installing the download manager you can download the CTP3 version:

The first thing i did is double clicking on the application file : SQLFULL_x86_ENU_INSTALL and the next thing that happens is the "Unloading the Box" window that opens.

There seems some unzipping taking place here in a folder SQLFULL_x86_ENU and i waited a couple of minutes before it's completed. the following files are available in the SQLFULL_x86_ENU folder:

Double clicking the setup.exe did the following window showing up:

And then we're in the installation center:

And we leave the first tab as it is and go to the second option : "installation"

and choose "New SQL Server stand-alone installation or add features to an existing installation" and it seems that i've passed some potential problems:

Press ok and the following window appears. There is only an evaluation version , yet ;-)

Press next.

Press Next.

Press install. I'm ignoring the windows firewall problem.

Press Next. Just a simple installation is needed.

Press Next. Now we can select the features that are available and i choose the full option model. Not sure whether we run into problems but we'll see.

Press Next..... and....an error has occurred: Powershell 2.0 not installed. Hmmm....

Strange error: "Windows Powershell 2.0 already installed" ? Below the error message in more detail. "Do not select the feature that require PowerShell 2.0"? How should i know which feature is using powershell 2.0?

I've downloaded powershell 2.0 from MSDN, installed and rerun the installation process again. Now, it seems that i've passed the powershell 2.0 prerequisites. I've noticed that before the installation rule window a message already appeared about installing powershell 2.0 (right bottom - two windows back).

Press Next. It seems there is still enough space available on my C drive.

Press Next.

Press Next. Adding current user. Not a very best practice but i'm just want to run Denali as quick as possible.

Press Next.

Press Next and in the installation begins

Seems that everything is succeeded

And finally execute a reboot and start SQL Server Management Studio via the Menu Start. You can see the following entries inthis menu:

I'm not sure whether i've installed all these menu entries during the installation of Denali CTP3. I did have a clean windows install (Windows Server 2008) but the yellow entries are new and the white entries should be older. Not quite clear why some are white and some are yellow.

When i start up the SQL Server Management Studio the following window appears:

And looking in the Object explorers of SSMS, SSIS (Control and dataflow) and SSRS shows me:

Well this is it for now.

Conclusion
The basic installation went smooth and i had one small problem with Powershell 2.0 that was a prerequisite (and wasn't reported during the analysisin the setup support rules window). I'm particularly interested in DQS and in SSIS deployment model. I hope to find out more in future about these parts.

Greetz,
Hennie

SSAS/SSRS : Building a graph for analyzing a subset related to the whole set

2011-07-17T14:49:00.004+02:00

Introduction
My client has some wishes about a graph that shows a subset of facts of certain locations against the whole population of locations. They want to highlight the locations and show their position in a sorted diagram. Below you can see the end result of the graph.

Scenario
For this purpose i've quickly created a small cube. First i build a script for building the following tables:

DimDate
DimLocation
DimDepartment
BridgeLocationDepartment

And they are related as follows:

This situation is already explained in more detail in my former posts:

Modeling a N:M relation between dimensions (part I).
Building a multivalue dimension construction in SSAS ( part II).
Joining issue with MDXing of M2M dimension (part III).
Cascading parameters and M2M dimensions (part IV)

I've created the following example script:

USE [GraphProblem]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactTest]') AND type in (N'U'))
DROP TABLE [dbo].[FactTest]
GO

CREATE TABLE [dbo].[FactTest](
[FactTestID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Location_Key] [int] NOT NULL,
[FactTestCount] [int] NOT NULL
) ON [PRIMARY]
GO

----
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimLocation]') AND type in (N'U'))
DROP TABLE [dbo].[DimLocation]
GO

CREATE TABLE [dbo].[DimLocation](
[Location_Dim_key] [int] NOT NULL,
[Location_Code] [int] NOT NULL,
[Location_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

----
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDepartment]') AND type in (N'U'))
DROP TABLE [dbo].[DimDepartment]
GO

CREATE TABLE [dbo].[DimDepartment](
[Department_Dim_key] [int] NOT NULL,
[Department_Code] [int] NOT NULL,
[Department_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

---
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BridgeLocationDepartment]') AND type in (N'U'))
DROP TABLE [dbo].[BridgeLocationDepartment]
GO

CREATE TABLE [dbo].[BridgeLocationDepartment](
[BridgeLocationDepartment_ID] [int] IDENTITY(1,1) NOT NULL,
[FK_Location_Key] [int] NOT NULL,
[FK_Department_Key] [int] NOT NULL,
[Valid_From] [date] NOT NULL,
[Valid_Until] [date] NOT NULL
) ON [PRIMARY]
GO

---
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]
GO

CREATE TABLE [dbo].[DimDate](
[Date_dim_key] [int] NULL,
[Date_Year] [int] NULL
) ON [PRIMARY]
GO

---

INSERT INTO dbo.DimDepartment (Department_Dim_key, Department_Code, Department_Description) 
VALUES 
(1, 1, 'Department A'),
(2, 2, 'Department B'), 
(3, 3, 'Department C'), 
(4, 4, 'Department D'), 
(5, 5, 'Department E'),
(6, 6, 'Department F'), 
(7, 7, 'Department G'), 
(8, 8, 'Department H'), 
(9, 9, 'Department I'), 
(10, 10, 'Department J') 

INSERT INTO dbo.DimLocation (Location_Dim_key, Location_Code, Location_Description) 
VALUES 
(1, 1, 'Location 1'),
(2, 2, 'Location 2'), 
(3, 3, 'Location 3'), 
(4, 4, 'Location 4'), 
(5, 5, 'Location 5'), 
(6, 6, 'Location 6'), 
(7, 7, 'Location 7'),
(8, 8, 'Location 8'),
(9, 9, 'Location 9'),
(10, 10, 'Location 10')

INSERT INTO dbo.BridgeLocationDepartment (FK_Location_Key, FK_Department_Key, Valid_From, Valid_Until)
VALUES 
(1, 1, '2011-01-01', '9999-12-31'), 
(2, 1, '2005-01-01', '9999-12-31'), 
(2, 2, '2005-01-01', '9999-12-31'),
(3, 3, '2005-01-01', '9999-12-31'),
(4, 3, '2005-01-01', '9999-12-31'),
(4, 4, '2005-01-01', '9999-12-31'),
(5, 5, '2005-01-01', '9999-12-31'),
(5, 7, '2005-01-01', '9999-12-31'),
(6, 6, '2005-01-01', '9999-12-31'),
(6, 7, '2005-01-01', '9999-12-31'),
(7, 7, '2005-01-01', '9999-12-31'),
(8, 8, '2005-01-01', '9999-12-31'),
(9, 9, '2005-01-01', '9999-12-31'),
(9, 10, '2005-01-01', '9999-12-31'),
(10, 10, '2005-01-01', '9999-12-31' )

INSERT INTO dbo.DimDate
VALUES (1,2000),
(2, 2001),
(3, 2002),
(4, 2003),
(5, 2004),
(6, 2005),
(7, 2006),
(8, 2007),
(9, 2008),
(10, 2009),
(11, 2010),
(12, 2011),
(13, 2012),
(14, 2013),
(15, 2014),
(16, 2015)


INSERT INTO [dbo].[FactTest]([FK_Date_Key],[FK_Location_Key],[FactTestCount])
VALUES
(7, 1, 1),(7, 2, 1),(8, 3, 1),(9, 3, 1),(9, 2, 1),(10, 4, 1),(10, 5, 1),(10, 5, 1),(11, 5, 1),(7, 7, 1),
(9, 4, 1),(10, 3, 1),(11, 10, 1),(12, 6, 1),(10, 2, 1),(9, 9, 1),(8, 7, 1),(5, 7, 1),(6, 1, 1),(7, 5, 1),
(8, 1, 1),(7, 1, 1),(7, 2, 1),(8, 3, 1),(9, 2, 1),(10, 4, 1),(10, 5, 1),(10, 5, 1),(7, 7, 1),(9, 4, 1),
(10, 3, 1),(11, 10, 1),(10, 2, 1),(9, 9, 1),(8, 7, 1),(5, 7, 1),(6, 1, 1),(7, 5, 1),(8, 1, 1),(10, 5, 1),
(7, 7, 1),(9, 4, 1),(10, 3, 1),(11, 10, 1),(10, 2, 1),(9, 9, 1),(8, 7, 1),(5, 7, 1),(6, 1, 1),(7, 5, 1),
(8, 1, 1),(10, 5, 1),(10, 5, 1),(11, 5, 1),(7, 7, 1),(9, 4, 1),(10, 3, 1),(11, 10, 1),(12, 6, 1),(10, 2, 1),
(9, 9, 1),(5, 7, 1),(6, 1, 1),(7, 5, 1),(8, 1, 1),(10, 5, 1),(10, 5, 1),(11, 5, 1),(8, 1, 1),(7, 1, 1),
(7, 2, 1),(8, 3, 1),(9, 2, 1),(10, 4, 1),(10, 5, 1),(10, 5, 1),(10, 1, 1),(10, 2, 1),(10, 3, 1),(10, 7, 1),
(10, 5, 1),(10, 9, 1),(10, 2, 1),(10, 1, 1),(10, 4, 1),(10, 4, 1),(10, 5, 1),(10, 9, 1),(10, 2, 1)

Created a cube on the datastructure with following structure:

The dimension usage tab:

And now take a look at the dimension browser tab:

So the values for 2009 seems an interesting candidate for simulating the situation. There are certain departments and locations which can be used for testing my graphproblem. I would like to focus on Department C (location 3 and 4) and department G (Location 5 and 7).

The solution
Now, let's build the report with a graph and a parameter "department". So when the department is selected i want to show the locations in highlighted manner. You can see this in the diagram in the introduction section. Initially, it would look something like this (the customer don't want the names of the locations shown):

But the locations of department C is not red (the two 5's). It's not possible to do a lookup between two datasets in SQL Server 2008 (SQL Server 2008 R2 does have a lookup function). Therefore i decided to alter my MDX query and add an extra column indicating whether a location belongs to a selected department at the parameter. So i changed the standard MDX query to this:

WITH MEMBER [Measures].[test]
AS
IIF(ISEMPTY((EXISTS([DimLocation].[LocationDescription].CURRENTMEMBER, {STRTOSET(@DimDepartmentDepartmentDescription, CONSTRAINED)}, "FactTest").item(0), [Measures].[FactTestCount])), 0, 1)

SELECT {[Measures].[FactTestCount], [Measures].[test]} ON COLUMNS,
[DimLocation].[LocationDescription].[LocationDescription] ON ROWS
FROM (SELECT ( { [DimDate].[DateYear].&[2009] }) ON COLUMNS
FROM [GraphProblem])

Resulting in (when testing with department C):

The most important section is the WITH section where IIF and EXISTS do some nifty work. The EXISTS function checks whether the set of locations are related to the set of departments (selected with the parameter). The EXISTS returns a NULL when there is not a relation and a measure value when there is a relation. So with the IIF and the ISEMPTY it's possible to return a 1 (for a relation) and 0 (for no relation)

So now take a look in the report and select the series properties in the diagram and select the Fill tab:

The next thing is checking whether the test measure is 0 or 1 with : "=IIF(Fields!test.Value = 1, "Red", "Blue")". This looks great when i run the report. The locations belonging to department C is highlighted:

When i choose department G the following diagram is shown:

Conclusion
Initially, i wanted to solve this problem in the report but when i found out that SQL Server 2008 doesn't have a lookup function i needed to try other things. There are some solutions available on the internet about building this with subreports or custom coding but the solution above is a better solution (in my opinion).

Greetz,
Hennie

SSAS/SSRS/MDX : Cascading parameters and M2M dimensions

2011-07-09T11:19:00.004+02:00

Introduction

Well, today a post about cascading parameters in Reporting Services (SSRS) in combination with MDX coding. Cascading parameters is about defining multiple parameters in such a way that a list of values of a parameter depends on the values of another parameter. For this post i'll build upon my lab environment as described in my earlier posts:

Modeling a N:M relation between dimensions (part I).
Building a multivalue dimension construction in SSAS ( part II).
Joining issue with MDXing of M2M dimension (part III).

As a matter of fact, this post could be seen as a part IV in this list because the problem is also related with the M2M dimension construction. This is explained in my former post (part III).

Scenario
What i'll describe in this post is building a cascading parameter build upon two datasets: one for department and one for location. I've seen enough examples of building parameters on SQL queries but there are not that many examples based on MDX. As decribed in the earlier posts (see above) i've the following situation:

Department A

Location 1.
Location 2.

Department B.

Location 2.

Department C.

Location 3.
Location 4.

So, the cascading parameter should show location 1 and 2 when the first parameter is department A. Again, when department C is chosen the other parameter should be limited to location 3 and 4.

Building the report
First, let's setup the report in Reporting Services that returns a department, a location and a count field. I've created a datasource in SSRS, built a dataset and added the two parameters: Department and Location.

After I pressed OK and returned back to the report i noticed that there are a couple of extra (hidden) datasets created(DepartmentDepartmentDescription and LocationLocationDescription). Dataset1 is MDX query that should return data for the report.

Let's try the report and check if the cascading parameter location is depending on the department parameter. Look at the diagram below and notice that all four locations are shown, even when i choosed a different departments.

Seems that we've to add some more magic to the report. Let's look at the parameter datasets in more detail. I've copied the MDX query of the department dataset below:

WITH 
MEMBER [Measures].[ParameterCaption] AS [Department].[Department Description].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Department].[Department Description].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Department].[Department Description].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {[Measures].[ParameterCaption], 
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]} ON COLUMNS , 
[Department].[Department Description].ALLMEMBERS ON ROWS FROM [MultiValueDimension]

No need for changes here. Let's take a look at the location dataset.

WITH
MEMBER [Measures].[ParameterCaption] AS [Location].[Location Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Location].[Location Description].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Location].[Location Description].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS ,
[Location].[Location Description].ALLMEMBERS ON ROWS
FROM ( SELECT ( STRTOSET(@DepartmentDepartmentDescription, CONSTRAINED) ) ON COLUMNS
FROM [MultiValueDimension])

The problem (as i described in my former post about the showing the right location related at the right department) is that all locations are shown because the relation between location and department isn't enforced. So i need to change the query and two important changes are needed.

First change the WITH part:

WITH
MEMBER [Measures].[ParameterCaption] AS [Location].[Location Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Location].[Location Description].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Location].[Location Description].CURRENTMEMBER.LEVEL.ORDINAL

into:

WITH 
MEMBER [Measures].[ParameterCaption] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.MEMBER_CAPTION) 
MEMBER [Measures].[ParameterValue] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.UNIQUENAME) 
MEMBER [Measures].[ParameterLevel] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.LEVEL.ORDINAL)

This part is enforcing the member to NULL when the relation is not there:

and change:

[Location].[Location Description].ALLMEMBERS ON ROWS

into:

NONEMPTY([Location].[Location Description].ALLMEMBERS,[Measures].[Bridge Location Department Count])

This part is needed to suppress the NULLs but fon't forget to show all locations( for a specific department), even when there is no fact.This is important to notice because when i don't add this extra constraint location 4 isn't shown at Department C. This location doesn't have a factrecord stored.

And the final solution is shown below:


WITH 
MEMBER [Measures].[ParameterCaption] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.MEMBER_CAPTION) 
MEMBER [Measures].[ParameterValue] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.UNIQUENAME) 
MEMBER [Measures].[ParameterLevel] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.LEVEL.ORDINAL)
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]} ON COLUMNS , 
NONEMPTY([Location].[Location Description].ALLMEMBERS,[Measures].[Bridge Location Department Count]) ON ROWS
FROM (SELECT (STRTOSET(@DepartmentDepartmentDescription, CONSTRAINED) ON COLUMNS 
              FROM [MultiValueDimension]))

The snippet 'FROM (SELECT (STRTOSET(@DepartmentDepartmentDescription, CONSTRAINED) ON COLUMNS FROM [MultiValueDimension]))' will change the location in a cascading parameter.

Now it's time to test this changed query. Starting up the report in preview mode is shown below (department A is showing the two locations 1 and 2):

Let's try another department (department C shows location 3 and location 4):

Conclusion
Main 'problem' is the usage of the M2M dimension (Location 2 Department) that is actually a n:m relation. To enforce the relation you have to use the second parameter of the NONEMPTY() function.

Greetz,
Hennie

SSAS: Joining issue with MDXing of M2M dimension (part III)

2011-07-01T15:19:00.006+02:00

Hi,

At this moment i'm developing a M2M (or MM) dimension for locations and departments. If you haven't seen my former posts (part I, part II) about this subject, i would recommend reading these posts. One issue i have with the M2M dimensions is getting the right information in the reports (who doesn't;-)) and it took me a while to understand how to handle this. The problem is how to show the locations which don't have a fact for a specific department.

Okay, this is the situation: there are departments and locations and they are n:m related to each other. Therefore i've developed a bridge table for this purpose. In this bridge table i've added a count field. In contrast with my former post i've added an extra record for simulating the scenario i have. So the records in the different tables looks like this:

Query :


SELECT
Location_Description,
Department_Description,
DT.Date_Year,
TestFactCount
FROM dbo.Location L
LEFT OUTER JOIN dbo.TestFact F ON L.Location_Dim_key = F.FK_Location_Key
LEFT OUTER JOIN dbo.DimDate DT ON F.FK_Date_Key = DT.Date_dim_key
INNER JOIN dbo.Bridge_Location_Department B ON B.FK_Location_Key = L.Location_Dim_key
INNER JOIN dbo.Department D ON D.Department_Dim_key = B.FK_Department_Key

Resulting in:

So when the user selects department C two locations should be shown: location 3 and 4. Location 3 has factvalues and location 4 does not have any fact values.

Option 1

Okay the first attempt for building a MDX query for this is:

SELECT [Measures].[Test Fact Count] ON 0,
[Location].[Location Description].[Location Description].MEMBERS ON 1
FROM ( SELECT (STRTOSET('[Department].[Department Description].&[ Department C]', CONSTRAINED) ) ON COLUMNS 
       FROM [MultiValueDimension])

As you can see all locations are shown (even the one that has no relation with department C) . Not a desired situation

Option 2:
The next thing i tried is the following query:

SELECT [Measures].[Test Fact Count] ON 0,NONEMPTY([Location].[Location Description].[Location Description].MEMBERS) ON 1FROM
( SELECT (STRTOSET('[Department].[Department Description].&[ Department C]', CONSTRAINED) ) ON COLUMNS FROM [MultiValueDimension])

Resulting in :

Also not a desirable situation here...

Option 3
Someone on the MSDN forum pointed me to a extra parameter of the nonempty() function. The nonempty() function has the following syntax: NONEMPTY(set_expression1 [,set_expression2]) and set_expression2 is a sort of limitation of the first set_expression. So the function returns a set of tuples that are not empty, based on the cross product of the second set.

So the final solution for getting the right information is this:

SELECT [Measures].[Test Fact Count] ON 0,NONEMPTY([Location].[Location Description].[Location Description].MEMBERS, [Measures].[Bridge Location Department Count]) ON 1FROM
( SELECT (STRTOSET('[Department].[Department Description].&[ Department C]', CONSTRAINED) ) ON COLUMNS FROM [MultiValueDimension])

So i'm using the bridge count of the bridge table for getting the right information. When this value is nonempty it does show me the locations and facts that are related. With this MDX query i can produce the following information:

Conclusion
With this solution you can simulate joining in SQL for getting the right information.

Greetz,
Hennie

SSAS/Kimball : building a multivalue dimension construction in SSAS (part II)

2011-06-23T09:14:00.002+02:00

In my former post i'll already explained the background for this post. If you didn't read this than i would advice reading this first post before reading this article. In this article i'll explain about building a multivalue dimension in SSAS (MM dimension). This post covers the following subjects:

Prerequisites.
Building a cube.

Prerequisites
Below you can see a script i've been using for building a cube. At the end of the script i've added a couple of facts/events. So when we will quering this cube the following results should be shown for departments:

Department A : 3
Department B : 2
Department C : 2

And for the locations:

Location 1 : 1
Location 2 : 2
Location 3 : 2

First create a database and execute the following script:


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestFact]') AND type in (N'U'))
DROP TABLE [dbo].[TestFact]
GO

CREATE TABLE [dbo].[TestFact](
    [TestFactID] [int] IDENTITY(1,1) NOT NULL,
    [FK_Date_Key] [int] NOT NULL,
    [FK_Location_Key] [int] NOT NULL,
    [TestFactCount] [int] NOT NULL
) ON [PRIMARY]
GO

----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]') AND type in (N'U'))
DROP TABLE [dbo].[Location]
GO

CREATE TABLE [dbo].[Location](
    [Location_Dim_key] [int] NOT NULL,
    [Location_Code] [int] NOT NULL,
    [Location_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
DROP TABLE [dbo].[Department]
GO

CREATE TABLE [dbo].[Department](
    [Department_Dim_key] [int] NOT NULL,
    [Department_Code] [int] NOT NULL,
    [Department_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

---
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Bridge_Location_Department]') AND type in (N'U'))
DROP TABLE [dbo].[Bridge_Location_Department]
GO

CREATE TABLE [dbo].[Bridge_Location_Department](

[Bridge_Location_Department_ID] [int] IDENTITY(1,1) NOT NULL,

    [FK_Location_Key] [int] NOT NULL,
    [FK_Department_Key] [int] NOT NULL,
    [Valid_From] [date] NOT NULL,
    [Valid_Until] [date] NOT NULL
) ON [PRIMARY]
GO

---
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]
GO

CREATE TABLE [dbo].[DimDate](
[Date_dim_key] [int] NULL,
[Date_Year] [int] NULL
) ON [PRIMARY]
GO

---

INSERT INTO dbo.Department (Department_Dim_key, Department_Code, Department_Description) 
VALUES (1, 1, ' Department A' ),(2, 2, ' Department B' ), (3, 3, ' Department C')

INSERT INTO dbo.Location (Location_Dim_key, Location_Code, Location_Description) 
VALUES (1, 1, ' Location 1' ),(2, 2, ' Location 2' ), (3, 3, ' Location 3')

INSERT INTO dbo.Bridge_Location_Department (FK_Location_Key, FK_Department_Key, Valid_From, Valid_Until)
VALUES (1, 1, '2011-01-01', '9999-12-31' ), 
(2, 1, '2005-01-01', '9999-12-31' ), 
(2, 2, '2005-01-01', '9999-12-31' ),
(3, 3, '2005-01-01', '9999-12-31' )

INSERT INTO dbo.DimDate
VALUES (1,2000),
(2, 2001),
(3, 2002),
(4, 2003),
(5, 2004),
(6, 2005),
(7, 2006),
(8, 2007),
(9, 2008),
(10, 2009),
(11, 2010),
(12, 2011),
(13, 2012),
(14, 2013),
(15, 2014),
(16, 2015)


INSERT INTO [MultiValueDimension].[dbo].[TestFact]
           ([FK_Date_Key]
           ,[FK_Location_Key]
           ,[TestFactCount])
VALUES
           (7, 1, 1),
           (7, 2, 1),
           (8, 3, 1),
           (9, 3, 1),
           (9, 2, 1)

Building a cube
Okay, now we have to build a cube based on the tables created in SQL Server. Kimball calls this a multivalue dimension (actually it's a variant on the multivaluedimension, in my opinion). Microsoft calls this a many-to-many dimension (MM). Marc Russo published a great whitepaper on this subject. Many-to-many dimension (MM) is a better name for this subject. What can we say about Many-to Many dimension construction in relation to the case we are building:

There are two measure groups needed.
There are two dimnsions needed.
The bridge Location Department table will be Intermediate Measure Group. The intermediate Measure Group is the measure group that relates the MM dimension to the regular dimension In this case it relates the location to the department dimension.
Location is the intermediate dimension.

Designing this in the cube designer shows this picture:

So how does this looks in the dimension usage tab of the cube designer?

As said earlier in the post: location and department are the dimensions. Testfact and the bridge location department table are the measure groups. So when we check the results in the browser we see the following results for the locations:

And for the departments:

The total is still 5 but adding the departments subtotals manually is equal to 7!!!

And this is as predicted at the beginning of this post. So we have reached nirvana. Well not quite yet. What about history? What if a location do not exist anymore between departments and locations? What if locations shift from locations to other departments? You want to count your facts at right departments in a correct timeline! I'll blog about this in the future.

So, this is it for now.

Greetz,
Hennie

SSRS : Setting up a A4 page in Reporting Services

2011-06-17T10:27:00.000+02:00

Hi,

I'm always a bit rusty when i start building reports in Reporting Services after a while. One thing i always has to figure out is how to set up the page size, body and margins. In a article on Microsoft you can find more information about this subjct. You can set the properties in the properties window or you can set the properties with report properties window (Menu -> Report -> Report properties). The relation between page, body, header and footer looks conceptual like the diagam below.

Below you can see how i manually set the page size of the report with A4 paper settings.

And don't forget to set the body properties:

The formula of setting the PageSize Width, bodysize and margins is this: Page Width = Body Width+ Left margin + Right Margin. In my case 21= 19+1+1.

In case of the height of the report you need to set the pagesize. In case of A4 : 29,7cm. In case of the body height you can set whatever you wan't. By setting the PageSize Height property the height of A4 is maintained and when the body height is larger than the pageSize (minus top margin and bottom margin) more pages are displayed/printed.

It's also possible to set the properties with the report properties window. Below you can see an example in inches.

We end up with the follow values for the proprties:

Greetz,

Hennie

SSAS/KIMBALL: modeling a N:M relation between dimensions (part I)

2011-06-14T16:57:00.001+02:00

Hi,

During the development of a dashboard for a customer i've a awkward problem. The situation is like this: There are events in the every daily business (fact) and these events can happen on certain locations (physical) locations. These locations can be shared by different (logical) departments. So conceptually you have something like this:

And if you model this in a Bachman diagram (3NF) it will look like this:

So there are two standard entities and one associative entity has been added for handling the n:m relation. This way n:m relations can be easily modeled in 3NF. So the values for the department table will be :

Department A
Department B
Department C

The values for the location table are:

Location 1
Location 2
Location 3

And the associative table "Department_Location" contains :

1 (Department A), 1 (Location 1)
1 (Department A), 2 (Location 2)
2 (Department B), 2 (Location 2)
3 (Department C), 3 (Location 3)

And you could introduce a valid_from and a valid_to (as in temporal modeling) where you can check the validity whenever the record is valid or not. Relations exists and can no longer exist at some point in time.

A first assumption of me was modeling the location and the department seperately (the grain of the fact is at location level):

So the grain is at Location level and therefore a 'simple' dimension is enough. But the fact can happen at different departments (e.g. in case of location 1) and therefore i introduced a bridge table. So the key of the fact table is pushed to the bridge department table and the department dimension key is also pushed to the bidge department table. But wait a minute.....there is a drawback of this modeling solution. You can't see whether which location belongs to which department, unless a fact has happen. If a fact didn't happen you can't determine whether a location belongs to a department. So i needed a better solution than this.

The solution
So studying kimball's book : "the datawarehouse lifecycle toolkit, second edition" shows me a example on page 270, paragraph "many-values dimensions with bridge tables". In this example a account dimension and Customer dimension is used with a bridge table. A weighting factor can help adding facts to the right customer. But i'm my situation i don't know how the facts are related to the department. Sometimes 99% of the facts belongs to the departments and sometimes less facts and this is not sure. So in my case a proper modeling solution is this :

In this case it's possible to determine which location belongs to which department.

Kimball recommends adding a valid_from and a valid_to column to the bridge table (as i already explained in the 3NF example) because a relation can exist at a certain point in time and they can no longer exist at some moment.

Next time i'll explain how to build this in SSAS.

Greetz,
Hennie

SSAS: NON EMPTY and INNER/LEFT OUTER JOINing

2011-06-12T11:30:00.000+02:00

Today a post about joining in SQL, the NON EMPTY operator and the nonempty() function in MDX. The origin of this blog started when a customer asked why some months on a graph didn't show up. So i started looking at the NON EMPTY operator and the NonEmpty() function.

Prerequisites
At first i tried to create a representative query on AdventureWorks cube. When i finally succeeded i tried to buid a query in SQL and at first glance building the simple MDX query to SQL seems easy but i gave up on this and i decided to built my own lab environment. This confirmed my believe that MDX is a very powerful query language. Hard to understand but when you have mastered it you can create powerful reports. Here is the SQL script is used for this small experiment.

1. Execute this script in SSMS:


USE [NonEmpty]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]
GO

CREATE TABLE [dbo].[DimDate](
[Date_dim_key] [int] NULL,
[Date_Year] [int] NULL
) ON [PRIMARY]
GO

--TRUNCATE TABLE dbo.DimDate 
--GO

INSERT INTO dbo.DimDate 
VALUES (1,2000),
(2, 2001),
(3, 2002),
(4, 2003),
(5, 2004),
(6, 2005),
(7, 2006),
(8, 2007),
(9, 2008),
(10, 2009),
(11, 2010),
(12, 2011),
(13, 2012),
(14, 2013),
(15, 2014),
(16, 2015)

------------------------------------------------------------------
USE [NonEmpty]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestFact]') AND type in (N'U'))
DROP TABLE [dbo].[TestFact]
GO

CREATE TABLE [dbo].[TestFact](
[TestFact_ID] [int] identity (1,1) NOT NULL,
[FK_Date_ID] [int] NULL,
[TestFact_Count] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.[TestFact] 
VALUES (1,1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 1),
(7, 1),
(8, 1),
(9, 1),
(10, 1),
--(11, 1),
(12, 1),
(13, 1),
(14, 1),
(15, 1),
(16, 1)

2. Build a simple cube on these tables:

Experimenting with the SQL queries
So when the customer was talking about the not showing some months on the x-axis of the graph i immediately realized it had something to do with LEFT and INNER joining problem (but in MDX).

So here are some SQL joining queries:


SELECT * 
FROM dbo.TestFact F
INNER JOIN dbo.DimDate D ON F.FK_Date_ID = D.Date_dim_key


SELECT * 
FROM dbo.DimDate D
LEFT OUTER JOIN dbo.TestFact F ON F.FK_Date_ID = D.Date_dim_key

BUT i'm building reports on cubes and therefore i can't use SQL queries and therefore i need MDX queries that can handle LEFT joining.

MDX queries
In MDX you have two options (as far is i know) for simulating INNER JOIN/LEFT/RIGHT FULL OUTER JOIN and that is the NON EMPTY keyword and the Nonempty() function. I'll show them both. First the simulation of the INNER JOIN in MDX:

1) INNER JOIN with the NON EMPTY keyword:

SELECT NON EMPTY {[DimDate].[DateYear].[DateYear]} ON ROWS,
NON EMPTY {[Measures].[TestFactCount]} ON COLUMNS
FROM [TestFact];

2) INNER JOIN with the NONEMPTY() function:

SELECT NONEMPTY({[DimDate].[DateYear].[DateYear]}) ON ROWS,
NONEMPTY([Measures].[TestFactCount]} ON COLUMNS
FROM [TestFact];

3) LEFT OUTER JOIN with the NON EMPTY keyword

SELECT [DimDate].[DateYear].[DateYear] ON ROWS,
NON EMPTY{[Measures].[TestFactCount]} ON COLUMNS
FROM [TestFact];

4) LEFT OUTER JOIN with the NONEMPTY() function

SELECT [DimDate].[DateYear].[DateYear] ON ROWS,
NONEMPTY({[Measures].[TestFactCount]}) ON COLUMNS
FROM [TestFact]

Conclusion:
INNER/LEFT/RIGHT OUTER/FULL OUTER joining can be simulated in MDX with the NON EMPTY keyword and/or the NonEmpty Function.

Greetz,
Hennie

SQL Server Query best practices

2011-06-02T10:29:00.001+02:00

This post is one in a series of posts about SQL Server best practices. I've posted best practices about the following subjects, so far:

This post is about "query" best practices:

I always write my SQL statements (SELECT, WHERE, etc) in upper case and fields and tables in according to the way they're created.
Write comments when something is not clear. This will aid other developers understanding your code.
Always write case consistent code. This will enhance transitions from Case Insensitive (CI) server/database to Case Sensitive (CS) server/database.
Don't use columnnumbers in ORDER BY. This will enhance readability.
Use ANSI joining (INNER, LEFT OUTER, RIGHT OUTER) in stead of old school joining (*=, =*,=).
Don't use SELECT * in queries. Always use columnnames in SELECT statements. This will improve Disk I/O and performance. Another advantage is that when you execute a SELECT INTO it is more error prone because when a column is added it doesn't effect the insert.
Don't use a % at start of a LIKE expression. This result in a index scan and the index is not fully used.
Use proper size for variables in queries. This will lower the change of SQL injection and will improve performance
Use WHERE whenever possible. This reduces the dataset.
Don't use the trick of TOP 100 PERCENT and ORDER BY in a view. This trick is not applicable in 2005 and 2008 (and further). e.g. . CREATE VIEW xxx AS SELECT TOP (100) PERCENT ....
(...)
ORDER BY SortCol;
Don't use NOLOCK hint. This is very important because it can cause datainconsistency like dirty reads, phantom reads and lost updates.
Use as many "derived tables" as you can. These are faster. For example:
SELECT FROM
(
SELECT
FROM
) AS A
Limit the usage of scalar functions in SELECT statements. Never use a scalar function in a SELECT when a lot of rows are queried. Scalar functions acts as cursor when a lot of rowd are retrieved. Change the function into an inline view.
Use as less possible HINTS. HINTS prohibits the SQL engine using the automated optimization. At first it could be a performence improvement but when data grows it could be slower
Use as less possible negative operators like <>, NOT IN and NOT EXISTS.

Greetz,
Hennie

Strategic, tactical and operational dashboards

2011-05-30T14:43:00.002+02:00

In my former post i already described the different needs of the different users of dashboards. For these users we can develop operational, tactical and strategic dashboards. Operational dashboards focusses on the operational users, tactical dashboards focusses on the tactical users and strategic dashboards focusses on the strategic users. Logical.

In the book of Wayne Eckerson "Performance dashboards", Wayne speaks about building a management summary on a operational dashboard (for example) and that started me thinking. Isn't building a management summary on a operational dashboard a tactical dashboard (and building a strategic dashboard on a tactical dashboard)? In an ideal world this would be true. Yes, operational, tactical and strategic dashboard should be in line which each other. KPI's needs to be operationalized to lower levels....or lower level indicators needs to be gathered and summarized to higher levels. Otherwise said: you have a top down approach or bottom up approach.

When applying the top down method, you'll be focussed on a balanced scorecard and strategymaps at strategic levels, the tactical dashboards metrics are derived from the KPI's and operational dashboard uses further detailed metrics. Off course it's also possible to use diagnostric metrics at the different levels.

In case of a bottom up approach the relevant operational metrics are summarized to higher aggregated levels for tactical analysis and again tactical metrics are collected into KPI's at strategic levels. In my opinion, with the bottom up approach is difficult to build a aligned organization. The probability that all measures are available for building a higher level dashboard is small. So aligning the dashboards will always have some top down initiatives.

Conclusion
In my opinion there is always a need in the bottom up approach for top down initiatives because at some certain point on a certain level more information is needed than is available. It's much harder building a dashboard with a bottom up method. To get an aligned organization the best way to start is building a strategic dashboard, initially. When this is succesful start implementing tactical and operational dashboards.

Regards,
Hennie

What kind of users will use a dashboard?

2011-05-26T11:35:00.009+02:00

As you may know, i've to build a dashboard for a customer. The dashboard should be build for the board of the directors as well as for workers on the workfloor. Currently reading Wayne Eckerson's book : Performance dashboards and it gives some insights about building dashoards. The first thing i'm interested in is which information is presented to whom. Who are my users and what are their demends and wishes? How do handle information? How can a dashboard aid their work? A director has different needs than a professional worker. So these questions come to my mind regarding usage of dashboards.

I think there are three different users of a dashboard:

Strategic users: These are the board of directors, executives, high level managers, etc. These managers monitor execution of strategic objectives and are frequently involved implementing a balanced scorecard. They will use the dashboard to align the organization with the business objectives. You can define this as a top down approach. The usage is more focussed on management. A list of typical usage is this:

Scorecarding.
Monitoring KPI's.

Tactical users: These are the departmental users like LOB managers, business analysts to compare performance of their areas or projects to budget plans, forecasts or last period's results. For example if a customer wants to monitor defects and the improvements of a machine they want to use a dashboard to display, monitor and analyze progress during the previous 12 months, for instance. The usage is oriented on analysis: Why did this happen? A list of types of usage is this:

Tracing trends in relation to company's goals and initiatives.
Rootcause analysis.
Issues.

Operational users: Operational users are frontline workers and managers who deal directly with cusomers or manages the production line. This information is lightly summarized. Operational users are more focussed on monitoring.

Real time/right time information
Acting very quickly on information.

An interesting graph i found in a internet document from "Juice : A Guide to Creating Dashboards People Love to Use". These suggests different graphs for different usage.

But there's nothing mentioned about a Gauge diagram, for example. So, the idea is great but i would like more information about which diagrams should you use in which situation.

Greetz,
Hennie

SSAS : datatype struggling

2011-05-11T16:14:00.001+02:00

I'm a bit struggling with SSAS and it's about a couple of measures with datatype 'bit' in my SQL Server fact table. At first i thought that i could add them up in SSAS with aggregatefunction 'sum'. Well, that doesn't seem the case here. An error occurred: "bit fields aren't additive". So then i changed the datatype to tinyint and when i processed the cube, the following error occured:

"Errors in the metadata manager. The data type of the 'field' measure must be the same as its source data type. This is because the aggregate function is not set to count or distinct count "

And i changed the datatype to tinyint and then i got really strange values like -93. Stange because there were only 0 and 1 values in this field. Reading a blog of Darren Gosbell tells me that the -93 is probably an overflow of the datatype. Then i changed the datatype to Integer and still i got errors like the one above (in red). Then i noticed more properties : source.

It seems there is another datatype property at a measure and it's defining the source attribute. When i changed that to integer i got the correct values. So there are four places where you can see and/or set datatypes:

SQL Server table.
Datasource view.
Measure.
Measure (Source group properties).

1 and 2 are quite obvious. Option 3 is also an option i do understand. You need to set a datatype in your cube when you roll up values from tinyint datatype to integer or perhaps to bigint. But the fourth option is less self explainable. So what is the role of this property group "source"? Is it a sort of CAST operation of the sourceattribute? Looking in on MSDN states that the source properties are describing the source of the measure. Litterally that doesn't seems to me a true statement because when i change the source attribute to the similar datatype in the dsv i got an error.

Below you can read my steps to glory:

Change the field in the fact table to Tinyint
Change the aggregatefunction to sum.
Change the property datatype to integer
Change the property datatype of the grouped properties 'source' to integer

I've posted a question on the MS SSAS forum about this issue and when i got a satisfied answer i will post it here on my blog.

Thnx,

Hennie

SQL Server Linked Server best practices

2011-05-10T09:18:00.003+02:00

This post is one in a series of posts about SQL Server best practices. I've posted best practices about the following subjects, so far:

This post is about linked servers. One note of appreciation for Arjan Fraaij, who is a major contributor to this post. One of the serverobjects is linkedserver and is used for using data from a another server or storing data to another server. There are also other options available, like SSIS and therefore it's good to make a list of pros and cons of linked server. So when do you use a linked server? The main reason is that you need the information from another server immediately and in other cases i would suggest using SSIS.

Below you can see diagram for a DTAP environment and databases A and B. On Server A a linked server is created to server B. This should be done in every environment : Development, Test, Acceptance and Production. It should not be allowed to create linked servers from a Test environment to a Acceptance environment.

Here are the Linked Server best practices:

Naamconvention according to CamelCase.
De namingconvention of a linked server is this:: ls<DatabaseName>

eg. lsDatabaseB.

A linked server should be related to a database (and not a server)
A linked server name has never a servername incorporated.
A linked server should only be created based on integrated security.
A linked server can only point to a database in the same environment.
If used in your organization , use a C-Name. This will enhance more flexibility in case migration scenarios.

Greetz,

Hennie

SSRS : Pareto analysis

2011-05-06T12:16:00.002+02:00

Hi,

A pareto chart (by Vilfredo Pareto) is a graphical way of highlighting the most important among (typically large) set of factors. This type of chart contains both bars and a line graph. The bars display the values in descending order, and the line graph shows the cumulative totals (cumulative percentage) for each category, left to right. In my current project i''ve to analyze all kinds of events and i'm thinking about using a pareto analysis.

I used a simple query from the SSRS recipes book and i started experimenting with this:

SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Drag a chart on the report and put the sales reasons on the category groups and internet sales amount in the details.

Experimenting with the custom attributes of the columns (chart series, clicking on the histograms) like this (don't forget to set the ShowColumnAs property

Will show a pareto diagram :

Conclusion
At first i thought that the pareto was a separate diagram in the toolbox but it is not. So the most important thing to do is setting the custom properties in the chart series (click on the bars). One thing i have to find out is get a line on 80% for determining what categories are responsible for 80% of the sales.

Greetz,
Hennie

SSRS : Report parameters

2011-05-03T11:12:00.000+02:00

Hi,

Currently building a dashboard for a customer and i wanted to understand the concept of parameters a bit better. Especially in relation with MDX queries. Using parameters isn't complicated, but you need to understand them thoroughly when you're building more complex reports. So, lets understand the scope of this post: Parameters. Where do they all appear?

1. Query designer parameters
When you're designing your MDX query in the query designer it's possible to set whether or not you want a parameter.

The MDX query is something like this:

2. Dataset parameters
The dataset is built on a query and is used by the report.

3. Report parameters
The report parameter is used for prompting to the user. The user can select a value and execute the report.

So i decided to rename the parameter. The first thing i did is renaming the query parameter (@dateCalendarYear into @qryDateCalendarYear) and executing the query and the following error occurs

Parser: The query contains the qryDateCalendarYear parameter, which is not declared. (msmgdsrv)

Then i noticed there is a another window in the query designer where information about a parameter is stored.

So i changed that from DateCalendarYear into qryDateCalendarYear.and now the query is executed. When i take a look in the parameters window in the dataset properties window an empty Parameter value is shown. So i choose the suggested option.

Okay let's see whether the reports run and it does.....Now lets change the report parameter and see whether if the report still runs.

An error occurs.....

So let's go the dataset properties window and that indicates that there is a mismatch between the parameter name and parametervalue.

Correcting this will let the report run.

Conclusion
So renaming the different parameters needs some different adjustments. There are four places where you can change something regarding parameters:

Query designer
Parameter window in the query desgner window
Parameters at the dataset properties window
Report parameters in the main report development window.

It's a bit confusing that the parameter name and parameter value ara actually both parameter names (and not values). One is the report parameter name and the other is the query parameter name.

So that's it for now.

Greetz,

Hennie

SSRS : experimenting with sparklines

2011-05-01T18:49:00.002+02:00

Hi,

Today a post about sparklines in SQL Server Reporting Services. As reading in the book ""SSRS recipes" from Paul Turley and Robert Bruckner, sparklines are invented by Edward Tufte. Sparklines are "small word sized graphics that are embedded in a context of words, numbers and images". I used the following query to experiment with. You can download this from the WROX site.

SELECT
    SUM(t.ExtendedAmountSum) AS ExtendedAmountSum
    , t.EnglishProductCategoryName
    , t.CalendarYear
    , t.MonthNumberOfYear
FROM
(
    SELECT
        SUM(FactInternetSales.ExtendedAmount) AS ExtendedAmountSum
        , DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
    FROM
        FactInternetSales
    INNER JOIN
        DimProduct ON FactInternetSales.ProductKey = DimProduct.ProductKey
    INNER JOIN
        DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
    INNER JOIN
        DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
    INNER JOIN
        DimDate ON FactInternetSales.OrderDateKey = DimDate.DateKey
    GROUP BY
        DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
    UNION ALL
    SELECT
        SUM(FactResellerSales.ExtendedAmount) AS ExtendedAmountSum
        , DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
    FROM
        FactResellerSales
    INNER JOIN
        DimProduct ON FactResellerSales.ProductKey = DimProduct.ProductKey
    INNER JOIN
        DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
    INNER JOIN
        DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
    INNER JOIN
        DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey
    GROUP BY
        DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
) t
GROUP BY
        t.EnglishProductCategoryName
        , t.CalendarYear
        , t.MonthNumberOfYear
ORDER BY
    CalendarYear, EnglishProductCategoryName

1. And i started building the following report. Insert a table into the report body and delete the original row group and associated rows. Drag the calendar year and english productcategory into the row groups window. Drag the field extendedAmountSum on the data fields region.

Running the report will show this:

Now drag a sparklines component from the toolbox on the report body. Setup the fields calendaryears and the Salesamount for the sparklines.

And drag the sparkline into the tabel and run the report and you will get something like this:

If you change the sparkline type to "smooth area" and you get this:

Change the chart type to pie and you get this:

I've some difficulty understanding what's length of the period of a sparkline. With a marker points you get a marker for every row for grouped records. For instance for 2005 and accessoires there are 6 months, 6 records and 6 markerpoints. Playing with background colors and markerpoints and you get this:

So that's it with playing with sparklines.

Greetz,

Hennie

SQL Server stored procedures best practices

2011-04-30T12:10:00.002+02:00

Today a post about SQL Server naming conventions. I've gathered these naming conventions in the last couple of years and i've implemented them at projects at my customers. This is the fourth post in a serie of best practice posts (part I, part II, part III and part IV ). As said, this post is about naming conventions and these naming conventions can be subdivided in server objects-, database objects- and T-SQL naming conventions.

Naamconvention according to Camel Casing.
Never prefix a stored procedure with sp_.
Think about schemas when you're using a lot prefixing in the names of stored stored procedures.
Create stored procedures in the database where they're used.
Stored procedures that are used in OLEDB Sources has the following conventions usp<manipulation>_<name>.
Use 'par' as a prefix for parameter variables (eg @intParTableName). This will help reading stored procedures and it distinguishes SP variables and parameter variables.
Write comment if something is not quite clear. Length of comment doesn't effect performance.
Always write case consistent names in your code. Meaning that when you move your code from Case insensitive database to a case sensitive database it will still work.
Add a debug parameter to a stored procedure. This could be a bit type. When you pass a 1 all SQL statements are printed and nothing is executed. This way you can debug your stored procedure and it's even more helpfull when you're using dynaminc SQL.
If you're using multiple times a function in a stored procedure, store the result once in a variable and use this variable instead.
Make sure that a stored procedure always return a returnvalue. Return values are standardized and are used for returning the status of the execution of a stored procedure. Use the OUTPUT parameter for returning data.
Use SET NOCOUNT ON at the beginning of a stored procedure (and triggers) as will messsage be suppressed, like '(1 row(s) affected)' . this enhance performance and reduces netwerktraffic
Use a less possible SET statements because they can issue a recompilation of the stored procedure.
Use a proper length of input parameter variable. Using a to large input parameter variable can cause SQL injection and it reduces memory usage.
don't use WITH RECOMPILE with your stored procedures
Keep your stored procedures short. Break long stored procedures in smaller stored procedures.
Use as less possible "WITH ENCRYPTION". Only use this when end users have access to the database and/or when you're using source control software.

Greetz,
Hennie