Chris Webb's BI Blog - Why Linq to MDX isn't worth the ROI

In my opinion, MDX is a complex query language that isn't intuitive, and most people I work with cringe when they need to write an MDX query.  Performance is unpredictable and it is really easy to shoot yourself in the foot.  Service packs with Analysis Services make a difference in both performance and results of a query.

If you disagree with the above statement, see Mosha's latest entry on Subselects, SCOPEs, and hierarchy navigation MDX functions.

With standardized T-SQL, and the query plan generator or plan explanation tools in modern databases, it is much easier to build and adjust SQL code for performance and maintainability.  SQL, though, doesn't have the time intelligence and other BI features that make Analysis Services shine.  Sure, you can hack them in there, but relational databases aren't really designed to answer the same questions as cubes.

Code developers seem to be of a different mindset than SQL DBAs.   Rather than using stored procedures, many people unfamiliar with databases prefer to use the 'dynamic SQL' approach that is causing so much of the SQL injection problems of today.  Maintaining classes with SQL strings is about as much fun as writing MDX.  If you're going to do something like that, at least put them outside your compiled assemblies.

So what's the solution for both of these?  SQL has tons of tools that remove the dynamic SQL approach from the hands of developers and put it into automated ORM generation and CRUD generation frameworks.  Tools for offline code generation, like MyGeneration and CodeSmith.  Tools for "on-the-fly" generation, like NHibernate, and LINQ.  What does MDX have?

Excel.

And LINQ to MDX still looks like it's pretty far off.

Seeing that Marco Russo has released his book "Programming Microsoft LINQ" reminded me of a conversation I had with him a while ago about something I've heard various people ask about over the last year - will there be a LINQ to MDX?

Chris Webb's BI Blog

What happened to Natural Language Query? Training queries so that Business Analysts could access their data with "friendly" terminology seemed like a good idea at the time...

Unfortunately, (or fortunately, depending on if you were the one doing the vocabulary training) it went the way of the Dodo.

This blog posting just about sums it up.

Even if I could talk to my computer (an idea that's never particularly appealed to me, this Mac is supposed to be able to do it but I've never turned it on), would I want to speak to it in full sentences stuffed with subordinate clauses and prepositional phrases? I think I'd want to grunt things like “Yahoo, Berlin weather” or “break line 238” or “spam!”.

http://www.tbray.org/ongoing/When/200x/2003/05/16/NLQuery

Grunting out a LINQ select statement.

public void Linq7() {
    List products = GetProductList();
    var productNames =
        from p in products
        select p.ProductName;
    Console.WriteLine("Product Names:");
    foreach (var productName in productNames) {
        Console.WriteLine(productName);
    }
}

Grunting out a SQL statement.

SELECT * FROM Product

Grunting out an MDX Statement

SELECT [Product].[Product].members on 0

FROM $Product

Grunting out an MS Access select statement.

Double-click on the table dummy!

I still prefer the SQL approach, since double-click doesn't seem to scale as well. :)

How about Dynamic SQL in Cobol?  It's all Portugese to me...

         1         2         3         4         5         6         7   
123456789012345678901234567890123456789012345678901234567890123456789012

