Skip to content

My MS SQL Anki notes deck is now shared on AnkiWeb.net

2013 August 22
by admin

As I’m going to prepare for exams 70-457 and 70-458 to transition my certification to MCSA,
I decided to share Anki deck I prepared. You’ll find it on Anki website.
I used this deck for MCTS/MCITP certification and I found Anki and spaced repetition principles interesting and it worked for me ! I’ll be adding some new notes to this deck and will upload it as I’ll progress through the material.
Hope you’ll find it useful and it will help you as much as it helped me !

Genealogy

2012 September 4
by admin

I inherited about 50 8mm cine reels and wanted to convert them to some digital format for future generations. That triggered my curiosity about my ancestors and where did we come from. I stumbled upon site familysearch.org and found it extremely interesting since they have digitalized some church records (births, deaths, marriages) from Slovakia. Apparently there was some controversy regarding this in Slovak press like what’s hidden motive of Mormon church making all those old records available and something about breeching Slovak Privacy & Information Act – that is – they shouldn’t make available records from 20th century.

Anyways, it looks like not all scanned records were processed yet but nevertheless I was able to trace my family almost back to 17th century. It’s quite fun for me to trace missing links and putting this puzzle of our family together. I might not able to solve it to degree I would like to, but still it’s interesting read and makes one wonder about all stories which could be read from old records. (It’s scary to see child mortality rate 200 years back).

Funny thing is to notice how spelling has changed during all those years. I found different variations of my name Verčík. I found Vertsik, Vercsik, Verczik, Veressik, Vertsik, Vercik, Werczik and most likely some miss-spellings like Vercžik, Verčzik and so on. Would be interesting to know how would SOUNDEX function cope with this 😉

Few points to remember :

– searchable database is wonderful to start with, however it might be worth checking original scan – you might  find some errors or some simplifications which might be crucial ( some births were assigned to different village, some information was not yet processed (marriages in my case) and it contains some relevant information as well – age of groom and spouse, witnesses names)

– search from different angles – use mothers’ name from primary search to find if there’s some misspelings or other siblings – try to cross check different records – births vs marriage records – could you find groom’s birth (baptism) in the right year ?  (date of marriage minus age of groom on marriage date)

– cross check different sources – there are some census/tax records in Hungary which might be helpful

Actually, anyone dealing with data quality should try this kind of investigation, you find some issues still relevant today !

For instance – how some misspellings are dragged along and after while they became new “entity” of its own. (or maybe it’s not misspeling – who knows ?). How one person can be known by different name variations.

You shouldn’t confuse map with territory !

I actually found in deaths records one candidate for world’s shortest novel  : “24/12/1806 Joannes Vercsik 5 years old” (we are celebrating Christmas on 24/12 – did they back then ?).

Anyone has something interesting to share from family history ?

Reporting Services dynamic custom parameter

2012 August 23
by admin

I wanted to have something else instead of default parameter control on the top of the rendered report. This is what I was able to come up with in the end. You have a menu on the left and by clicking on a country values in the report on the right will change accordingly by “dynamically” changing the MDX WHERE clause in underlying SSAS query.

By dynamic I mean that if you add a country to “Geography” dimension it will show in the menu. It’s not working the way the regular parameter control is working. Each click on the country means a query to SSAS, which is not a case in parameter control – you set all the parameter options and THEN it will render the report.

Main points of this solution :

Menu on the left is tablix with just one column and hidden header.
What makes it “dynamic” – e.g. that you can toggle option on and off – is background color function depending on
variable/parameter pair which is storing actual and “clicked” option stored in string in form of 1 or 0 on particular position in the string from the left. For instance, “0011” means that options 3 and 4 (Canada and France in this case) are on and the rest is off.
When you click on any country, report parameter called “rpCountry” is computed as combination of report variable vToggle  and position of clicked column in “menu”. That is – vToggle is storing previous setting of menu and parameter rpCountry is storing current setting of menu and after report is rendered vToggle is set to rpCountry value.
Since background color is set dynamically based on position of a row and vToggle value,  it appears that menu row was toggled on and report values has changed accordingly. When in fact it changed color AFTER report was rendered again.
Underlying MDX query takes rpCountry string as an parameter and WHERE slicer clause is set “dynamically” accordingly to which country is toggled on.

