Keep It Simple and Fast

The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties

2013-05-10T10:17:00.000+02:00

You are a part of the local administrators group on the server. You want to add a user to the Server administrator group of your Analysis Server. After adding the user you will get next error message: The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties.

Solution: Start SQL Server Management Studio with the option 'Run as Administrator'. Now it is possible to add users to the Server administrator group of your Analysis Server.

The sqlncli10 provider is not registered on the local machine. Failed to connect to the server.

2013-04-10T17:29:00.001+02:00

When you configure the data source of your Office 2010 Excel Power Pivot sheet, you can get the message:
Failed to connect to the server. Reason: The sqlncli10 provider is not registered on the local machine.

This can happen if you want to connect to a SQL 2008 (R2) server. On the client you need to have installed the Microsoft SQL Server Native Client (SQL Server Native Client). This is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver.

Solution: Install the sqlncli10 provider on your machine.

The sqlncli10 provider for SQL 2008 R2 can be downloaded from here.

X86 package.
X64 package
IA64 package.

In case you have a SQL 2012 server and have received a Power Pivot sheet which want to use the SQLNCLI10 provider, you can change the data provider to use to data provider for SQL 2012: SQLNCLI11

Press the Existing connection button in the Power Pivot Window
Select the PowerPivot Data Connection
Press Edit
Specify the SQL server name
Specify the Database name
Press the advanced
Select Provider: SQL Server Native Client 11.0

In case you can't selected the SQL Server Native Client 11.0. The provider is not installed on your client. The SQLNCLI11 provider for SQL 2012 can be downloaded from here:
X86 package
X64 package

After installation it should be possible to configure your datasource, to be able to update your Excel Power Pivot sheet with all data.

Tsql script to see creation anf modification time of all indexes in database.

2013-04-05T15:34:00.001+02:00

Next script will show the modification date of all indexes in the selected database.

Select s.name, t.name, t.create_date, t.modify_date,i.name, c.name
From sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
ic.column_id = c.column_id
Where i.index_id > 0
and i.type in (1, 2) -- clustered & nonclustered only
and i.is_primary_key = 0 -- do not include PK indexes
and i.is_unique_constraint = 0 -- do not include UQ
and i.is_disabled = 0
and i.is_hypothetical = 0
and ic.key_ordinal > 0
Order by 4 desc

Performance tips for your Power Pivot sheet

2013-02-28T15:42:00.000+01:00

Power Pivot is a really good personal Business Intelligence tool with a great performance. However, for every tool there are tips to optimize the performance. In Power Pivot you need to define the BISM. (Business Intelligence Semantic model), please take next tips into consideration during the design of your BISM model:

Use views to import data in Power Pivot. The view will contain the business logic of how the data is stored in your database. If changes are made to your business logic, you only need to change the views. The Power Pivot sheet will still work.
Use logical columns names in the views. For instance [Account code] in stead of debnr. Everybody should understand what kind of content is stored in each column.
Import only columns you really need. Avoid SELECT * FROM MyView1 As described in my previous blog post: Memory management in Power Pivot, all data is kept in memory. Every column which is not used will use memory which can not be used for other purposes.
Import columns which are useful for analytics purposes. For instance for customer data: Account code, Country, State. Columns like street name are not so useful. As described here, it will create a lot of distinct values in your dictionary for this column. This will have a negative impact on performance.
Import DateTime columns in 2 separate columns. One Date column and one Time column. If time portion is not useful for your analytics do not import it at all.
Import master data in separate tabs. For instance all item attributes in one tab and use the item key in all transactional tabs. Link the item key from the transactional tab to the item key of the Item master tab.
Reduce the number of rows to import. If you analyse on month level, group all data in the view to the level you want. For instance group by Date, Item, Amount. This will save a lot of rows to import. Of course, this is not possible sometimes because you do not want to loose the granularity of analysis.
Reduce the number of rows to import by selecting only the subset you are going the analyze. For instance your database contains financial transaction as of financial year 2008. If you need to analyze of the current and previous year, import only the last 2 years.
Optimize column data types. A column with few distinct values will be lighter than a column with a high number of distinct values. This is important also for measures, which are considered also possible quantitative attributes. If the measure you are storing is a float and is the result of a calculation, consider reducing the number of digits to be imported. This will reduce the size of the dictionary, and possibly also the number of distinct values.
Avoid high-cardinality columns. Columns with unique ID's like invoice numbers are very expensive. Sometimes you can skip this columns and use the COUNTROWS function instead of the DISTINCTCOUNT.
Use measures instead of calculated columns if possible. Calculated columns are stored as an imported column. This does not apply to calculated measures. A calculated measure is calculated at query time.
In case you need to store a measure in a calculated column, consider to reduce the number of digits of the calculation.
Normalizing data doesn’t have a big effect on the size of the resulting database. However, it might have a strong impact on both processing time and memory required to process data. The key is to find a right balance. A Star schema is in most situation the right balance.

Enjoy it, to make your Power Pivot sheets even more powerful.