000001*----------------- I N I C I O   D O   C O D I G O --------------*
000002 IDENTIFICATION DIVISION.                                         
000003*-----------------------
000004 PROGRAM-ID.     SELECT01.                                         
000005
000006* Sistema      : EXEMPLO                      
000007* Programa     : SELECT01                                           
000008* Objetivo     : Listar os dados da CONTA corrente    
000009* Analista     : CARLOS ALBERTO DORNELLES                                                   
000010* Desenvolvedor: CARLOS ALBERTO DORNELLES                           
000011* Data         : 31/12/2002                                         
000012* Linguagem    : COBOL / DB2 / CICS                                 
000013* Manutencoes  :                                                    
000014*----------------------------------------------------------------*
000015* Desenvolvedor              Responsavel                   Data   
000016* -------------              -----------                   ---- 
000017*
000018* xxxxxxxxxxxxxxxxxxxxxxxxx  xxxxxxxxxxxxxxxxxxxxxxxxxx xx/xx/xxxx
000019* descrição xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
000020*----------------------------------------------------------------*
000000
000021 ENVIRONMENT DIVISION.                                            
000022*---------------------
000023 CONFIGURATION SECTION.                                           
000024*---------------------
000025 SPECIAL-NAMES. 
000026     DECIMAL-POINT IS COMMA.                           
000027
000028 DATA DIVISION.                                                   
000029*-------------
000030 WORKING-STORAGE SECTION.                                         
000031*-----------------------
000032 77  WS-SQLCODE-EDT              PIC  ----9.                      
000033 01  AREAS-DE-TRABALHO.                                           
000034     03 WS-TAM                   PIC  9(005).                     
000039 01  WS-VARIAVEIS-DB2.                                            
000040     03 WS-GR-CURSOR.                                             
000041        49 WS-ID-TAMANHO-CURSOR  PIC S9(004) COMP   VALUE ZEROES. 
000042        49 WS-DE-CURSOR          PIC  X(2000)       VALUE SPACES. 
000035 01  WS-AREA-ENTRADA.                                               
000039        05 PRM-NU-CONTA-E        PIC  9(004).                     
000035 01  WS-AREA-SAIDA.                                               
000036     03 PRM-QTDE-CONTA           PIC  9(004).                     
000037     03 PRM-ARRAY-SAIDA          OCCURS 1 TO 100 TIMES            
000038                                 DEPENDING ON PRM-QTDE-CONTA.                  
000039        05 PRM-NU-CONTA          PIC  9(004).                     
000040        05 PRM-NO-CONTA          PIC  X(040).                     
000040        05 PRM-NO-ENDERECO       PIC  X(070).                     
000040        05 PRM-NO-CIDADE         PIC  X(050).                     
000039 01  WS-AREA-ERROS.                                               
000040     03 PRM-QTDE-ERROS           PIC  9(003).                     
000041     03 PRM-ARRAY-ERROS          OCCURS 1 TO 094 TIMES            
000042                                 DEPENDING ON PRM-QTDE-ERROS.           
000043        05 PRM-NUMERO-MENSAGEM   PIC  X(004).                     
000044        05 PRM-PROGRAMA          PIC  X(008).                     
000045        05 PRM-INFORMACOES       PIC  X(200).   
000046
000047*              Definicao de tabelas e areas na DCLGEN            *
000048*----------------------------------------------------------------*
000049     EXEC SQL INCLUDE SQLCA      END-EXEC.                        
000050     EXEC SQL INCLUDE TABELA01   END-EXEC. 
000051
000052 LINKAGE SECTION.                                                 
000053*---------------
000054 01  DFHCOMMAREA.                                                 
000055     03 LKS-EXCECAO.                                               
000056        05 LKS-ERRO-CICS         PIC  9(003).                     
000057        05 LKS-NU-MENSAGEM       PIC  9(004).                     
000058        05 LKS-NO-MENSAGEM       PIC  X(078).                     
000059        05 LKS-NU-SQLCODE        PIC  9(004).                     
000060     03 LKS-IDENTIFICACAO.                                         
000061        05 LKS-IN-NOME-PGM       PIC  X(008).                     
000062        05 LKS-IN-CO-USUARIO     PIC  X(008).                     
000063        05 LKS-IN-CO-FUNCAO      PIC  X(002).                     
000064     03 LKS-ENTRADA-SAIDA.                                         
000065        05 LKS-CONTEUDO-TAM      PIC  9(005).                     
000066        05 LKS-CONTEUDO.                                           
000067           07 FILLER             PIC  X(001) OCCURS 1 TO 20000    
000068                                 DEPENDING ON LKS-CONTEUDO-TAM.    
000069
000070 PROCEDURE DIVISION USING DFHCOMMAREA.                            
000071*------------------------------------
000000
000072     PERFORM R000-PROCED-INICIAIS      THRU R000-FIM.                
000073     PERFORM R100-PROCED-PRINCIPAIS    THRU R100-FIM.                
000074     PERFORM R999-PROCEDIMENTOS-FINAIS THRU P999-FIM.                
000075
000076 R000-PROCED-INICIAIS.                                            
000077*--------------------
000000
000105     MOVE LK-CONTEUDO(1:LK-CONTEUDO-TAM) TO WS-AREA-ENTRADA.      
000078     INITIALIZE     LKS-EXCECAO.                
000079     MOVE SPACES TO LKS-CONTEUDO(1:20000).      
000080     MOVE ZEROES TO LKS-CONTEUDO-TAM            
000081                    PRM-QTDE-CONTA           
000082                    PRM-QTDE-ERROS.            
000083 R000-FIM.                                                        
000084     EXIT.                                                        
000085
000086 R100-PROCED-PRINCIPAIS.                                          
000087*----------------------
000000
000088     PERFORM R200-ABRE-CONTA THRU R200-FIM.             
000089     PERFORM R210-LE-CONTA   THRU R210-FIM.             
000090     IF SQLCODE EQUAL +100                                            
000091        MOVE 1       TO LKS-ERRO-CICS                       
000092        ADD  1       TO PRM-QTDE-ERROS                     
000093        MOVE SPACES  TO PRM-INFORMACOES (PRM-QTDE-ERROS)
000094        MOVE SQLCODE TO LKS-NU-SQLCODE                      
000095        MOVE SQLCODE TO WS-SQLCODE-EDT                      
000096        STRING 'Nenhum registro encontrado na tabela TABELA01.'                  
000102                     DELIMITED BY SIZE                                
000103                               INTO PRM-INFORMACOES (PRM-QTDE-ERROS)
              END-STRING