This is the MDX query for menu :


WITH MEMBER Measures.x AS [Geography].[Country].CurrentMember.Name
SELECT { Measures.x} ON COLUMNS,
{ ([Geography].[Country].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Adventure Works] CELL PROPERTIES VALUE

This is the MDX query for the table of actual values :


SELECT NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS,
NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works]
WHERE

FILTER([Geography].[Country].AllMembers,
MID(@rpCountry, RANK([Geography].[Country].CurrentMember, [Geography].[Country].Members ),1) =
"1" )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

What’s crucial here is the WHERE clause where the cube is sliced according to @rpCountry parameter which is just menu string representation. What the Filter does is that it will select just those members of [Geography].[Country] hierarchy which position (computed by RANK) in the parameter is set to 1 (decided by MID function).

Menu tablix textbox has set Fill propery function :


=IIF(MID(variables!vToggle.Value, RunningValue(Fields!x.Value, CountDistinct, Nothing),1) = "1",
"LightBlue","Silver")

This is coloring each row of the menu depending on row number ( nice trick with RunningValue here – need to find proper author reference here) and variable vToggle which is holding current menu setting.

There’s also Action property set :

Function is :


=IIF(Mid(Variables!vToggle.Value, RunningValue(Fields!x.Value, CountDistinct, Nothing),1) = "1"
,
Mid(Variables!vToggle.Value, 1, RunningValue(Fields!x.Value, CountDistinct, Nothing) -1) & "0"
& Mid(Variables!vToggle.Value, RunningValue(Fields!x.Value, CountDistinct, Nothing) + 1)
,
Mid(Variables!vToggle.Value,1, RunningValue(Fields!x.Value, CountDistinct, Nothing) -1) & "1"
& Mid(Variables!vToggle.Value, RunningValue(Fields!x.Value, CountDistinct, Nothing) + 1)
)

This is setting 1 or 0 on row number position in the string. This is what it makes work – it’s building the parameter for which countries should be shown in the report.

There’s report variable vToggle :

And report parameter rpCountry is set :

I hope I didn’t forget anything important here.

Some caveats : you might want to do some handling regarding All member – I just wanted to show a principle. Default value of parameter is set arbitrary – you have to be careful to not exceed number of hierarchy members. All functions (RANK in MDX or RunningValue in SSRS) are order dependent – that means you have to bear in mind how are members in hierarchy sorted. I already mentioned it, but this involves round-trip to server – you click on menu and query is send to server and report rendered again. But that’s the case with reporting services – it’s not an application, it’s the reporting tool.

Anniversary

2012 April 24
by admin

It’s quite fitting that I’m starting blogging now 20 years since my first published article (not that I wrote that much afterwards). It was in 1992 and magazine was called Bit. The article was basically small introduction how to find “unlimited lives” and hack the games on Commodore 64/128D.

Back then I was on high school so I reckon this was my first “freelancer job”.

That Commodore 128D was equipped with 5 1/4 floppy disk and cool green monochromatic monitor. It had two modes – classic Commodore 64 and CP/M operating system in 80 characters console-type mode.
I was utterly disappointed when I’ve got it because all my friends had Sinclair ZX Spectrum and frankly I haven’t foggiest idea that something like Commodore 64/128 exists. Neither I wasn’t able to figure out what to make of those funny 5 1/4 floppy disks when all my friends were using those cool tapes ! In the end I was glad that I got Commodore – I learned a few things about 6502/6510 CPU (well, CPU, two registers X,Y and one accumulator A if I remember correctly). It was quite amazing what back then programmers could do with such limited memory and computing speed. That was first time I heard digitized music on computer (I guess it was something like 5 seconds sample of a Wham! song). Seems like memory/CPU constraints made programmers pretty creative.

Anyway, back to the blog as such. I haven’t figured out yet whether it will be personal or professional blog or even if this will be published at all. I take this attempt to blog as exercise in perseverence (new blog every week, ehm ehm) and probably also as something to establish my online presence. It doesn’t come to me naturally to write something for all the world to see, but it could be fun and I hope as well that this writing will push my comfort zone somewhat further.
Let’s see how it goes…