Memory management in Power Pivot: Column oriented databases.

2013-02-27T13:29:00.000+01:00

Power Pivot is a perfect personal Business Intelligence tool. It is simple to use and the performance of the Power Pivot engine is really great. To better understand this engine, so you can even better make use of it, I will explain how this engine is working.

Row oriented versus column oriented databases.

All traditional relational databases, including SQL Server, are row oriented databases. They store data in tables row by row. The row of a table is the main unit of storage. Indexes are used to point to all columns of a certain row. It depends on the definition of the index which records belongs to this index.

A column-oriented database, like Power Pivot, uses a different approach. Every column is considered as a separate entity. Data is stored for every column in a separate way. I will explain this with an example.

ID	Car	Engine	Color
1	Audi A4	Petrol	Silver
2	Audi A4	Gazole	Red
3	Audi A4	Gazole	Blue
4	BMW	Petrol	Silver
5	BMW	Gazole	Silver
6	BMW	Gazole	Red
7	Mercedes	Gazole	Blue

Every column will have it's own sorted dictionary with all distinct values and a bitmap index references the actual values of each item in the column by using a zero-based index to the dictionary. Next table will show the dictionary values and index values.

Column	Dictionary	Values
ID	32,23,10,43,57,65,71	2,1,0,3,4,5,6
Car	Audi,BMW,Mercedes	0,0,0,1,1,1,2
Engine	Petrol, Gazole	0,1,1,0,1,1,1
Color	Silver, Red, Blue	0,1,2,0,0,1,2

As you can see, the dictionary can be the most expansive part of the index. Especially if a high number of distinct values exists in a column. The lower the number of distinct values in a column the smaller the size of dictionary for this column. This will make the value bitmap index more efficient.

The xVelocity engine, which is implemented on Power Pivot, is an in-memory database. This means that it has been designed and optimized assuming that the whole database is loaded in memory. Data is compressed in memory and dynamically uncompressed during each query. Because all data is kept in memory it is essential to be critical which data to import in your Power Pivot sheet. For instance customer data can be useful like, country, state. However street name is not efficient. Every customer will have a unique address which will result in a big dictionary without a low number of distinct values. It will have a high number of distinct values.

Enjoy the power of Power Pivot.

How to create XML with a TSQL query?

2013-02-25T18:09:00.000+01:00

In this blog post I will describe how you can generate an XML file using TSQL statements. For instance for data migrations, you need to export data from your SQL database which can be imported via XML in another system.

The solution is really simple. Add ''FOR XML" to your SELECT query.

Example 1:
SELECT res_id, sur_name, first_name
FROM Humres
WHERE Res_id > 0
FOR XML PATH ('Resource')

The „FOR XML‟ always needs to be completed with the „AUTO‟ or „PATH‟ command:

When using „AUTO‟ command every column in the SELECT query will be handled as an attribute in a single element per records.
When including the „PATH(< path name >)‟ command the XML path can be set. Every records starts with its own parent element having the label as defined in the „PATH‟ command. Every column in the SELECT query will be handled as child element.

In this example the „PATH‟ command is used since this allows better control.

Example 2:

The next step would be to include custom column names to be used in the XML elements (rather than using „res_id‟, „sur_name‟, etc.) and include the resource number as an attribute in the „Resource‟ element.

SELECT res_id AS '@number',
RTRIM(sur_name) AS 'LastName',
RTRIM(first_name) AS 'FirstName'
FROM Humres
WHERE Res_id > 0
FOR XML PATH ('Resource')

Explanation:

Use a „@‟ in the column name results in an attribute
Including the RTRIM command trims the value (removing the extra spaces at the end of the value).
In many cases XML is case sensitive. Therefore make sure to use the correct attribute and element names.

Example 3:

A final step in creating a basic XML file would be to include the root element. A root element can be included by simply adding the command „ROOT(< root name >)‟ to the XML command in the SQL query.

SELECT res_id AS '@number',
RTRIM(sur_name) AS 'LastName',
RTRIM(first_name) AS 'FirstName'
FROM Humres
WHERE Res_id > 0
FOR XML PATH ('Resource'), ROOT('Resources')

Please note:
Sometimes a second root element is needed. For instance in Exact (the element). Since the XML formatting of SQL queries only allows one root element, this can only be handled by using sub queries (or adding the root element manually to the output file). The use of sub queries will be explained in the following paragraph.

Example 4: Creating child-elements
There are two ways of generating child-elements as part of your parent element (which is defined in the „PATH‟ command and applies to every record).

Example 4.1 Child elements with 1:1 relationship
The first way can only be used in case there is a 1:1 relationship between the parent element (in our example the resource records) and the child element (in the example below the title record). In this case the child-element can be generated by including the element name in the column names (in the SELECT section):