000104        MOVE '0001'     TO PRM-NUMERO-MENSAGEM (PRM-QTDE-ERROS)
000105        MOVE 'SELECT01' TO PRM-PROGRAMA (PRM-QTDE-ERROS)
000106        PERFORM R999-PROCEDIMENTOS-FINAIS                                
000107     END-IF.                                                      
000108     PERFORM UNTIL SQLCODE = +100                                 
000109             PERFORM R220-MONTA-CONTA THRU R220-FIM              
000110             PERFORM R210-LE-CONTA    THRU R210-FIM              
000111             IF PRM-QTDE-CONTA = 100                                 
000112                MOVE +100 TO SQLCODE                                   
000113             END-IF                                                    
000114     END-PERFORM.                                                 
000115     PERFORM R230-FECHA-CONTA THRU R230-FIM.             
000116     MOVE LENGTH OF WS-AREA-SAIDA TO LKS-CONTEUDO-TAM.                                
000118     MOVE WS-AREA-SAIDA TO LKS-CONTEUDO (1:LKS-CONTEUDO-TAM).                
000119                                    
000120 R100-FIM.                                                        
000121     EXIT.                                                        
000122
000123 R200-ABRE-CONTA.                                               
000124*---------------
000000
000136     INITIALIZE WS-GR-CURSOR.                                    
000137     MOVE 1 TO WS-ID-TAMANHO-CURSOR. 
000000     MOVE PRM-NU-CONTA-E TO NU-CONTA.                            
000138                                                                         
000139     STRING                                                      
000140        'SELECT NU_CONTA , NO_CONTA , NO_ENDERECO , NO_CIDADE '
000000        'FROM DCL.TABELA01_CONTA '                 
000141                DELIMITED BY SIZE INTO WS-DE-CURSOR                
000142                WITH POINTER WS-ID-TAMANHO-CURSOR
000000     END-STRING.
000000
000000     IF NU-CONTA NOT EQUAL ZEROES
000139        STRING                                                      
000140           'WHERE NU_CONTA >= ' :NU-CONTA 
000141                   DELIMITED BY SIZE INTO WS-DE-CURSOR                
000142                   WITH POINTER WS-ID-TAMANHO-CURSOR
000000        END-STRING
000000     END-IF.
000000       
000225     EXEC SQL                                                     
000226          PREPARE CONSULTA FROM :WS-GR-CURSOR                         
000227     END-EXEC.                                                     
000228                                                                         
000229     IF SQLCODE NOT EQUAL +0                                          
000230        MOVE 1 TO LK-ERRO-CICS                                    
000231        MOVE SQLCODE TO LK-NU-SQLCODE                             
000232        PERFORM R999-PROCEDIMENTOS-FINAIS                                
000233     END-IF.                                                       
000234                                                                         
000235     EXEC SQL                                                     
000236          DECLARE CUR001 CURSOR FOR CONSULTA                          
000237     END-EXEC.                                                    
000238                                                                         
000239     IF SQLCODE NOT EQUAL +0                                          
000240        MOVE 1 TO LK-ERRO-CICS                                    
000241        MOVE SQLCODE TO LK-NU-SQLCODE                             
000242        PERFORM R999-PROCEDIMENTOS-FINAIS                                
000243     END-IF                                                       
000244                                                                         
000245     EXEC SQL                                                     
000246          OPEN CUR001                                                 
000247     END-EXEC.                                                    
000248                                                                         
000133     IF SQLCODE NOT EQUAL +0                                          
000134        MOVE 1       TO LKS-ERRO-CICS                       
000135        ADD  1       TO PRM-QTDE-ERROS                     
000136        MOVE SPACES  TO PRM-INFORMACOES (PRM-QTDE-ERROS)
000137        MOVE SQLCODE TO LKS-NU-SQLCODE                      
000138        MOVE SQLCODE TO WS-SQLCODE-EDT                      
000139        STRING 'Erro de acesso a base de dados. SQLCODE: '                  
000140                WS-SQLCODE-EDT ' ErrMc: ' SQLERRMC                                      
000143               ' - Tabela utilizada -> TABELA01'                        
000145                DELIMITED BY SIZE                                
000146                          INTO PRM-INFORMACOES (PRM-QTDE-ERROS)
000147                MOVE '0001'     TO PRM-NUMERO-MENSAGEM (PRM-QTDE-ERROS)
000148                MOVE 'SELECT01' TO PRM-PROGRAMA (PRM-QTDE-ERROS)
000149                PERFORM R999-PROCEDIMENTOS-FINAIS   
              END-STRING                             
