BI and the Anscombe quartet

The anscombe quartet is showing us something that is also related to Business intelligence. In Business Intelligence it's familiar to show KPI's or business metrics. A KPI as one number hides some times important information like the anscombe quartet is showing us.

And, they all have the same mean, variance, deviation, correlation and lineair regression!

What is this saying us? The statistician Francis Anscombe suggested this quartet to demonstrate the importance of graphical data analysis and the effect of outliers before they decide on a statistical analysis on the basis of their characteristics. The data sets show that the simple statistical characteristics are not always sufficient in order to describe the data.

Conclusion

Be very cautious showing numbers in General Business Metrics and KPI's in your report and your dashboards. Tell the whole story behind a KPI or Business metric and do not only show a number.

SSRS : Multi Language Reporting

INTRODUCTION

Years ago, I've worked in a team building a multiluanguage portal for an international company. We've built all kinds of functions for translating reports. Now, I've to built another multilingual portal for an international company again and google led me to the following blog on Codeproject. This is a neat solution for building multilingual reports. It stores labels, languages and translations. In this example a language parameter is used for parameterized translations. For my current project it seems that this is a cool solution.

TABLES

In the example below I've created three tables: Label, Language and Translations. The Label contains the ID of the field that needs to be translated. I've modded the language table a bit so that the language field is not a descriptive field but contains the format string equal that of the regional settings on your windows system (eg. en-US). Now you can translate your report automatically with the regional settings of your windows system. I renamed the LabelText table to Transalation because that's what it actually does, in my opinion. This is the DDL:

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

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

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

CREATE TABLE [dbo].[Label](
 [LabelID] [int] IDENTITY(1,1) NOT NULL,
 [Description] [varchar](900) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Translation](
 [LanguageID] [int] NOT NULL,
 [LabelID] [int] NOT NULL,
 [LabelTranslation] [nvarchar](max) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Language](
 [LanguageID] [int] IDENTITY(1,1) NOT NULL,
 [LanguageCode] [varchar](100) NOT NULL,
 [Active] [bit] NOT NULL
) ON [PRIMARY]
GO

Here is some content of the table:

INSERT INTO [dbo].[Label]  
           (Name)
     VALUES
           ('ReportName1'),
           ('PeriodFrom'),
           ('PeriodUntil'),
           ('Currencies'),
           ('Total'),
           ('Time'),
           ('Source'),
           ('Location'),
           ('Page'),
           ('TotalPages')               
GO

INSERT INTO [dbo].[Language]  
           ([LanguageCode],
            [LanguageName],
           Active)
