This week, I had an interesting problem to solve with SSAS and I wanted to share it (and its solution).
Business description
We’ve three countries (Belgium, Netherlands, France) with two borders (Belgium/France and Belgium/Netherlands). We’ve also two additional borders (Spain/France and Germany/Netherlands) but the countries Spain and Germany are not part of the solution to model.
An oriented border is a representation of a border in one direction: it means that the border Belgium/France has two corresponding oriented borders Belgium to France and France to Belgium. For the border Germany/Netherlands, we only have one oriented border Germany to Netherlands. On each oriented border I’ve a capacity to import something.
External borders of a subset of countries are borders with at least one of their countries not listed in the set. Sample: for the subset with Belgium and Netherlands, the borders Belgium/France and Netherlands/Germany are external, on the other hand, the border Belgium/Netherlands is considered as an internal border.
Expected analysis
- List oriented borders belonging to a set of countries and their corresponding capacity of import
- List external borders of a set of countries and their corresponding capacity of import
- The capacity of import of a set of country is equal to the sum of capacity of import for all its external borders.
The tricky part of the requirement is about external and internal borders for a set of countries. This notion of external/internal depends of the set of countries and so can’t be pre-calculated.
Here under, I’ll define one solution: it’s surely not the only solution to answer to analysis needs but at least this version is correct and is efficient.
Solution
The model has a first dimension named Country with an attribute Country.
CREATE TABLE [dbo].[Country]
(
[CountryId] INT NOT NULL PRIMARY KEY IDENTITY,
[Country] VARCHAR(50) NOT NULL
)
GO
insert into BiddingZone VALUES('Belgium');
insert into BiddingZone VALUES('France');
insert into BiddingZone VALUES('Netherlands');
Then a second dimension Border is added. This dimension has two attributes: Border and Oriented Border. Oriented borders will be displayed with an arrow between the two codes of the countries (fr->be). Borders will be displayed with a slash between them (be/fr).
CREATE TABLE [dbo].[Border]
(
[BorderId] INT NOT NULL PRIMARY KEY IDENTITY,
[Oriented Border] VARCHAR(10) NOT NULL
[Border] VARCHAR(10) NOT NULL,
)
GO
insert into Border VALUES('be->fr', 'be/fr');
insert into Border VALUES('fr->be', 'be/fr');
insert into Border VALUES('be->nl', 'be/nl');
insert into Border VALUES('nl->be', 'be/nl');
insert into Border VALUES('de->nl', 'nl/de');
insert into Border VALUES('fr->es', 'fr/es');
The facts are linked to the border dimension at the grain of the oriented border.
CREATE TABLE [dbo].[Import]
(
[BorderId] INT NOT NULL,
[Import] INT NOT NULL
)
GO
insert into Import VALUES(1, 100);
insert into Import VALUES(2,110);
insert into Import VALUES(3, 70);
insert into Import VALUES(4, 75);
insert into Import VALUES(5, 137);
insert into Import VALUES(6, 68);
Finally, we need to create a factless fact between the Countries and the Borders. The idea is to model the factless fact as the link between the oriented border and the corresponding destination country.
CREATE TABLE [dbo].[BorderCountryAssociation]
(
[BorderId] INT NOT NULL,
[CountryId] INT NOT NULL
)
GO
insert into Import VALUES(1, 2); --be->fr , fr
insert into Import VALUES(2, 1); --fr->be , be
insert into Import VALUES(3, 3);
insert into Import VALUES(4, 1);
insert into Import VALUES(5, 3);
insert into Import VALUES(6, 2);
This is the structure and content of your underlying database. Now we can start the cube section, by creating two dimensions (Country and Border). … trivial.
Then, we add to the DSV a first fact table BorderCountryAssociation with an additional column (set to 1). This additional column will be named Border count. We’ll see the need later.
The fact table Import is added to the DSV and we can create the matrix of the dimension usage with a Many-to-many.
At this moment, most of the analysis needs are already fulfill. The remaining requirement is the problem of internal and external borders. Based on our model an internal border is a border appearing twice in the table BorderCountryAssociation! The field previously added named Border count will count how many times the border appears in the subset defined by the countries. If it’s more than once then the value of import should be set to null to avoid to take it into account.
To modify the value of the fact Import based on the value of the fact Border Count, when we’re at the level of the Border, we’ll use a scope statement. Previously, we’ll add a named calculation (“Import”) in the DMV (table Import). This named calculation will be initially set to NULL.
CALCULATE;
SCOPE([Measures].[Import]);
SCOPE([Border].[Border].[Border].MEMBERS);
THIS = IIF([Border Count]>1, NULL, [Measures].[Import db]);
END SCOPE;
END SCOPE;
This action has solved the problem for the internal borders but unfortunately it has broken the solution for the oriented borders. In order to solve both issues, we need to create an additional scope statement for the oriented border level.
CALCULATE;
SCOPE([Measures].[Import]);
SCOPE([Border].[Oriented Border].[Oriented Border].MEMBERS);
THIS = [Measures].[Import db];
END SCOPE;
SCOPE([Border].[Border].[Border].MEMBERS);
THIS = IIF([Border Count]>1, NULL, [Measures].[Import db]);
END SCOPE;
END SCOPE;
The corresponding result matches with the initial requirements.
Ambiguity vs Information 