SELECT
h.res_id as '@number',
RTRIM(h.sur_name) as 'LastName',
RTRIM(h.first_name) as 'FirstName',
RTRIM(p.predcode) as 'Title/@code',
p.aan_oms as 'Title/Description',
p.aanhef as 'Title/Salutation'
FROM humres h
LEFT JOIN pred p ON h.predcode = p.predcode
WHERE res_id > 0
FOR XML PATH('Resource'), ROOT('Resources')

Explanation:

A LEFT JOIN on table „pred‟ has been included to get the prefix data.
Correlation names „h‟ and „p‟ have been included to easily refer to the correct tables (in this case „humres‟ and „pred‟).
By including a forward slash (“/”) in the custom column names, a child element can be generated. The child element name needs to be defined on the left side of the forward slash.
Multiple forward slashed can be used in the column names to use deeper child element levels.

Example 4.2 Child elements with 1:N relationship
In case a 1:N relationship exist, such as one customer having multiple contacts, the child elements should be generated based on a sub query which gets all matching records. In our example, using the resources table, the resource is the parent element and the roles are the child elements. The sub query should get all roles linked to the resource and generate the corresponding child elements.
First create the sub query with a XML mark-up to get all roles:

SELECT
r.RoleID as '@code',
r.RoleLevel as '@level',
rd.Description as 'Description'
FROM humres h2
LEFT JOIN HRRoles r ON h2.res_id = r.EmpID
LEFT JOIN HRRoleDefs rd ON r.RoleID = rd.ID
FOR XML PATH('Role'), ROOT('Roles')

Explanation:

The query gets all resources (humres), linked to roles (hrroles) and the role details (hrroledefs).
The correlation name for humres is set to h2 since this query will become a sub query in which “h” already exists.

Next, this query needs to be part of the main query. This can be done by including it in the SELECT section of our main query and by making sure the sub query returns only the roles per specific resource.

SELECT
h.res_id as '@number',
RTRIM(h.sur_name) as 'LastName',
RTRIM(h.first_name) as 'FirstName',
RTRIM(p.predcode) as 'Title/@code',
p.aan_oms as 'Title/Description',
p.aanhef as 'Title/Salutation',
(
SELECT
r.RoleID as '@code',
r.RoleLevel as '@level',
rd.Description as 'Description'
FROM humres h2
LEFT JOIN HRRoles r ON h2.res_id = r.EmpID
LEFT JOIN HRRoleDefs rd ON r.RoleID = rd.ID
WHERE h2.res_id = h.res_id
FOR XML PATH('Role'), ROOT('Roles'), TYPE
)
FROM humres h
LEFT JOIN pred p ON h.predcode = p.predcode
WHERE res_id > 0
FOR XML PATH('Resource'), ROOT('Resources')

Explanation:

In the WHERE section of the sub query the filter „h2.res_id = h.res_id‟ has been added to make sure only the roles per user are taken.
In the FOR XML section the command „TYPE‟ has been added. If left out, the query result of the sub query will be alphanumeric (varchar) instead of XML. This means, with the „TYPE‟, the sub query result will be printed as a text/string.
Note that in this case it is “legal” to have multiple records and fields coming from a sub query. In standard (non-XML) SELECT queries having a sub query in the SELECT should only result in one record and field.

Example 5. XML file format

When running a SELECT query with XML commands, the XML output is in „Unicode (UTF-8)‟ coding by default:

When saving the output file, the file format will be using this encoding. Therefore, make sure to select the correct encoding type supported by the target application. (One of) the encoding type supported by Exact is „Western European (Windows)‟.

Enjoy it to create your own XML files via TSQL.

Design tips for My favorite dashboard.

2013-01-21T21:41:00.000+01:00

Dashboards are very popular way to display data. Other popular names are (performance) cockpits. Sometimes I see dashboards for which I think, which vision is used to build such a bad dashboard. If you start building a dashboard without a vision it will end up in a dashboard which maybe looks nice as a first impression, but is useless on a daily basis. In this blog post I will describe my vision how you should build a useful dashboard. First of all I will start with 2 examples of a dashboard. Both will show the same information. The only difference is the presentation of the data. Examples are the best way to explain.
Let's start with a dashboard in which a lot of improvements can be made:

Now the same information in presented in a better way:

I will comment on the dashboard which can be improved. In general one rule can be applied: Keep in mind. Less is better.

Report title in big font and printed Bold. Goal of the dashboard is to show attention to the figures not to the title.
Gray background in the report. This is visual fluff. The gray color has no function. Keep in mind. Less is better.
Axe values. A lot of zero's is expensive space and is difficult to read. It will make the bar chart it self smaller. We read with our brains. Is the axis value 200.000 or 2 million?
Red color of the bar chart. Is something wrong? Colors have a function. Red is a color to grab attention that something is wrong (STOP). In this example nothing is wrong. Revenue is better than previous year.
3D graph. A dashboard is not a painting. The dashboard is used in a business environment. Not in a gallery. Always use 2D graphs. They are easier to read and understand. For example: What is the value for period 2 of previous year?
Aqua color with gradient style center. This is visual fluff. Keep in mind. Less is better.
Legend takes a lot of valuable space which result in a smaller bar chart. It's not the legend but the chart which need to most space.
Numbers should not be center-justified in the columns. Right-justified is easier to compare when scanning up and down a column.
Use grid lines carefully. Keep in mind. Less is better.
Matrix header in bigger font, printed bold on a colored background. One way of visualization is enough to emphasize difference between the lines. Using a list level for other lines will show difference between header and sub-lines. Keep in mind. Less is better.
Underline the header. This is useless. Keep in mind. Less is better.
Repeating currency symbol. One currency symbol is enough. Is will save value space in every column.
Hyperlink to other report is printed in the same font color. Use a other color for hyperlinks, so the user can see that a hyperlink to another report is available.
Remove the border of the bar chart.