VALUES ('en-US', 'English (United States)', 1), 
  ('nl-NL', 'Dutch (Netherlands)', 1),
  ('fr-FR','French (France)',0),
  ('de-DE','German (Germany)',0),
  ('it-IT','Italian (Italy)',0),
  ('ja-JP','Japanese (Japan)',0),
  ('es-ES','Spanish (Spain)',0),
  ('ar-SA','Arabic (Saudi Arabia)',0),
  ('zh-CN','Chinese (PRC)',0),
  ('zh-HK','Chinese (Hong Kong S.A.R.)',0),
  ('zh-TW','Chinese (Taiwan)',0),
  ('cs-CZ','Czech (Czech Republic)',0),
  ('da-DK','Danish (Denmark)',0),
  ('fi-FI','Finnish (Finland)',0),
  ('el-GR','Greek (Greece)',0),
  ('he-IL','Hebrew (Israel)',0),
  ('hu-HU','Hungarian (Hungary)',0),
  ('ko-KR','Korean (Korea)',0),
  ('nb-NO','Norwegian, Bokmål (Norway)',0),
  ('pl-PL','Polish (Poland)',0),
  ('pt-BR','Portuguese (Brazil)',0),
  ('pt-PT','Portuguese (Portugal)',0),
  ('ru-RU','Russian (Russia)',0),
  ('sv-SE','Swedish (Sweden)',0),
  ('tr-TR','Turkish (Turkey)',0),
  ('bg-BG','Bulgarian (Bulgaria)',0),
  ('hr-HR','Croatian (Croatia)',0),
  ('et-EE','Estonian (Estonia)',0),
  ('lv-LV','Latvian (Latvia)',0),
  ('lt-LT','Lithuanian (Lithuania)',0),
  ('ro-RO','Romanian (Romania)',0),
  ('sr-Latn-CS',' Serbian (Latin, Serbia)',0),
  ('sk-SK','Slovak (Slovakia)',0),
  ('sl-SI','Slovenian (Slovenia)',0),
  ('th-TH','Thai (Thailand)',0),
  ('uk-UA','Ukrainian (Ukraine)',0),
  ('af-ZA','Afrikaans (South Africa)',0),
  ('sq-AL','Albanian (Albania)',0),
  ('am-ET','Amharic (Ethiopia)',0),
  ('hy-AM','Armenian (Armenia)',0),
  ('as-IN','Assamese (India)',0),
  ('az-Latn-Z','Azeri (Latin, Azerbaijan)',0),
  ('eu-ES','Basque (Basque)',0),
  ('be-BY','Belarusian (Belarus)',0),
  ('bn-BD','Bengali (Bangladesh)',0),
  ('bn-IN','Bengali (India)',0),
  ('bs-Cyrl-BA','Bosnian (Cyrillic, Bosnia and Herzegovina)',0),
  ('bs-Latn-BA','Bosnian (Latin, Bosnia and Herzegovina)',0),
  ('ca-ES','Catalan (Catalan)',0),
  ('fil-PH','Filipino (Philippines)',0),
  ('gl-ES','Galician (Galician)',0),
  ('ka-GE','Georgian (Georgia)',0),
  ('gu-IN','Gujarati (India)',0),
  ('ha-Latn-NG','Hausa (Latin, Nigeria)',0),
  ('hi-IN','Hindi (India)',0),
  ('is-IS','Icelandic (Iceland)',0),
  ('ig-NG','Igbo (Nigeria)',0),
  ('id-ID','Indonesian (Indonesia)',0),
  ('iu-Latn-CA','Inuktitut (Latin, Canada)',0),
  ('ga-IE','Irish (Ireland)',0),
  ('xh-ZA','isiXhosa (South Africa)',0),
  ('zu-ZA','isiZulu (South Africa)',0),
  ('kn-IN','Kannada (India)',0),
  ('kk-KZ','Kazakh (Kazakhstan)',0),
  ('km-KH','Khmer (Cambodia)',0),
  ('rw-RW','Kinyarwanda (Rwanda)',0),
  ('sw-KE','Kiswahili (Kenya)',0),
  ('kok-IN','Konkani (India)',0),
  ('ky-KG','Kyrgyz (Kyrgyzstan)',0),
  ('lo-LA','Lao (Lao P.D.R.)',0),
  ('lb-LU','Luxembourgish (Luxembourg)',0),
  ('mk-MK','Macedonian (Former Yugoslav Republic of Macedonia)',0),
  ('ms-BN','Malay (Brunei Darussalam)',0),
  ('ms-MY','Malay (Malaysia)',0),
  ('ml-IN','Malayalam (India)',0),
  ('mt-MT','Maltese (Malta)',0),
  ('mi-NZ','Maori (New Zealand)',0),
  ('mr-IN','Marathi (India)',0),
  ('ne-NP','Nepali (Nepal)',0),
  ('nn-NO','Norwegian, Nynorsk (Norway)',0),
  ('or-IN','Oriya (India)',0),
  ('ps-AF','Pashto (Afghanistan)',0),
  ('fa-IR','Persian',0),
  ('pa-IN','Punjabi (India)',0),
  ('quz-PE','Quechua (Peru)',0),
  ('sr-Cyrl-CS','Serbian (Cyrillic, Serbia)',0),
  ('nso-ZA','Sesotho sa Leboa (South Africa)',0),
  ('tn-ZA','Setswana (South Africa)',0),
  ('si-LK','Sinhala (Sri Lanka)',0),
  ('ta-IN','Tamil (India)',0),
  ('tt-RU','Tatar (Russia)',0),
  ('te-IN','Telugu (India)',0),
  ('ur-PK','Urdu (Islamic Republic of Pakistan)',0),
  ('uz-Latn-UZ','Uzbek (Latin, Uzbekistan)',0),
  ('vi-VN','Vietnamese (Vietnam)',0),
  ('cy-GB','Welsh (United Kingdom)',0),
  ('wo-SN','Wolof (Senegal)',0),
  ('yo-NG','Yoruba (Nigeria)',0)