000150     END-IF.
000000                                                      
000151 R200-FIM.                                                        
000152     EXIT.                                                        
000153
000154 R210-LE-CONTA.                                                 
000155*-------------
000000
000156     EXEC SQL                                                     
000157          FETCH  CUR001                                                    
000158          INTO  :NU-CONTA                                              
000159          ,     :NO-CONTA                                              
000159          ,     :NO-ENDERECO                                           
000159          ,     :NO-CIDADE                                             
000160     END-EXEC.  				  
000000                                                  
000161     IF SQLCODE NOT EQUAL +0 AND +100                                 
000162        MOVE 1       TO LKS-ERRO-CICS                       
000163        ADD  1       TO PRM-QTDE-ERROS                     
000164        MOVE SPACES  TO PRM-INFORMACOES (PRM-QTDE-ERROS)
000165        MOVE SQLCODE TO LKS-NU-SQLCODE                      
000166        MOVE SQLCODE TO WS-SQLCODE-EDT                      
000167        STRING 'Erro de acesso a base. SQLCODE: '                  
000168                WS-SQLCODE-EDT ' ErrMc: ' SQLERRMC                                     
000171               ' - Tabela utilizada -> TABELA01'                          
000173                DELIMITED BY SIZE                                
000174                          INTO PRM-INFORMACOES (PRM-QTDE-ERROS)
              END-STRING