Some more remarks to take into account during the design of your dashboard.

A dashboard is used on a daily basis. Display only data which needs your attention, so you can take action to improve. Report the KPI's which needs improvement. The dashboard need to help you to achieve your goal. Data content which will not change overtime is use less on a daily used dashboard.
We do not see with our eyes, we see with our brains. Content on the dashboard should be clear to understand. If people need to think about what they see, you need to change the visualization of your information.
A dashboard should fit on one screen. Avoid scrolling bars. With scrolling bars, it can happen that something that needs your attention (RED) is outside your screen.
Do not use shadows. It is visual fluff with no meaning.
Do not use logo's and pictures. It is wasting your valuable space. If needed make it small and place it somewhere out of the way.
What do you want to show or compare? Based on this select the best visualization for it. See : http://www.keepitsimpleandfast.com/2012/07/which-visualizations-should-i-use-in-my.html For example using a pie chart to compare 2 values can be useful However it can take a lot of useful space. Do not use is to compare multiple values especially if you do not know how many values you need to compare. A bar chart is better in this situation.
Display consolidated information, summaries or exceptions. Do not display details. Details are used in slice and dice reports to explain the consolidated information, summaries or exceptions.
Put data which is relevant to each other, close together.
Use a gray color for your fonts instead of black.

Enjoy it to build dashboards to deliver a good user experience for your users.

Display last refresh time in Power Pivot

2013-01-14T19:50:00.000+01:00

With Power Pivot, you can start analyzing your data off-line. After importing the data you do not need a database connection anymore. A lot of people want to know the date of the last time that the Power Pivot data is refreshed. You can do this in the following way:
Add a tab (LastDataRefreshTime) to the Power Pivot window and use next query:

SELECT GETDATE() AS LastDataRefreshTime

After adding the tab to the Power Pivot Window, one record is added in this sheet. This record can be used in you Power Pivot Sheets.

Select the tab in your Excel sheet on which you want to add this LastDataRefreshTime.
Add a Pivot table and select LastDataRefreshTime from the PowerPivot Field List.

By default the measure is added as a SUM. Change this to MAX.

The last data refresh time will now be displayed in your sheet.
Enjoy the Power of Power Pivot.

Exception from HRESULT: 0x800A03EC during starting Power View in Office 2013

2013-01-11T15:30:00.002+01:00

In Office 2013 you can enable Power Pivot and Power View. After opening a Power Pivot sheet you can start using Power View. Power View can be found in the Insert Ribbon of Office 2013.

After pressing the Power View icon next error can occur:
Exception from HRESULT: 0x800A03EC

This error can occur in next situation:

You have created a Power Pivot sheet with Power Pivot version 11.1.3000 in Office 2010.
You opened this Power Pivot sheet in Office 2013.
You press the Power View button in the Insert Ribbon of Office 2013.

Solution: Upgrade your Power Pivot datamodel to datamodel of PowerPivot for Excel 201. To do this follow next steps:

Open the Power Pivot sheet Version (11.1.3000) in Office 2013.
Select the Power Pivot in the Ribbon.
Press the Manage Data Model button in the Power Pivot Ribbon.
You will get next message: This workbook has a Power Pivot data model created using a previous version of the PowerPivot add-in. You'll need to upgrade this data model with PowerPivot for Excel 2013.
Press OK
Start the upgrades of your Power Pivot model.
After upgrading successfully, you can press the Power View button to start using Power View.

Enjoy the Power of Power View.

SSRS reports on the IPad or IPhone has been improved since SQL 2012 SP1

2012-12-13T18:26:00.001+01:00

Almost 2 years ago I wrote a blogpost about SQL Server Reporting Service (SSRS) Reports on the IPad or IPhone. I was the first time you could display SSRS reports on the IPad. Unfortunatly not everything was rendered in a correct way. In SQL Server 2012 SP1 this has been improved.

Here is an example of a report in SQL 2008 R2

Here is an example of the same report in SQL 2012 SP1

As you can see, the strange Blue Question marks are gone.

