SolidQ Blogs

Kako smo požrli NT konferenco

Dejan Sarka — 2013-04-29T12:10:52+02:00

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.

Rendimiento con índices columnares

Rubén Garrigós — 2013-04-26T21:05:01+02:00

Los índices columnares son un nuevo tipo de índices disponibles en SQL Server 2012 que incorporan el almacenamiento columnar al motor relacional.

Dejando de lado los múltiples inconvenientes y limitaciones de estos índices, voy a centrarme en analizar el impacto de rendimiento teniendo en cuenta los puntos clave que se destacan de este tipo de índices:

- Solo las columnas necesarias para la consulta son accedidas.

- Las columnas están comprimidas lo cual reduce la cantidad de lecturas

- El plan de ejecución en batches reduce el consumo de CPU

Vamos a utilizar la base de datos AdventureWorksDW2012 y comenzaremos por analizar el índice columnar que viene como ejemplo en BOL:

CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSales]
ON [FactResellerSales]
(
    [ProductKey],
    [OrderDateKey],
    [DueDateKey],
    [ShipDateKey],
    ResellerKey,
    [EmployeeKey],
    [PromotionKey],
    [CurrencyKey],
    [SalesTerritoryKey],
    [SalesOrderNumber],
    [SalesOrderLineNumber],
    [RevisionNumber],
    [OrderQuantity],
    [UnitPrice],
    [ExtendedAmount],
    [UnitPriceDiscountPct],
    [DiscountAmount],
    [ProductStandardCost],
    [TotalProductCost],
    [SalesAmount],
    [TaxAmt],
    [Freight],
    [CarrierTrackingNumber],
    [CustomerPONumber],
    [OrderDate],
    [DueDate],
    [ShipDate]
);

Podemos ver que pese a ser un índice no cluster, han incluido todas las columnas de la tabla. La razón que gana peso para esta decisión es que únicamente podemos tener un índice columnar por tabla.

Si lanzamos una consulta que el optimizador considera apropiada veremos como el nuevo operador ColumnStore IndexScan entra en juego:

SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM FactResellerSales
GROUP BY SalesTerritoryKey;

Si comparamos la estimación de costes de esta consulta comparada con otra en la que forzamos el uso del índice cluster vemos que, efectivamente, el uso del índice columnar parece ser mucho más eficiente:

Si comparamos los tiempos de ejecución vemos que la consulta con el índice columnar es más rápida, realiza menos lecturas y consume menos CPU:

(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactResellerSales'. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 17 ms.

(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactResellerSales'. Scan count 1, logical reads 2982, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 26 ms.

Veamos qué ocurre si creamos un índice de cobertura sobre las columnas utilizadas:

CREATE INDEX IX_SOLID ON FACTRESELLERSALES(SALESTERRITORYKEY,EXTENDEDAMOUNT)

En este caso, ya el optimizador nos estima que la operación con el índice de cobertura va a ser tanto o más rápida que con el índice columnar:

El resultado de la ejecución de esta última query muestra que estamos prácticamente a la par en el consumo de CPU y en la duración aunque el número de lecturas sigue siendo considerablemente mayor:

(10 row(s) affected)
Table 'FactResellerSales'. Scan count 1, logical reads 290, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 20 ms.

Si probamos estas mismas queries en un escenario de caché fría (DBCC dropcleanbuffers) veremos que la diferencia en tiempos aumenta a favor del índice columnar:

(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactResellerSales'. Scan count 1, logical reads 52, physical reads 3, read-ahead reads 71, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 22 ms.

(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactResellerSales'. Scan count 1, logical reads 2982, physical reads 2, read-ahead reads 2972, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 93 ms.

(10 row(s) affected)
Table 'FactResellerSales'. Scan count 1, logical reads 290, physical reads 0, read-ahead reads 287, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 33 ms.

Otro factor que debemos considerar es que también es posible comprimir los índices no columnares mediante compresión de fila o de página. Veamos que ocurre cuando comprimimos el índice cluster y el índice de cobertura con compresión de página:

ALTER INDEX [ix_solid] ON [dbo].[FactResellerSales] REBUILD PARTITION = ALL WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = PAGE)
GO
ALTER INDEX [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] ON [dbo].[FactResellerSales] REBUILD PARTITION = ALL WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = PAGE)
GO

Con compresión activa los resultados con caché fría son los siguientes:

(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactResellerSales'. Scan count 1, logical reads 52, physical reads 4, read-ahead reads 71, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 23 ms.

(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactResellerSales'. Scan count 1, logical reads 370, physical reads 0, read-ahead reads 358, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 43 ms.

(10 row(s) affected)
Table 'FactResellerSales'. Scan count 1, logical reads 92, physical reads 1, read-ahead reads 90, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 33 ms.

Vemos que la compresión nos ayuda a reducir las lecturas y el tiempo total en el caso del índice cluster mientras que en el índice de cobertura, al ser menos páginas a leer, realmente la diferencia no es apreciable.

Para poder apreciar un poco mejor las diferencias de tiempos y de consumos de CPU vamos a inflar un poco la tabla ejecutando 5 veces este insert que duplica el número de registros con cada ejecución:

insert into FactResellerSales
SELECT [ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
      ,[ResellerKey]
      ,[EmployeeKey]
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
      ,(select count(*) from [FactResellerSales]) + row_number() over (order by SalesOrderNumber)
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
FROM [dbo].[FactResellerSales]
go 5

Ahora que tenemos ya casi 2 millones de filas volveremos a recrear el índice columnar (tuvimos que borrarlo para poder insertar datos) y desfragmentaremos el resto de índices. Repetiremos las pruebas realizadas anteriormente con caché fría y caliente así como con compresión o sin compresión de página.

Al aumentar el número de registros vemos que las tres operaciones se ejecutan con planes paralelos:

La siguiente tabla resume los valores obtenidos:

Ahora vamos a testear otra consulta que, en principio, parece muy óptima también para un índice columnar, un cálculo de un total acumulado:

La primera diferencia que vemos es que no se está considerando un plan paralelo para el índice columnar. La siguiente tabla resume los valores obtenidos:

Podemos ver que en este escenario el índice columnar ha ofrecido peores tiempos de respuesta. Desgraciadamente el trace flag no documentado 8649 no nos ayuda en este caso ya que parece no afectar a los planes con índices columnares. Si modificamos manualmente el “cost threshold for parallelism” y lo disminuimos, podemos obtener un plan paralelo con un tiempo de respuesta comparable con el del índice de cobertura:

Table 'FactResellerSales'. Scan count 7, logical reads 744, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 547 ms, elapsed time = 195 ms.

Por tanto podemos extraer las siguientes conclusiones:

* Los índices columnares son más eficientes cuanta más lectura física se ve obligado el servidor a ejecutar para resolver la consulta. El algoritmo de compresión del columnar es bastante más eficiente que el algoritmo de compresión de página.

* Con la tendencia a mayor cantidad de memoria en los servidores no es inusual ver servidores con 512 GB o más de memoria. Esto hace que el porcentaje de acierto de página sea muy elevado. En este escenario, un índice de cobertura puede ser más eficiente que un columnar para algunas consultas que utilizan agregados

* En base a los resultados obtenidos consideramos que una estrategia mixta podría dar los mejores resultados que una que se incline solo hacia una alternativa.

Esta estrategia mixta consistiría en el uso de una tabla particionada + indices columnares (para datos históricos, de solo lectura) junto a una tabla no particionada con índices no columnares (para datos no históricos, de lectura/escritura). Ambas tablas podrían unificarse en una única vista para facilitar las consultas que accedan tanto a datos históricos como a datos no históricos.

La parte histórica contaría con un conjunto reducido de índices no columnares (índice cluster, PK e índices no clustered imprescindibles) más un índice columnar que incluya todas las columnas que se utilicen en consultas históricas. La parte no histórica contaría con el conjunto de índices necesarios para la operativa no histórica (índice cluster, PK, índices sobre FKs y todos índices no clustered necesarios).

PASS SQL Saturday #196 Denmark

SolidQ — 2013-04-18T13:38:00+02:00

Hello everyone! I know it is probably a bit late to let you know about this event, but is it really too late?

There are 3 pre-cons available. World-renowned speakers and MVPs: Jen Stirrup, Mark Rasmussen and Denny Cherry. If you may wish to attend these pre-con sessions, register here.

PASS SQL Saturday Denmark, the main (free) event is going to be taking place on April 20 at Microsoft Denmark, Tuborg Boulevard 12, 2900 Hellerup. Gianluca Hotz, Régis Baccaro and Peter Larsson, colleagues from SolidQ are speakers of the #SQLsatDenmark. Let me introduce their sessions:

Gianluca Hotz | SQL Server 2012 AlwaysOn

Description: Business continuity is becoming more and more a central topic for many companies. AlwaysOn is the new solution for high availability and disaster recovery introduced with SQL Server 2012. With the help of live demos, in this session we will cover in details this new technology that extends on the familiar concepts of database mirroring and failover clustering. We will also introduce some other engine enhancements that will help raising the overall service availability.

Régis Baccaro | Database development with SSDT

Description: SQL Server Data Tools (SSDT) is a toolset that allows professional database and application developers to carry out all their database design work for SQL Server and SQL Azure within Visual Studio. SSDT provides a rich SQL Server development experience with first class language services and VS integration, as well as declarative, model-based tools that can be utilized for both online and offline development. In this session we'll dive into Online and Offline development as well as the Productivity and tools that the SSDT team keeps providing to us.

Peter Larsson | Remodel your old EAV design

Description: Sometimes you really need an EAV model. In most cases they are ugly and slow. After this session you will have gained new insights how to turn your old, slow, EAV model into a slim superfast model. There will be real life examples from a recent project. With the techniques displayed in the presentation, I cut the execution time down from 134 days down to 0.5 seconds and cut the storage need from 550GB to 45GB.

SQL Server & Business Intelligence Conference 2013 - Milan

SolidQ — 2013-04-10T18:55:00+02:00

The most important technical conference in Italy, the “SQL Server & BI Conference 2013” will be held in Milan during May 28-29. During these days, SQL Developers, SQL DBAs, BI Professionals and Excel Power Users will gather up to learn as much as they can about SQL Server and BI.

There are four SolidQ Mentors speaking:

Danilo Dominici – “SQL Server 2012 Virtualization Best Practices"

Francesco De Chirico - “Monitoring MDX Queries in Analysis Services

Davide Mauri – “Integration Services 2012: Automating DWH Patterns through Metadata” and “Query Optimizer and Data Distribution Statistics”

Gianluca Hotz – “SQL Server Security in a vulnerable world” and “PowerShell for SQL DBA”

See you in Milan!

Follow our blogs to read more about technical information and tips.

Build 2013 - WOW

Ken Spencer — 2013-04-02T23:27:56+02:00

Its interesting to read the news and see all the negative stuff about Microsoft. Most of the writes don’t know what the real world is all about just like the reporters on the news.

For a read on how important Windows and other Microsoft technologies is, check out this: http://www.zdnet.com/microsofts-build-2013-conference-sells-out-in-under-three-hours-7000013411/

The 2013 Microsoft Build conference sold out in 3 hours. In 2012 it sold out in under 1 hour. So 3 and 1 is not bad. Specially since they have a bigger venue now. So Windows Phone 8 and Windows 8 plus XBOX and of course Windows Azure are really driving things.

SSIS – Data Profiling

Ken Spencer — 2013-03-27T21:30:22+01:00

SSIS (SQL Server Integration Services) is a powerful technology for most things data related. This includes moving data in and out of SQL Server and many other things like copying files or in my case today profiling data.

The following table shows a problem we run into all the time with many clients. In this case we have a lookup table that lists product categories. As you can see there is a lot of bad data there where the category names are not consistent. For instance Belt is also shown as belt, blt, and bt. And it’s not usually just the database issue necessarily its either the database is not designed right and/or the applications are not handling data correctly.

We saw this recently where a database had a global option table for all types of fields and the data was really bogus just like this figure shows.

The only way you can really clean up your data is to profile it and monitor it. And there are good tools coming out like the new features in SQL Server 2012 which I will get into in another post.

This discussion brings me to the SSIS Data Profiler for this post. This SSIS task is really cool and will let us do things like analyze the key strength of columns, look at the column lengths for the current data, analyze the data in those columns and so forth. It’s nice because this is right out of the box in SQL Server. Let’s dig in and see how this works.

The following figure shows the Data Profiling Task on the design panel of SSIS 1012. This task is the only thing in the current project.

Once you have the task in the design surface you can configure it by double clicking it or right clicking and selecting Edit. The next figure shows the edit dialog. When the Data Profiling Task runs it outputs its results to a file. Notice how the DestinationType property is set to FileConnection and the Destination property to the DataProfile.xml connection. This is where the results will end up when the task runs.

Now that you have an output location, you can create a profile of your data. The easiest way to do this is to click the Quick Profile button shown in the last figure. This will open the following dialog:

You can see that I have already selected the Localhost.db connection and the ProductCategory table. Then I checked all of the boxes to create a complete profile. Next I clicked Ok and the profile was done.

Then I ran the package in debug mode.

Now the really cool thing is the Data Profile Viewer. You can find this in the Start menu and also it’s in in Data Profiling Tasks property page in the General area where you can just click Open Profile Viewer (see the 2^nd figure) to run it.

This brings up the following dialog:

This tool allows us to dig into the results of our data profile and see what’s going on with our data. There is lots of good info in this output depending upon what you are looking at. For instance, you can find out the key strength of columns, look at a profile of the data, check column length against data and more.

In the case I mentioned early on, we really want to look at the data and its consistency. You can click on the Column Pattern Profiles item to dig into what your data looks like. The next figure shows this view after I also clicked on the Category column:

You can also click the Drill Down button to the right of All Rows to display all the data that a row represents. For instance if I were to click Drill Down, the display would change to this:

You can see the two discrete types of data that make up PANTS is Pants and pants. To drill into another field, double click it and it will change the drill down details.

As you can see from this image there’s lots of fields that are not correct. The next image shows the Pattern Distribution after I clicked the Pattern header to sort it. Now its clear that Belt and Blt are the same.

Now that I have identified this problem, I can run this query in SSMS:

select * from [dbo].[ProductCategory] where description = 'Belts'

This shows me that we have 3 entries for Belts where we should have 1 entry.

15 Belt Belts

16 belt Belts

17 blt Belts

This type of cleanup should be done on any database that’s been in production for any length of time. When you find these problems, you can look at related tables and figure out where to make other data changes. And you should look at your applications and make sure no one has put in hard references or is feeding a list with hard coded values such as Belt or Blt and such.

It’s also worth looking at cleaning up your data layout and making lookups normalized if they are not already.

What Are DQS and MDS About

Dejan Sarka — 2013-03-23T10:21:43+01:00

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.

Windows Phone 8 - Pandora

Ken Spencer — 2013-03-21T16:01:29+01:00

Today the new Pandora app for the phone came out. Apps are coming out fast and furious.

Check this out on TrendSpotter

More on Htc 8x replacement

Ken Spencer — 2013-03-21T15:59:34+01:00

Man, i love the Windows Phone 8.
Got my new phone from Verizon.

fired it up, logged in. Then went to mail and put in email / password.

Bank. Outlook.com is hooked up and my contacts are there.

Plugged in the phone to the usb and fired up media player. Sync.

Now my phone is right back to where the other one was with no fuss. Just have to reinstall some apps.

Windows Phone 8 – Verizon Support

Ken Spencer — 2013-03-18T20:44:02+01:00

Some good news.

I have the htc 8x and love this phone. There's only been one issue with it so far.

Fairly frequently i get a message that says the SIM card is invalid. I called the other week and Verizon suggested some changes in settings. Got better and then it came back hard.

So today i called. After 5 minutes, i have a new phone coming overnight thanks to the warranty. Yeah for Verizon.

I also found this thread on the issue:

http://answers.microsoft.com/en-us/winphone/forum/wp8-wpnetwork/htc-8x-sim-card-is-missing-or-invalid/16962008-df41-4959-a6ea-f03b00d5d118?page=~pagenum~

It was interesting enough in the last post where they pointed to this article on iphones:

http://wooservers.com/blog/2010/07/10/fixing-iphone-4g-no-sim-installed-problem/

Looks like they have the same issue. I have not tested the tape ideas in this article but if the issue comes up again i am going to give it a try.

PASS SQL Saturday #188 Portugal

SolidQ — 2013-03-15T11:28:00+01:00

SQL Saturday will be held March 16 at Microsoft Portugal, Lisbon, 1990-110, Portugal. As you know, this event is a free training event for SQL Server Pofessionals and SQL Server lovers. This event is about to beat the record of 320+ registrations!

We are proud to announce that from a total of 31 speakers, 5 are part of the SolidQ team:

	Régis Baccaro \| Database development with SSDT “In this session we'll dive into Online and Offline development as well as the Productivity and tools that the SSDT team keeps providing to us.”
	Enrique Catalá \| Parallelism in SQL Server “In this session we will discuss about the parallelism in SQL Server. We will talk about configuration parameters, parallel execution plans, parallel operators and more. We also will talk about problems and best practices.”
	Eladio Rincón \| Parallelism in SQL Server “In this session we will introduce the main concepts about transactional replication and two real cases to show the scale-out benefits of this technology. At the end we will talk about common best practices for transactional replication.”
	Rubén Pertusa \| Sentiment analysis using SSAS 2012 ”This session reviews some text mining and integration techniques for extracting and incorporating these insights from Big Data into our analytical platform. Real life samples and practical demos will be shown using Microsoft technologies. Co-speaker: Javier Torrenteras (SolidQ BI Manager)”
	Milos Radivojevic \| Identifying and Solving Sort Warning Problems ”We will see how to identify this problem in SQL Server 2008 and more elegant in SQL Server 2012 and of course we can discuss how to prevent this problem.”

You will find the extended session details on the event’s official website.

Hope you enjoy your SQL Saturday!

More on 3D

Ken Spencer — 2013-03-10T17:24:47+01:00

Check out this: http://blog.appliedis.com/2012/11/09/my-trip-to-nova-labs-makerspace-in-reston-an-uplifting-experience/

This is from my friend Vishwas. Its pretty cool and nails whats going on these days. Pretty wild.

3d Printing and now scanning - wow

Ken Spencer — 2013-03-09T21:38:34+01:00

The future is certainly getting interesting now. Looks like soon we can scan physical items and turn right around and print them.

I think this technology is really cool. I wonder how this will affect the future.

http://www.pcmag.com/article2/0,2817,2416414,00.asp

Windows a Metro with a twist

Ken Spencer — 2013-03-07T01:05:38+01:00

Check out this new app from StarDock. Modernmix lets you configure metro apps to run in windows just like normal desktop apps on Windows 8. This is a really suite feature as it lets you run apps in a window where you can copy and paste and more.

One nice feature is the ability to run an app in a window and still see it on a multi monitor screen just like the old days. And its really configurable.

You can find it here: http://www.stardock.com/.

The value of focused and targeted training, in college, in business schools and in IT training

Fernando G. Guerrero — 2013-03-03T23:27:28+01:00

I am currently enrolled in an Executive MBA course at Fundesem, a local business school in Alicante (Spain), and I am writing about this when I just celebrated my 54 birthday a couple of weeks ago. I guess I will keep learning as long as my heart keeps me alive.

At this age, I spent there most Friday evenings and Saturday mornings, and I have to spend some of my very limited free time to study and prepare homework, and… I just love it.

I could talk about the value of the trainers, and the value and relevance of the topics covered, but the biggest value comes form the interaction with the other students enrolled in this course. Due to my travel schedule, I need to miss some classes, but I have the opportunity of attending them later at a different location: Albacete. Wherever there is a problem, there is an opportunity as well: due to my scheduling problem, I can enjoy learning from two very talented teams of experienced students, one team in Alicante and a different team in Albacete.

I was driving back home last Friday and I just realized how much I enjoy this training, and how different this is from my first years in college. This drove me to a key question: Why college education has to be so different from professional education?

I enrolled in college (Civil Engineering school at UPV) highly motivated, back in 1976, after several years of working all my holidays as a surveyor, surveying rural roads and irrigation channels in the countryside of Alicante. My father is a retired surveyor, and he taught me Trigonometry and some basic Surveying techniques when I was 11 or 12 years old. So, I became a part time surveyor when I was 15 years old (the year earlier I was only carrying the surveyor ruler, or doing some calculations to learn the ropes, so it would not count as proper work). This helped me contribute to my family finances for some years, and later on gave me the chance of becoming independent and paying for my education through college.

When I arrived to college I expected to learn more about the topics I loved, roads, irrigation channels, pipelines, surveying, and so on. Instead, I was forced to learn something “very important” for civil engineers: the equation of Schrödinger for the second electron of the Helium´s atom. I can´t imagine what type of value this knowledge could bring to a civil engineer. However, my internal memory unit is clever enough to decide what I need to keep in central memory, so I managed to forget that stuff long ago.

I thought that the second year would be better, but it mostly wasn't. Then the third year arrived, but I was too busy trying to pass the exams of tensor calculus and other deep scientific topics, to think about when we would start learning about roads, channels and pipelines. However, that third year I was lucky enough to have a great professor of Surveying and Photogrammetry (Prof. Manuel Chueca Pazos) and I saw for the first time that professors could be relevant in college, with direct real life professional application of their studies. Some years later, I worked for him, becoming a lecturer in his department for six years.

Then, in the fourth year, I had an amazing professor of Applied Statistics (the late Prof. Juan Benet, don´t confuse him with the famous Spanish engineer and author with the same name) who was a the same time deeply technical, highly relevant and very entertaining. I will always remember his classes.

Looking back to those years, I can see that the technical and scientific difficulty of those subjects helped shaping my brain, and that the absurd difficulty of the exams (often with passing rates of less than 10%) helped tremendously my ability to solve complex problems of any kind. However, the personal cost of that effort was tremendous, and attending those courses was very painful to the point that most of us could easily consider those years the worst years of our lives. During those years, I could hardly do anything else than studying all the time, weekends included, and sadly too often I had to decide between attending a class or staying at home studying.

What a difference from my business studies of this year. I am always looking forward to the next day in class. Always inspired by the trainers and my classmates. I hardly miss a class, and I am enjoying this training far more than any training I attended in the past.

We typically say that there is no excuse for being boring in class (or speaking in a conference). A boring trainer or speaker does not get the message through, risking to lose the time of the professionals attending the class or conference session. A the same time, we are on the business of providing relevant training. The type of training that enables professionals to enhance their skills and abilities.

We are not in this business just to make a buck. We are in this business because we believe 100% in its value, short and long term, because we see the results day by day, because we enjoy doing what we do, and because we love it. I hope that this love shows in each and every course we teach, and each and every consulting project we deliver.

Fernando G. Guerrero

Service Broker

Ken Spencer — 2013-03-01T23:41:23+01:00

SQL Server Service Broker is a cool technology for creating asynchronous applications and many other users. Its pretty slick in that it can be used with TSQL, .Net or a combination of the two.

Its always interesting troubleshooting applications though. Here's a link to some interesting things about Service Broker troubleshooting.

Data Modeling Essentials Seminar at DevWeek 2013

Dejan Sarka — 2013-02-25T11:54:24+01:00

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

Android apps on Windows 8

Ken Spencer — 2013-02-12T19:55:32+01:00

Nice.

Check this out:

http://mashable.com/2013/02/12/bluestacks-windows-8/

Now you can run Android apps on Windows 8. Pretty slick.

Windows 8 is really taking off. Lines out the door at lots of stores.

PASS SQL Saturday #198 Vancouver, BC

SolidQ — 2013-02-11T20:00:00+01:00

Hello! Here’s another interesting SQL Saturday for you! On February 16th in Vancouver Canada. This event is going to be held at British Columbia's Institute of Technology.

Our colleagues Paul and Mark will be there to speak about Visualization Choices and Applied Enterprise Semantic Mining. Here’s some info about their sessions:

Paul Turley - Visualization Choices: What, When, and How?

When do you use a sparkline, column, or line chart? How do you create a business scorecard – and with what tool? Using Excel, Power View, and Report Builder, this session will guide you through the choices and help you apply the right visual to your business data. Come see what well-known industry experts have to say about the correct use of visualization choices and techniques, colors, backgrounds, borders, and 3-D effects. Then see demonstrations of how to create those rich visuals in the different reporting tools, used with PowerPivot and tabular semantic models.

Mark Tabladillo - Applied Enterprise Semantic Mining

Text mining is projected to dominate data mining, and the reasons are evident: we have more text available than numeric data. Microsoft introduced a new technology to SQL Server 2012 called Semantic Search. This session's detailed description and demos give you important information for the enterprise implementation of Tag Index and Document Similarity Index. The demos include a web-based Silverlight application, and content documents from Wikipedia. We'll also look at strategy tips for how to best leverage the new semantic technology with existing Microsoft data mining.

Admittance to the event is free thanks to our wonderful sponsors. There is a small, $10 lunch fee that will cover an excellent meal catered by BCIT exclusively for this event.

Don´t miss out, register today.

Contingency Tables in SQL Server 2012

Dejan Sarka — 2013-02-05T10:42:23+01:00

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) &