Dejan Sarka: Posts

Kako smo požrli NT konferenco

Dejan Sarka — Mon, 29 Apr 2013 10:10:52 GMT

First a quick note to my regular readers. I decided for a post in Slovenian language, because I want to address my fellow citizens only. You are not missing anything, this is not a technical blog.

Končana je 18. NT konferenca, kot kaže, tudi zadnja NT konferenca. Ne morem si kaj, da mi ne bi bilo zaradi tega žal. NT konferenca je bila vedno nekaj posebnega, in to v svetovnem merilu. V najboljših časih je število udeležencev preseglo 2.000, kar je najbrž svetovni rekord glede na število prebivalcev. Če bi npr. v ZDA uspeli privabiti en promil prebivalstva, bi morali imeti več kot 300.000 udeležencev. Zato sem v tujini našo izjemno konferenco vedno omenjal s ponosom. Na žalost bomo imeli še eno stvar manj, na katero bi lahko bili ponosni.

Popolnoma mi je jasno, da v sedanji krizi koncept konference, kot smo ga imeli, ne more več preživeti. Zato mi je tudi jasno, da mora Microsoft Slovenija nekaj spremeniti. Nisem pa prepričan, da je smer, ki so jo ubrali, najboljša. Predvidoma bodo naslednje leto tri konference, ki bodo trajale dva in dvakrat po en dan. S tem konceptom nimam problema, vendar moramo vedeti, da bodo to povsem običajne konference, kot jih imajo povsod po svetu, da ne bomo imeli nič posebnega.

Med samo konferenco sem stalno dobival prispodobo ladje Titanic. Orkester igra, mi se pa potapljamo. Vsak večer zabava z neomejenimi količinami pijače in hrane, ogromna izbira hrane med kosilom, dragi nahrbtniki, in tako naprej. Kot da ne bi bilo nobene krize. Ampak za to ne krivim Microsoft. Glede na pretekle izkušnje je bilo vedno ogromno negodovanja s strani udeležencev, če ni bilo nahrbtnika ali pa majice. Vedno se je tudi našel nekdo, ki mu ni bila všeč hrana. Kot da doma je samo kaviar in pije šampanjec. Meni gre po glavi primerjava na tiste, ki se vedno pritožujejo glede glasbe, doma pa plešejo na poročila. Letos so bile poleg rednih pritožb na tapeti še dodatne. Letos je marsikoga motila lokacija, predvsem dejstvo, da so bila predavanja na dveh lokacijah, v dveh hotelih. Brez vsakega razumevanja za dejstvo, da je za polovico ljudi nemogoče plačati tako drago lokacijo, kot je bila v Portorožu. In ne glede na čudovito okolje, ki ga ponuja Bled, in ki je precej bolj unikatno kot je pač eno od tisočih mediteranskih letovišč.

Kot primer naj navedem komentar, ki sem ga imel priložnost prebrati na FB. Pod sliko kremne rezine je nekdo komentiral »In to je to za € 800…«. No, če hodite na konference samo zaradi žrtja in pitja, potem to res ni to. Pa čeprav ste lahko ob kosilu požrli neomejeno količino kremnih rezin, a se to ni zdelo vredno omeniti nikomur. No, seveda v treh dnevih ne morete požreti kremnih rezin za € 800. Svetujem vam, da denar raje zapravite v gostilnah. Bistvo konference je v druženju, v izobraževanju, v sklepanju poznanstev… Človek bi rekel, da je glavna vrlina premnogih udeležencev preprosto požrešnost.

Požrešnost se žal kaže še drugod. Microsoft Slovenija je že tretjič odpeljal vse udeležence, ki se kitijo z nazivom CIO, na posebno lokacijo. Pri tem so bili managerji mnogih IT podjetij izvzeti. To se je zgodilo že tretjič, čeprav smo sponzorji in predavatelji dali zelo negativno mnenje o tem. Za nas namreč konferenca precej izgubi smisel, če mi ne moremo do ljudi, s katerimi bi lahko sklepali posle. Kot da bi Microsoft Slovenija hotel požreti ves posel, kar ga še je v slovenski IT industriji, in odžreti vse svojim obstoječim partnerjem. Kot da ne bi bil eden temeljev uspeha Microsoft-a ravno odlična partnerska mreža. Rezultat je na dlani – količina sponzorjev in vrednost sponzorstev se že manjša. Pa ne samo zaradi krize! Spet požrešnost.