Starting with SQL 2012 Service Pack 1 (SP1), Reporting Services supports viewing and basic interactivity with reports on Apple iOS devices like IPad en Iphone, with the Apple Safari browser.
Viewing reports in Report Manager (http://myserver/reportserver) is not suppported. You need to start the reports from the report server via http://myserver.reports. Here you can browse to the report and tape the report name to open the report. After opening the report yiu can see that the Export to PDF and TIFF file is supported. More information about the support for Apple iOS devices on SSRS can be found here.
For a video of using SSRS on your Ipad please see:

PowerPivot fieldlist is grey and not selectable

2012-12-03T17:46:00.000+01:00

Sometimes my PowerPivot field list is grey and can't be selected. It can happen when you are busy in an existing PowerPivot sheet. It looks like a bug because a restart of Excel solved my situation.

Solution:

Save your Power Pivot Sheet
Close all other Excel sheets
Close Excel
Check in task manager if Excel.exe is not running
If Excel.exe is still running, kill this task
Start Excel
Open your Power Pivot sheet
Click on a graph and you should be able to select the Field list.

Unfortunatly, is is not clear when and how this happens. I'm using Power Pivot version 11.0.3000.0

Power Pivot: The PivotTable report will not fit on sheet.

2012-11-20T15:02:00.000+01:00

During the refresh of data in an Excel Power Pivot next error can occur:

The PivotTable report will not fit on the sheet. Do you want to show as much as possible?

In the past I created an Excel Power Pivot file with multiple sheets. On every sheet one or more pivots or pivot charts are defined. Big question for me: On which sheet does this error occur? Current error message is to general. It does not tell on which sheet the error occurs. After a while I found the root cause of this problem. In the past I made a Pivot chart on some data. The datasheet of this Power Pivot Chart was hidden. Later on I deleted the sheet with the Pivot Chart. The datasheet is not automatically deleted. In this situation the datasheet was still availabel as hidden sheet. I unhide the sheet. When I looked to the data, it contains a big amount of columns. (Over 256 columns). This sheet with more than 256 column is the root cause of this error. Because the initial Pivot chart sheet was already deleled, I deleted the datasheet. After deleting the datasheet, I was able to refresh all data in my Power Pivot sheet.

Solution: check all datasheets in your Power Pivot sheet for pivots with more than 256 columns.

The alternative for Zipping your SQL backups. Save download and restore time.

2012-10-17T21:50:00.000+02:00

Sometimes you need a SQL database backup to analyze. To minimize the download time people compress the SQL database backup with tools like WINZIP, WINRAR, 7ZIP, ARJ etc...... This is nice but there is a more efficient way. First of all, I will explain the download and restore process of a WINZip backup.
You need to uncompress the database backup file before you can start the restore process it self. Example: you receive a SQL database backup of 50 Gb which is compressed to 5 GB. To restore this database, you need much more diskspace:

5 Gb for the zip file
50 Gb for the backup file
50 Gb for the restored database. (assume their is no empty space in the database)

In total 105 Gb of diskspace is needed.

The more efficient way. Use the Backup compression feature. Backup time is much faster because less disk IO is needed to write the backup file. A compressed backup file can be restored without a seperate uncompress proces. This will save a lot of disk space. In the previous example 50 Gb because you do not need to uncompress the WINZIP file. You will receive a 5Gb database which you can directly restore to the 50 GB database file(s).

In the option tab of the Backup database window you will find at the buttom the Set backup compression option. By default it is set to Use the default server setting. You can change this to Compress backup.

On server level you can change the default compression setting to compressed. Retrieve the server properties of the SQL server. Select the Database Settings property. Check the Compress backup checkbox.

As of now every SQL backup will be compressed.

By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup.

More information can be found here.

SQl 2008 R2 Setup fails: ExecuteStandardTimingsWorkflow

2012-10-15T11:28:00.000+02:00

I tried to install SQL 2008 R2 Enterprise Edition X64 on my laptop which has a brand new image of Windows 7. All Windows updates are installed. I started the setup of SQl 2008 R2 from the root of my installation DVD. I run the setup with the option 'Run as Administrator'. During the Setup Support Files, the setup progress hangs on: ExecuteStandardTimingsWorkflow.

After a while next error while occur: Error writing to file: X86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.4027_xww_e69378d0.cat Verify that you have access to that directory.

Big question: Which directory?

Solution: Your installation DVD is corrupt. Take another installation DVD to complete succesfully the setup of SQL Server 2008 R2.

Script to generate a time dimension table to use in Power Pivot.

2012-09-21T16:45:00.001+02:00

In SQL Server 2012 Power Pivot a new feature is introduced. You can Mark as Date Table. This will enable you to leverage date filtering in Excel. For instance, you can see the revenue totals grouped by different date groupings. For instance per week number, per month, week day number etc. To use this feature you need to have a Time Dimension table.

Create a time dimension table. (See later in this blog how to do)
Mark this table as Date table.
Link from your Revenue table the column invoicedate to the key of the Time Dimension table.

Now you are ready to use this feature.

To create a Time dimension table you can a script (GlobeBI_DimTime.SQL) which can be downloaded from here. At the end of the script you can specify the start date and end date of the Time dimension entries.

INSERT INTO Dimtime
SELECT * FROM dbo.F_TABLE_DATE ('20000101','20201231')

In the script Date entries are created from 1 Januari 2000 up to 31 December 2020.

How to use Dynamic data sources in your SSRS report.

2012-08-08T09:45:00.000+02:00

You can have situations in which your report should be executed on multiple databases. The user should be able to select the desired database on which the report should run. In this blog post I will explain how you can do this.
To be able to select to different database you need to make use of a dynamic data source. A data source makes use of a connection string. It is possible to pass the connection string of a data source as an expression. By using an expression, you can make use of parameter values to pass the servername and database name to the connection string. There is only one restriction of a dynamic data source. The data source should be embedded within the report. It can not be implemented with a shared data source. In this blogpost I will use a second database in which I retrieve the available SQL server\databases on which my reports should be executed.

Open your report
Add 2 report parameters

ServerName
DatabaseName

Add datasource named: DynamicDataSource. Use the a 'hard coded' connection string. For instance: Data Source=MySQLServer1;Initial Catalog=MyDatabase1.

Add datasource to the database with all SQL Server\databases. In my example named: Synergy
Add a embedded dataset to retrieve SQL Server and Database information.
Configure the available values for the report parameters: ServerName and DatabaseName.
Add all datasets and report items to your report.
Test your report using the 'hard coded' connection string.
If everything works fine, change the 'hard coded' connection string with next expression
="data source=" & Parameters!ServerName.Value & ";initial catalog=" & Parameters!DatabaseName.Value
Run the report and select a value for the report parameters ServerName and DatabaseName

Enjoy it.

Bluescreen caused by CIPCDDDP.SYS after rebooting Windows 7 Enterprise X64

2012-08-07T11:24:00.000+02:00

After rebooting my laptop which runs Windows 7 Enterprise edition (64 bits) I got blue screens (BSOD) caused by driver CIPCDDDP.SYS. I could only boot in safe mode. On another computer I found that the CIPCDDDP.SYS driver is part of the Cisco IP Communicator V7. In Safe mode it was not possible to unistall this driver. This driver is loaded when network drivers are loaded. To avoid this process

I undocked my laptop
Disabled my wireless network using the wireless network swith on my Dell Latitude E6410.
Now I was able to boot without a bluescreen.
After booting I enabled the wireless network card.
Update my Cisco IP Communicator client to version 8.6.2.

After booting I did not get a BSOD but after a while my laptop did not respond to any keyboard input. Therefor I disabled again my wireless network. After booting successfully, I enabled my wireless network card and started Cisco IP Communicator 8.6.2. successfully and was able to make a phone call.

So everything is working again after 90 minutes. I hope this workaround will also help for you if you experience this Blue Screen of Death (BSOD).

Microsoft® SQL Server® 2008 R2 Service Pack 2 available for download

2012-08-07T09:36:00.000+02:00

Service Pack 2 for SQL Server 2008 R2 is available for download, it includes product improvements based on requests from the SQL Server community and hotfix solutions provided in SQL Server 2008 R2 SP1 Cumulative Updates 1 to 5. A few highlights are as follows:

Reporting Services Charts Maybe Zoomed & Cropped Customers using Reporting Services on Windows 7 may sometime find charts are zoomed in and cropped. To work around the issue some customers set ImageConsolidation to false.
Collapsing Cells or Rows, If Hidden Render Incorrectly Some customers who have hidden rows in their Reporting Services reports may have noticed rendering issues when cells or rows are collapsed. When writing a hidden row, the Style attribute is opened to write a height attribute. If the attribute is empty and the width should not be zero.

You can download SQL Server 2008 R2 Service Pack 2 from here.
Succes with upgrading your SQL Server with this Service Pack.

Which visualizations should I use in my dashboard or cockpits ?

2012-07-05T15:19:00.000+02:00

With SSRS you can build very nice reports. You can make use of a lot of different report types like:

Table
Tablix
Chart
Gauge
Map
Data Bar
Sparkline
Indicator

Every report type has it's own purpose. To make your reports a success, the report should directly tell you what you need to know. One aspects of this, is to apply the dashboard design rules to your reports.
Another aspects is to choose the best report type. I will use a tree stucture you can use to select the best report type. For your report you should answer yourself some questions about the report:

Comparison

Among Items

Two variables per Item: Variable width column chart
One variable per Item

Many categories: Table
Few categories

Many Items: Bar Chart
Few Items: Column Chart

Over Time

Many Periods

Cyclical data: Polar Chart
Non Cyclical data: Line Chart

Few Periods

Single or Few categories: Column Chart
Many categories: Line Chart

Relationship

Two variables: Scatter chart
Three variables: Bubble chart

Distribution

Single variable

Few Data Points: Column Histogram
Many Data Points: Line Histogram

Two variable: Scatter Chart
Three variable: 3D Area Chart

Composition

Changing over time

Few Periods

Only relative difference matters: Stacked 100% Column Chart
Relative and Absolute difference matters: Stacked Column Chart

Many Periods

Only relative Difference matters: Stacked 100% Line Chart
Relative and Absolute difference matters: Stacked Line Chart

Static

Single Share of Total: Pie Chart
Accumulation to total: Waterfall Chart
Components of Components: Stacked 100% Column Chart with Sub components.

A nice picture of this tree structure can be found on : http://extremepresentation.typepad.com/photos/uncategorized/choosing_a_good_chart.jpg

Click on the picture to enlarge.

Some observations:

Pie Charts are ONLY used to show Simple Share of Total.
Over Time Difference between few periods (Column charts) and many periods (Line Charts).
3D is ONLY used to display distribution of 3 variables.
Few variables (Column Histogram) versus Many variables (Line Histogram)

Select the report type which is the best to show your data, therefor do NOT select a report type because it looks nice. If you want to have something which is nice, please buy a painting.

Enjoy using this tree structure, to select the best report type for you report.

How to upload a big PDF to your IPad

2012-07-04T11:27:00.001+02:00

Today I received a new book to read. In the book, a DVD was included with a PDF version of the book. I really like this because now i'm able to read the book on my IPad. In past I send an email to myself and opened this email on my IPad. This gives me the opportunity to open the PDF with IBooks. However, the PDF of this new book is 25 Mb. This is too big to attach to my email, so I have to look to another way get the PDF on my IPad.

How to do this?

Upload the PDF file to your Skydrive account.(https://skydrive.live.com/)
Install the Skydrive app on your Ipad\Iphone. (http://itunes.apple.com/us/app/skydrive/id477537958?mt=8)
Open the Skydrive app and login with you skydrive credentials.
Open the PDF.
Click on the reply icon in the left bottom.
Select Open in other app.
Accept warning that it can take a while to open files bigger than 2 Mb.
Select IBooks

That's all you need to do. Happy reading on your IPad.

SQL Azure connection termination causes.

2012-06-05T09:41:00.000+02:00

Microsoft SQL Azure database is a clud based relational database service that's hosted and maintained by Microsoft. Multiple databases from different customers are hosted on the same server. To garantuee performance for all these different customers, the SQL server should not come in an overload state. SQL Azure has the ability to monitor and rebalance active and online user databases. To achieve this, SQL Azure continuously gathers and analyze database usage statistics and will terminate connections when necessary. Throttling is a mechanism used by SQL Azure to prevent machines from becoming overloaded and unresponsive. Throttling can be divided in:

Soft throttling. This applies to the database consuming the most resources on the box. Soft throttling happens when a physical machine seems to be on the way of beig overloaded, unless its workload is reduced.
Hard throttling. This is the final stage of throttling. It happens when the machine is critically impacted due to overload. It terminates existing operations and prevents new ones until the metric returns below expected thershold.

This blog will summarize all connection terminations which can happen:

Worker threads. When soft throttling limit for worker threads on a machine is exceeded, the database with the highest requests per second is throttled. Existing connections to that database are terminated and new connections to the database are denied, until number of workers drops below soft limit
Database size. When the database space allocatted to user db is full, the user gets a db full error.
Physical database space. When total database size on a machine exceeds 90% of total space available on machine, all databases become read-only. Load balancer ensures the situation is resolved by balancing databases across machines.
Log bytes used. SQL Azure supports transactions generating log of up to 2 GB in size.
Transaction log length. Uncommitted transactions can block the truncation of log files. To prevent this, the distance from the oldest active transaction log sequence number (LSN) to the tail of the log (current LSN) cannot exceed 20% of the size of the log file. When violated, the offending transaction is terminated and rolled back so that the log can be truncated.
Transaction Lock Count. Sessions consuming greater than one million locks are terminated.
Blocking System tasks. Transactions request locks on resources like rows, pages, or tables, on which the transaction is dependent and then free the locks when they no longer have a dependency on the locked resources. Due to these locks, some transactions might block resources required by system sessions. If a transaction locks a resource required by an underlying system operation for more than 20 seconds, it is terminated. In addition, any transaction that runs for more than 24 hours is terminated.
Tempdb usage. When a session uses more than 5 GB of tempdb space, the session is terminated.
Memory use. When there are sessions waiting on memory for 20 seconds or more, sessions consuming greater than 16 MB for more than 20 seconds are terminated in the descending order of time the resource has been held, so that the oldest session is terminated first. Termination of sessions stops as soon as the required memory becomes available.
Too many requests. If number of concurrent requests made to a database exceed 400, all transactions that have been running for 1 minute or more are terminated.
Idle connections. Connections to SQL Azure database that are idle for 30 minutes or longer will be terminated.
Denial of service. When there are a high number of login failures from a particular source internet protocol (IP) address, SQL Azure will block the connections from that IP address for a period of time. The connection is terminated and no error is returned.
Fail over issues. SQL Azure is flexible to cope with any variations in usage and load. The service replicates multiple redundant copies of data to multiple physical servers to maintain data availability and business continuity. In case of a hardware failure, SQL Azure provides automatic failover to optimize availability for your application. Currently, some failover actions may result in an abrupt termination of a session.

More details about all these termination causes and returned error messages can be found here.

How to create a timeline in a SSRS report.

2012-05-18T11:43:00.000+02:00

Timeline reports can be very usefull to display what is happening during a specific time period. For instance, you have a server on which a lot of background tasks are running. In the beginning, you know which jobs are running at which moment. However, when the number of jobs are increasing, you will lose the overview. Especially when the execution times of the background jobs are taking longer after a while. A timeline report will help you to visualize all running background jobs. In SQL Server Reporting Services (SSRS) their is not a standard reporting item to make a timeline. However, there is a way to do this with the current report items. In this blogpost, I will explain how you can make a timeline report. I will use the example to display which backgroundjobs are running on which moment.

First of all we need to have a dataset which the execution times and durations of the backgroundjobs.
I have table named: Activitylog. In this table the starttime of the background job is registered including the execution time (duration).

SELECT StartTime, APP, Duration FROM ActivityLog

The report will have 4 filters to specify a date range. This date range is divided in a date and hour of thedate on which the backgroundjobs have been executed. (@MeasureDateStart, @MeasureDateEnd, @MeasureHourStart, @MeasureHourEnd)

This will results in a dataset which calculates the EndTime of the backgroundjob based on the StartTime and duration of the backgroundjob. It will use the parameters: @MeasureDateStart, @MeasureDateEnd, @MeasureHourStart, @MeasureHourEnd to select the data you need. All backgroundjobs will be displayed which have a start time and or end time within the selected time period.
SELECT AL.App, AL.StartTime,
DATEADD(ms, AL.Duration, AL.StartTime) AS EndTime, AL.Duration
FROM ActivityLog AS AL
WHERE (AL.StartTime >= @MeasureDateStart) AND
(DATEPART(hh, AL.StartTime) >= @MeasureHourStart) AND
(DATEADD(dd, 0, DATEDIFF(dd, 0,DATEADD(ms, AL.Duration, AL.StartTime))) <= @MeasureDateEnd) AND
(DATEPART(hh, AL.StartTime) <= @MeasureHourEnd)

The dataset is ready for use.

Start Business Intelligence Development Studio(BIDS) and create a new report with a datasource to the database and define the dataset.
All defined parameters in the dataset will be added automatically to the report.
Add the report item Chart and select the Range Bar.
Define the chart, Values: Startime ;Series Groups: App ;Category Groups Details.

Configure the StartTime by opening the series properties.
if desired you can configure the tooltip to show additional information.
Configure the horizontal Axis to display the hours by opening the properties and select custom format: hh

Your report is ready for use and should look like the first picture in this blogpost.

Auto refresh of SSRS reports.

2012-05-08T13:24:00.000+02:00

In some situations you want to use a SSRS report to monitor activity on your solution which requires some action of people. For instance your internal helpdesk has made an overview of the number of logged errors in the last 10 minutes. This overview will help the helpdesk in their daily operation. In SSRS you can configure your report to refresh automatically. Of course you should be very carefully in implementing this feature. It can result in a lot of recurring datbase activity which can have a big impact on the overall performance of your solution.

To configure auto refresh of your report, open the property windows of your Report. In the Other section you will find a property AutoRefresh. Sets its value to 60 and the report will auto-refresh every 60 seconds.

Picture: Milford Sound, New Zealand Dec 2011.

The difference between drill down and drill through reports.

2012-04-24T10:31:00.000+02:00

During the design of reports and dashboards I have seen that a lot of people do not know the difference between drill down and drill through reports. In most situations people uses the term: Drill down for both situations. However, there is a difference between these reports.

Drill through reports:
A drill through report allows you to go from summary to detail. For instance: You have a financial application in which you can show the balance and profit and loss for a specific time frame. Every general ledger displays the total amount of all transactions for this specific period. By clicking (Drill through) on one of the general ledgers you will see the underlying transactions for this specific period.

Drill down reports:
A drill down reports allows you to look to the data in different levels. For instance, a report displays information on Country level if we drill down the report displays information on state level. Next drill down will display information on city level. So Countries contains States, States contains Cities. So we can view all levels through drill down.
Another examples is Year contains Months, Months contains Weeks, Weeks contains Days, Days contains Hours, Hours contains Minutes.

Picture: My drill down from the Kawarau Bridge, New Zealand Dec 2011

How to display a multi value parameter in a text box in SSRS.

2012-04-19T10:51:00.000+02:00

In my previous blog I described how you can pass a multi-value parameter to another SSRS report via drill through. After doing this, sometimes you want to show the used multi-value parameters in a text box. The solution for this is very simple. This can be achieved by using the JOIN function. I will explain with an example @Hostname as multi-value parameter.
Open the text box and define next expression:

="My own text: " + Join(Parameters!Hostname.Label,", ")

Other blog post about multi-value parameters: How to pass NULL value to a multi-value parameter in SSRS?

Picture: Milford Sound, New Zealand Dec 2011.