--en-US
DECLARE @LanguageID   AS int = (SELECT LanguageID FROM [Language] WHERE   LanguageCode = 'en-US')     
DECLARE @LabelID   AS int = (SELECT LabelID FROM Label WHERE   Name = 'ReportName1')  

INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'ReportName!')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'PeriodFrom')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'PeriodFrom')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'PeriodUntil')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'PeriodUntil')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Currencies')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Currencies')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Total')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Total')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Time')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Time')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Source')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Source')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Location')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Location')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Page')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Page')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'TotalPages')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'TotalPages')


--nl-NL

SET @LanguageID = (SELECT LanguageID FROM [Language] WHERE   LanguageCode = 'nl-NL')  
SET @LabelID  = (SELECT LabelID FROM Label WHERE   Name = 'ReportName1')  

INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'RaportNaam!')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'PeriodFrom')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Periode Van')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'PeriodUntil')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Periode Tot')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Currencies')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Valuta')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Total')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Totaal')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Time')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Tijd')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Source')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Bron')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Location')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Locatie')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'Page')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Pagina')

SET @LabelID = (SELECT LabelID FROM Label WHERE   Name = 'TotalPages')
INSERT INTO [dbo].[Translation] ([LanguageID],[LabelID],[LabelTranslation]) VALUES (@LanguageID, @LabelID, 'Totaal aantal paginas')

       
 

The above scripting can be improved be implementing stored procedures. I'll work on that in the future. The next thing to consider is to build a webapplication or .NET application to maintain the translations.

REPORT

Next, in the report I created a dataset for the supported languages and made it visible, but when I'm almost finished I'll change this in a hidden parameter. Below I've printed some important screenshots of the solution.

This is the language dataset for filling the parameter:

The available values for the language parameter:

Then I've created the translation parameter:

Modded the properties accordingly to the following screenshot:

Then I added the code from the codeproject.com and tweaked it a bit.

       
Public Function GetTranslation(Parm as Parameter, LabelID as Integer, _
                Optional AddColon as Boolean=False) as String

Dim t as Integer

For t = 0 to Ubound(Parm.Value)
   If (Parm.Value(t) = LabelID) Then
      if AddColon = true then
         Return Parm.Label(t) & ":"
      else
         Return Parm.Label(t)
      End if
   End if
Next t

Return ""

End Function

And I tested it with an expression in a textbox : Code.GetTranslation(Parameters!Translation, 2)

Depending on the language parameter the right translation is chosen form the translation table.

FURTHER IMPROVEMENTS

When you want to make your report truly multilanguage, then you have to go a step further. Then you have to set the language property in the report, for instance. This will format your fixed elements like a date (eg. 11/5/2014 or 5/11/2014).

Another important thing to consider is the data. I've blogged already about multi currency issues earlier (Part I, Part II and Part III). This solution will transform your amount fields in every currency you want. In a new blogpost I'm going to write about multicurrency (Part IV) I'll show how to use the formatstring and the Fields!Amount("FORMAT_STRING") option to format the currencies ccording to the settings of the SSAS cube. This way the currency symbols are inherited from the SSAS cube. Below an example of the report in English

Here an example in Dutch:

One thing to consider and to improve is to define multilingual values for contents of a table. For instance Bicycle, car and Bus for English and Fiets, Auto and Bus in dutch. In AdventureWorks this is solved by adding extra columns to a dimension: Englishname, FrenchName, etc. Perhaps I'll take a look into that....

CONCLUSION

It's possible to make your report multilingual. Standard, there is no support for a multilingual report in Reporting Services, unfortunately. In C# and .NET you have resource files that stores the translations. With aid of some logic in SSRS we can come quite along way..

Greetz,
Hennie