Še ena požrešnost je v igri. Povsod po svetu, kjerkoli sem bil, so bile cene nočitev v hotelih znižane za udeležence konference. Saj to je vendar normalno, če ne zaradi drugega, že preprosto zaradi količine. No, v Sloveniji očitno to ne dela. Tako kot že prej v Portorožu, so tudi na Bledu hoteli ceno dvignili dan pred konferenco in jo spustili dan po konferenci. Sem preveril, imam dokaze. Torej, preprosto, vse skupaj nas lupijo še hoteli. Za Portorož je bilo to vsaj malo logično, saj 2.000 ljudi na konferenci ne more gostiti nobena druga lokacija v Sloveniji, čeprav je še vedno požrešno in neprimerno. Za Bled mi je pa to dejstvo malo manj razumljivo. Zaradi požrešnosti bodo sigurno imeli precej manj nočitev v prihodnosti.

Ne vem, morda razmišljam naivno. Ampak sprašujem se, ali v tej državi res ni mogoče narediti nekaj skromnega, času primernega? Ali ne bi mogli narediti konference s poceni hrano, malo zastonj pijače (npr. kupona za dve pivi) in možnostjo, da si pijačo naprej plačujemo sami, brez nahrbtnika, ter z resnično nižjimi cenami hotelskih storitev? Ali ne bi bilo bolje previhariti težke čase in skušati ohraniti tisto, kar je dobrega, namesto vse do konca požreti in nato potoniti?

Kar se mene tiče, že vem, kaj bom odgovoril tujcem, ko me bodo vprašali, kaj se dogaja z NT konferenco. Odgovoril bom, da smo imeli najboljšo konferenco na svetu, vendar smo jo preprosto požrli.

What Are DQS and MDS About

Dejan Sarka — Sat, 23 Mar 2013 09:21:43 GMT

This is just a very quick information about SQL Server 2012 Data Quality Services (DQS) and Master Data Services (MDS). If you don't have a clue what they are useful for, this blog post should give you the basic idea.

Many companies or organizations do regular data cleansing. When you cleanse the data, the data quality goes up to some higher level. The data quality level is determined by the amount of work invested in the cleansing. As time passes, the data quality deteriorates, and you need to repeat the cleansing process. If you spend an equal amount of effort as you did with the previous cleansing, you can expect the same level of data quality as you had after the previous cleansing. And then the data quality deteriorates over time again, and the cleansing process starts over and over again.

The idea of Data Quality Services is to mitigate the cleansing process. While the amount of time you need to spend on cleansing decreases, you will achieve higher and higher levels of data quality. While cleansing, you learn what types of errors to expect, discover error patterns, find domains of correct values, etc. You don't throw away this knowledge. You store it and use it to find and correct the same issues automatically during your next cleansing process. The following figure shows this graphically.

The idea of master data management, which you can perform with Master Data Services (MDS), is to prevent data quality from deteriorating. Once you reach a particular quality level, the MDS application—together with the defined policies, people, and master data management processes—allow you to maintain this level permanently. This idea is shown in the following picture.

Not an in-depth blog post, as I said; however, I hope it brings some basic understanding about the purpose of DQS and MDS.

Data Modeling Essentials Seminar at DevWeek 2013

Dejan Sarka — Mon, 25 Feb 2013 10:54:24 GMT

Bad database design and inefficient queries lead to applications that are hard to maintain and upgrade and that don't perform well. Why don't you start with a good design? Join me during the DevWeek 2013 post-conference seminar that provides developers and administrators with essential knowledge needed for a good database logical and physical design for well-performing applications in a single day. Of course, the knowledge gained by attending this seminar helps improving design and performance of existing databases as well.

The Data Modeling Essentials seminar explains the relational model for transactional databases and the dimensional model for data warehouses. However, even the best logical model can't help when the physical implementation is bad. Therefore, the seminar shows in details how SQL Server stores and accesses data, and how to optimize the relational and the dimensional model.

Besides the seminar, you are also welcome to join my conference sessions:

High performance statistical queries
Temporal data in SQL Server
Minimally logged (bulk) inserts
SQL Server data mining
Market basket analysis