000175        MOVE '0002'     TO PRM-NUMERO-MENSAGEM (PRM-QTDE-ERROS)
000176        MOVE 'SELECT01' TO PRM-PROGRAMA (PRM-QTDE-ERROS)
000177        PERFORM R230-FECHA-CONTA THRU R230-FIM               
000178        PERFORM R999-PROCEDIMENTOS-FINAIS                                
000179     END-IF.  
000000                                                    
000180 R210-FIM.                                                        
000181     EXIT.                                                        
000182
000183 R220-MONTA-CONTA.                                              
000184*----------------
000000
000185     ADD  1 TO PRM-QTDE-CONTA.                                  
000186     MOVE NU-CONTA    TO PRM-NU-CONTA    (PRM-QTDE-CONTA).        
000187     MOVE NO-CONTA    TO PRM-NO-CONTA    (PRM-QTDE-CONTA). 
000186     MOVE NO-ENDERECO TO PRM-NO-ENDERECO (PRM-QTDE-CONTA).        
000187     MOVE NO-CIDADE   TO PRM-NO-CIDADE   (PRM-QTDE-CONTA). 
000000
000188 R220-FIM.                                                        
000189     EXIT.                                                        
000190
000191 R230-FECHA-CONTA.                                              
000192*----------------
000000
000193     EXEC SQL                                                     
000194          CLOSE CUR001                                                
000195     END-EXEC.   			 
000000                                                 
000196 R230-FIM.                                                        
000197     EXIT.                                                        
000198
000199 R999-PROCEDIMENTOS-FINAIS.                                              
000200*-------------------------
000000
000201     IF LKS-ERRO-CICS = 1                                          
000202        MOVE LENGTH OF WS-AREA-ERROS TO LKS-CONTEUDO-TAM           
000203        MOVE WS-AREA-ERROS TO LKS-CONTEUDO (1:LKS-CONTEUDO-TAM)              
000205     END-IF.                                                      
000206     EXEC CICS                                                    
000207          RETURN                                                    
000208     END-EXEC.  
000000                                                  
000209 P999-FIM.                                                        
000210     EXIT.                                                        
000211*----------------- F I M   D O   C O D I G O --------------------*

Microsoft Dynamics CRM UK Blog : Resizing A Virtual PC Hard Drive

 

Resizing A Virtual PC Hard Drive

Holy Diver...

The other day I was working on one of my Virtual PC demo images, when I noticed I was running out of disk space. When I originally created the Virtual Hard Drive (.VHD) I just accepted the default size of 16GB without thinking, but more and more often I find I need to install other products such as SharePoint Server, BizTalk Server, PerformancePoint Server, which inevitably require extra disk space.

Microsoft Dynamics CRM UK Blog : Resizing A Virtual PC Hard Drive

SQL Excel freeware add-in - Home

SQL Excel is just that, a simpler and easier way to get SQL results into Excel directly from SQL.

Seems really promising...

May 21st - new much improved version is available (compatible and tested on Excel 2000, 2002/XP, 2003 and 2007).    This is an Excel for Windows add-in so it wont work on a mac

SQL Excel freeware add-in - Home

Windows XP (SP2): 3 ways to optimize performance with NTFS

Could be useful for optimizing SQL bootup times.  Note that a large number of databases will also increase bootup times... be sure to detach databases not in use and keep sizes as small as possible.

Microsoft Bootvis is a good tool for troubleshooting bootup times.

Resize the Master File Table and prevent fragmentation
The Master File Table (MFT) contains specific information about each folder and file on your hard disk (such as date of creation, parameters etc.). To prevent fragmentation, the NTFS reserves exactly 12.5% of your hard disk capacity for the MFT. However, when your free disk space is low, XP writes those files directly into the MFT. Additionally, small files are automatically stored in the MFT. Both factors lead to a certain degree of fragmentation which is why we recommend to increase the MFT:

Windows XP (SP2): 3 ways to optimize performance with NTFS

Quick and Dirty SSIS Variable Export

If you have a lot of variables in your SSIS package and you want to document the contents, one way is to do this:

1. Create a package configuration

2. Enter a name for the config file (variables.xml)

3. Select the variables to include.

4. Click OK to create the config file.

This XML can then be opened in Excel 2007 as an XML table.

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays - SQL Server Central

Another few uses for the numbers table, and a new SQL 2005 CTE example without a numbers table.
http://www.sqlservercentral.com/articles/T-SQL/63003/

SSIS Trick - Setting multiple variables at once

There are custom components out there that set a single variable for you.  You can also set variables using script tasks. 

One way I found to set multiple variables in a package at the same time is to execute a 'fake' sql command. 

There is a tiny performance hit, however this could also be considered a way to include 'profiling' of your app variables with SQL Profiler.

1. Drag a SQL Command Task into the package.
2. Set the command connection.  Set the command text to SELECT 'Myvalue' as MyVariableValue, 'myvalue2' as My2ndVariableValue

Notice no FROM statement.  This also works for other databases, using DUMMY tables.
3. Set the resultset to 'Single Resultset'
4. Set the Results tab to map the variables to the select statement results.

Test Run: Testing SQL Stored Procedures Using LINQ

Using LINQ to create unit tests for SQL.

