This blog has been moved to http://www.t-sql.dk
30 jun
Mit Microsoft Connect forslag er blevet implementeret
Mit beskedne bidrag til næste version af SSIS er hermed i hus.
I går fik jeg bekræftelse fra Microsoft om, at forslaget har fundet vej til enten næste version eller næste service pack.
29 jun
Perculiar Named Calculations
In regards to this article about named calculations in SSAS, I had the perculiar finding today, that NULLs are handled somewhat differently than I would have expected.
In my case I had constructed a named calculation, just as in the article, by concatenating two columns, both of type string and allowing NULLs.
The perculiar part was, that when the second value was NULL, the whole expression would result in a NULL.
So, in order for the concatenation to succeed, the expression should be as follows:
[Marital Status] + CASE WHEN LEN([Has Children]) > 0 THEN ‘ ‘ + [Has Children] ELSE ” END
10 maj
How to setup basic dimension security in SSAS
SQL Server Analysis Services offers the possibility to create roles, whereby you can manage the access certain users will have to data in the cube(s) on the database. The following is a basic tutorial on how to create a role Limited Access, used to limit the access to the Product dimension of the Adventure Works cube.
First we right-click the Roles collection on the database and select New Role:
Then we give the role a meaningfull name and perhaps a description to distinguish it even more:
Next we add the members of the role, eg. the users:
We can add as many users as we like, even groups to let he IT Administration handle the permissions:
We then need to decide which cube(s) the role is granted access to:
We jump a few steps here in order to get to the basic part of setting dimension security in SSAS 20008.
Select the Dimension Data tab, and select which dimension(s) the role is to be restricted on:
This particullar role is limited to seeing only members Accessories and Bikes, hence Clothing and Components are prohibited:
In order for us to test this newly created role, we can select to browse the cube using the credentials of the role.
This is done in the browse cube dialog:
We then select our new role:
And voilà, the permissions show up as expected:
For more advanced stuff on security in cubes, please refer to: http://www.ssas-info.com/analysis-services-articles/51-security
9 maj
How to get SSAS to return the correct currency format.
Although setting the language and collation correctly, it’s not easy to see why a specific MDX would not return the right formatting, in regards to monetary, thousands seperator etc.
There is however a way of forcing the language, through a property available on the calculated measure. In the following example, I’ll show that even though the server settings are da-DK (Danish), the results are displayed as en-US.
First, a screenshot of the server settings:
Then the query, that returns en-US formatting:
For this to work, we need to add: Language=1030 as property to the calculated member, then we get:
See more on FORMAT_STRING and LANGUAGE @ http://msdn.microsoft.com/en-us/library/cc879322.aspx
5 maj
How to find what port a service is listening at
In general, this can be used to find the port of any service that you might be interested in.
In my case I needed to setup a small testing scenario, involving a PC and a laptop. The PC containing an instance of SSAS that contains the data for the application.
So I needed to setup the firewall to allow the connection to SSAS from the laptop.
In order for me to do that, I needed the port number that the Analysis Services service is listening at. This can be found in two/three easy steps (should be almost the same on Windows 7 and Windows Server 2008 R2)
First you fire up your Task Manager:
From there launch the Resource Monitor and select the Network tab, then look at the TCP connections section:
or take a look at the Listening Ports section:
17 apr
MDX Query using other hierarchy in same dimension as in slicer
The other day a coworker of mine presented me with a problem in one of our solutions. His basic pain was, that the front-end of the application only allowed for one dropdown containing whatever dimension was set up to be displayed. This dimension is to act as slicer in a scorecard application. Since his KPIs were aiming different levels of different hierarchies in the date dimension, he was experiencing problems when faced with the choice of only one available date hierarchy.
Luckily we are able to solve this issue by means of MDX.
The following statement should display the solution, by the use of the method EXISTS and the knowledge of the structure of the date dimension hierarchies.
WITH
MEMBER [Measures].[Test] AS
( EXISTS([Date].[Calendar Week].Members, [Date].[Calendar].CurrentMember).Item(1), [Measures].[Internet Sales Amount])
SELECT
{[Test]} ON COLUMNS
FROM [Adventure Works]
WHERE { [Date].[Calendar].[Date].&[20070201] }
We use the EXISTS function to find the members of the [Date].[Calendar Week] hierarchy with the slicer set to a member of another hierarchy, in this case [Date].[Calendar].[Date].&[20070201]. Note that we need to get Item(1) of the collection returned, as we get the All member at first index (0).
UPDATE:
As mentioned by Hrvoje Piasevoli (blog|twitter) it is a more clean solution to just query by the tuple made by the week hierarchy and the measure, eg.:
([Date].[Calendar Week].CurrrenMember, [Measures].[Internet Sales Amount])
In our case however, we discovered that the attribute relations in the Date dimension was not setup correctly to allow us to “convert” a member in one hierarchy to another. We ended up with the [All] member of that hierarchy.
We solved the issue by making a common root for the two date hierarchies, as seen in the Adventure Works cube.
30 mar
Lærte noget nyt om arv i dag…
Da jeg sad og skulle implementere et design, kom der en ide op omkring en variabel der skulle udbydes af en abstrakt klasse, for derefter at blive assignet i den nedarvende klasse.
Under selve implementeringen finder jeg på at gøre variablen readonly, men finder ud a at det ikke kan lade sig gøre.
Jeg troede, at så længe jeg assignede variablen i en konstruktør, var der ingen is på koen. Men der skulle jeg så lære noget nyt 🙂
Eksemplificeret ved følgende blok:
public class Foo
{
protected readonly string setInSubClass;
private readonly string setInBase;
protected Foo()
{
setInBase = "base" ;
}
}
public class Bar : Foo
{
public Bar() : base ()
{
setInSubClass = "subclass" ;
}
}
}
Men visual studio skulle vise sig at kende til en begrænsning jeg ikke kendte (det er ikke første gang). Se screensnip:
Og tilhørende build fejl:
28 mar
Slotskage i lyserød
Der begynder så småt at blive stillet større og større krav til udformning af kager til bestemte begivenheder i den lille familie.
Senest fylder den ældste 4 år på onsdag, hvorfor der den seneste tid er blevet eksperimenteret med tiltag i den retning.
Herunder ses forsøget på et prinsesseslot i skøn forening af smør, sukker, sukker, mel, smør, æg, mel, sukker, smør, æg og appelsin også bedre kendt som mazarinkage med glasur og vafler.
Den endelige kage, med hele baduljen kørt på 🙂
dotdottelot dot blog 