Contingency Tables in SQL Server 2012

Dejan Sarka — Tue, 05 Feb 2013 09:42:23 GMT

Contingency tables are used to examine the relationship between subjects' scores on two qualitative or categorical variables. They show the actual and expected distribution of cases in a cross-tabulated (pivoted) format for the two variables. Here is an example of the actual and expected distribution of cases over the Gender column (on rows) and the MaritalStatus column (on columns) of the dbo.vTargetMail view from the AdvetureWorksDW2012 demo database:

Actual
Gender	Married	Single	Total
F	4745	4388	9133
M	5266	4085	9351
Total	10011	8473	18484
Expected
Gender	Married	Single	Total
F	4946	4187	9133
M	5065	4286	9351
Total	10011	8473	18484

If the columns are not contingent on the rows, then the rows and column frequencies are independent. The test of whether the columns are contingent on the rows is called the chi-square test of independence. The null hypothesis is that there is no relationship between row and column frequencies. Therefore, there should be no difference between the observed and expected frequencies. Contingency tables are the base for the chi-square test. However, even without the test, you might notice some relationship between two discrete variables just by seeing the contingency table.

Contingency tables are very simple to interpret, and therefore they are a very popular tool in statistics and data mining. Wouldn't it be nice to have a possibility to create such a nice contingency table checking the association between NumberCarsOwned and BikeBuyer columns from the dbo.vTargetMail like the following screenshot shows with SQL Server tools?

Of course, I would not write this blog if I would not have a solution. The screenshot above is actually a screenshot of a SQL Server 2012 Reporting Services (SSRS) report. The whole story starts with the following query.

WITH

ObservedCombination_CTE AS

(

SELECT NumberCarsOwned AS OnRows,

BikeBuyer AS OnCols,

COUNT(*) AS ObservedCombination

FROM dbo.vTargetMail

GROUP BY NumberCarsOwned, BikeBuyer

)

SELECT OnRows, OnCols, ObservedCombination

,SUM(ObservedCombination) OVER (PARTITION BY OnRows) AS ObservedOnRows

,SUM(ObservedCombination) OVER (PARTITION BY OnCols) AS ObservedOnCols

,SUM(ObservedCombination) OVER () AS ObservedTotal

,CAST(ROUND(SUM(1.0 * ObservedCombination) OVER (PARTITION BY OnRows)

* SUM(1.0 * ObservedCombination) OVER (PARTITION BY OnCols)

/ SUM(1.0 * ObservedCombination) OVER (), 0) AS INT) AS ExpectedCombination

,CAST(ROUND(100.0 * ObservedCombination /

SUM(ObservedCombination) OVER (), 0) AS INT)

AS PctTotal

,REPLICATE('*',

CAST(ROUND(100.0 * ObservedCombination /

SUM(ObservedCombination) OVER (), 0) AS INT))

AS Histogram

FROM ObservedCombination_CTE

ORDER BY OnRows, OnCols

Note that the query uses the new window functions in SQL Server 2012. The result of this query is not pivoted yet, however it contains all of the data needed.

OnRows	OnCols	Observed Combination	Observed On Rows	Observed On Cols	Observed Total	Expected Combination	PctTotal	Histogram
0	0	1551	4238	9352	18484	2144	8	********
0	1	2687	4238	9132	18484	2094	15	***************
1	0	2187	4883	9352	18484	2471	12	************
1	1	2696	4883	9132	18484	2412	15	***************
2	0	3868	6457	9352	18484	3267	21	*********************
2	1	2589	6457	9132	18484	3190	14	**************
3	0	951	1645	9352	18484	832	5	*****
3	1	694	1645	9132	18484	813	4	****
4	0	795	1261	9352	18484	638	4	****
4	1	466	1261	9132	18484	623	3	***

Next part is to create the report. Use the Matrix (OK, Tablix with row and column groups) control. The trick is what to put in the cells of the matrix. The following screenshot shows the expression used for the most detailed cell of the matrix.

Similar expression are used for other cells with data. The expression for the column total, i.e. for the cell that is the intersection of the [OnCols] column and Total row, is

=Sum(Fields!ObservedCombination.Value) & Chr(10) & Chr(13) &

Sum(Fields!ExpectedCombination.Value) & Chr(10) & Chr(13) &