Test Run

Testing SQL Stored Procedures Using LINQ

Dr. James McCaffrey

Test Run: Testing SQL Stored Procedures Using LINQ

Kimberly L. Tripp: Improving *my* SQL skills through your questions! http://www.SQLskills.com/blogs/kimberly

 

If you add more than 1GB then you'll add 16VLFs. In general, most transaction logs will only have 20 or 30 VLFs - even 50 could be reasonable depending on the total size of the transaction log. However, in many cases what happens is that excessive autogrowths can cause an excessive number of VLFs to be added - sometimes resulting in hundreds of VLFs. Having an excessive number of VLFs can negatively impact all transaction log related activities and you may even see degradation in performance when transaction log backups occur. To see how many VLFs you have solely look at the number of rows returned by DBCC LOGINFO. The number of rows returned equals the number of VLFs your transaction log file has. If you have more than 50, I would recommend fixing it and adjusting your autogrowth so that it doesn't occur as fequently. To get rid of all of the execessive VLFs, follow these easy steps to shrink off the fragmented chunk and add a new, clean chunk to your transaction log:

Kimberly L. Tripp: Improving *my* SQL skills through your questions! http://www.SQLskills.com/blogs/kimberly

Andy Leonard : SSIS Design Pattern - Incremental Loads

Incremental loads without a CDC tool.

SSIS Design Pattern - Incremental Loads

Introduction

Loading data from a data source to SQL Server is a common task. It's used in Data Warehousing, but increasingly data is being staged in SQL Server for non-Business-Intelligence purposes.

Andy Leonard : SSIS Design Pattern - Incremental Loads

Steve Fibich : Passing a values back from a child package to a parent package in SSIS

 

I found this process very useful to pass metadata about versions of data imported into a master data management system from a child package back to a parent package.  You can pass any data types from a child to a parent using this method.  Normally I put the script that passes the value from the child to parent package in the post execute event handler.  The only reason I put the task in the event handler is for style, as I feel it’s better to put this variable handling code separate from the specific package logic itself.

Steve Fibich : Passing a values back from a child package to a parent package in SSIS

Andy Leonard : SSIS Design Pattern - Read a DataSet From Variable In a Script Task

Andy has more SSIS goodness.

This script uses an OLEDbDataAdapter (oleDA) to fill a DataTable (dt) with the contents of the dsVar SSIS package variable, then iterates each row and column to build a string containing the data in the row. It then pops up a messagebox for each row displaying the row's contents before moving to the next row.

Andy Leonard : SSIS Design Pattern - Read a DataSet From Variable In a Script Task

Andy Leonard : Introducing Change Data Capture, SSIS, and SQL Server 2008 CTP5 (Nov 2007)

I'm currently working with an AS/400-based CDC system.  MS has jumped into the pond with built-in CDC in 2008.

Introducing Change Data Capture, SSIS, and SQL Server 2008 CTP5 (Nov 2007)

Introduction

On Thursday, 24 Jan 2008, I presented New Features In SSIS 2008 to the Richmond SQL Server Users Group.

Most of the presentation was dedicated to demonstrating Change Data Capture (CDC) interacting with SQL Server 2008 Integration Services. I started seriously working on this demo the first week of January, thinking I'd put 2 - 6 hours into it to get it running using the detailed instructions in Books Online. Things were going relatively well working through the demo until I hit calls from SSIS to table-valued functions created by CDC.

Andy Leonard : Introducing Change Data Capture, SSIS, and SQL Server 2008 CTP5 (Nov 2007)

Multiple NULL values in a Unique index in SQL Server/DB2 LUW « Systems Engineering and RDBMS

 

Yesterday, when helping out a friend who was working on a project that required porting an application from Oracle v9.2.0.5 to SQL Server 2005, he ran into the same UNIQUE index issue as we had blogged before. Since that was a major requirement by the client, this project needed to support having multiple NULL values in the column and still have a UNIQUE constraint. That is allowed by Oracle but not in SQL Server and DB2 LUW. There is a way to make this work in SQL Server and DB2 LUW also but that requires a work-around. Consider this table:

Multiple NULL values in a Unique index in SQL Server/DB2 LUW « Systems Engineering and RDBMS