Parameters in query

Mar 12, 2014 at 7:52 PM
Edited Mar 12, 2014 at 8:26 PM
Hi,

I'm having some problems using parameters in my queries. I have a test looking a little like this:
<test name="CompareQueries" ignore="false">
    <system-under-test>
        <execution>
            <query file="Queries\MdxQuery.mdx" connectionString="@SsasDB">
                <parameter name="DateMember">
                    <![CDATA[[DateDim].[Date].&[20130101]]]>
                </parameter>
            </query>
        </execution>
    </system-under-test>
    <assert>
        <equalTo>
            <column index="0" role="key" type="text"/>
            <column index="1" role="value" type="numeric"/>
            <query file="Queries\SqlQuery.sql" connectionString="@DataMartDB">
                <parameter name="DateInt" sql-type="Int">
                    20130101
                </parameter>
            </query>
        </equalTo>
    </assert>
</test>
An MDX query looking like this:
SELECT
    {
        [Measures].[SomeMeasure]
    } ON 0
    ,{
        [SomeOtherDim].[SomeAttribute].CHILDREN
    } ON 1
FROM
    [MyCube]
WHERE
    (
        STRTOMEMBER(@DateMember)
    )
The equivalent SQL looks like this:
SELECT
    [SomeOtherDim].[SomeAttribute]
    ,SUM([SomeMeasure]) AS [SomeMeasure]
FROM
    DataMartTable
WHERE
    DataMartTable.Date = @DateInt
GROUP BY
    [SomeOtherDim].[SomeAttribute]
When I run this test I get the following error:
NBi.NUnit.Runtime.TestSuite.CompareQueries:
NBi.NUnit.Runtime.CustomStackTraceErrorException : Parser: The query contains the DateMember parameter, which is not declared.
The connection string used was '[connectionstring for SsasDB]'
When I remove the parameters in the MDX script I get another error concerning the SQL script:
NBi.NUnit.Runtime.TestSuite.CompareQueries:
System.Data.SqlClient.SqlException : Must declare the scalar variable "@DateInt".
Am I missing something obvious? Everything works fine without the parameters.

Beside the parameter stuff I starting to get the hang of NBi and NUnit. :-)
Coordinator
Mar 13, 2014 at 9:15 AM
Edited Mar 13, 2014 at 9:15 AM
Everything sounds correct so it should work :-)

Just two questions:
  • Which version of NBi are you using (1.3 or 1.4)?
  • Are you executing your queries on Microsoft SQL Server and MS SSAS (not Oracle or Mysql)?
If the answer are positives, I guess that NBi has a bug somewhere but without a debugging session, I'm not able to spot it. I'll check this tonight (CET). As far as I remember, I've built several acceptance and integration tests on this feature so it should be something really odd.

If you're blocked and are looking for a quick solution, I'd tried to put the query directly in the xml and not in an external file but it's really doubtful that it will solve your issue.
Mar 13, 2014 at 11:52 AM
I'm using NBi v.1.4.0.3 and NUnit v.2.6.3.13283. My servers are SQL 2008 R2.

I have also tried putting the query directly in the XML without any luck. Without the parameters the queries works fine, both as external scripts and embedded in the XML.

Could it be my connection strings that are the reason? I have tried different settings for both connections. Are there any specific settings that have to be included in the connection string aside from server name and database name? You mention that parameters only works for specific providers:
Note that this feature is only available if you're using System.Data.SqlClient or System.Data.AdomdClient.
Coordinator
Mar 13, 2014 at 2:44 PM
From what I see in your first post,
NBi.NUnit.Runtime.TestSuite.CompareQueries:
System.Data.SqlClient.SqlException : Must declare the scalar variable "@DateInt".
you're using the correct connection string to connect to your sql database. It's more difficult to check this for your analysis service but the error message clearly states that the software has understood that you've a parameter in your query. So I guess that you're using the correct connection-strings.

In the zip file containing the binaries, you've a folder named "Samples", inside, you've a nbits file named "QueryEqualToResultSet.nbits". In this file, you've a test named "SQL with Parameters", could you try to copy/paste this test in your test-suite and run it (The targetted SQL database is opened on internet so you don't need to install any database on your server or change the connection string). Normally all these tests are run (and successful) during a build, so I really expect that this test will be green on your environement.
Coordinator
Mar 13, 2014 at 6:25 PM
Ok good news, I'm able to reproduce this behaviour :-)

The problem sounds specific to a test with two queries ... still need to check but will open an issue for this.
Mar 13, 2014 at 8:56 PM
I have no problems running the test "SQL With Parameters" from the "QueryEqualToResultSet.nbits" sample as expected.

Good news for me at least. It means I'm not alone with this issue. :-)
Coordinator
Mar 13, 2014 at 10:59 PM
I've fixed this issue with in the new release 1.4.1
Mar 14, 2014 at 9:51 AM
The 1.4.1.6 version seems to have fixed the MDX query isue. The SQL query does however seem to have some weird behavior. I have three parameters (@StatusType, @FromDateInt and @ToDateInt). In the profiler the query looks like this:
EXEC SP_EXECUTESQL N'SELECT
    [SomeOtherDim].[SomeAttribute]
    ,SUM([SomeMeasure]) AS [SomeMeasure]
FROM
    DataMartTable
WHERE
    DataMartTable.Date BETWEEN @FromDateInt AND @ToDateInt
    AND DataMartTable.StatusType = @StatusType
GROUP BY
    [SomeOtherDim].[SomeAttribute]
',N'@StatusType varchar(20),@FromDateInt int,@ToDateInt int',@StatusType='
                            Closed
        ',@FromDateInt=20121001,@ToDateInt=20131001
According to NBi the result set is empty which it should not be (it isn't). It seems to me that something is misplaced in the query before it is sent to the server.
Mar 14, 2014 at 10:45 AM
Forget my previous post. The SQL query seems to work fine, it's my MDX query that returns an empty set. Will test some more to see if it's a human or machine related error. :-)
Coordinator
Mar 14, 2014 at 11:51 AM
It's never a machine error, it's always a human as the root cause :-)

If you want to see more samples of parameters working with NBi, you can check in the folder samples, I've added new test-suite named QueryWithParameters.nbits . All these samples are part of the acceptance tests and are automatically executed when I build a release so I'm 100% sure that they are effectively working.
Mar 14, 2014 at 1:48 PM
Good point. :-)

I have investigated some more. I have tried any combination I can think of using MDX, SQL and with/without parameters. Below is the results of my tests. I have not tested whether some of them are false positives (where both sets are empty).
  • Compare MDX with MDX (parameter in both) -> OK
  • Compare MDX with MDX (parameter in assert) -> OK
  • Compare MDX with MDX (parameter in test system) -> OK
  • Compare MDX with SQL (parameter in both) -> Failed (MDX return empty set)
  • Compare MDX with SQL (parameter in assert) -> Failed (MDX return empty set)
  • Compare MDX with SQL (parameter in test system) -> OK
  • Compare MDX with SQL (no parameter) -> OK
  • Compare SQL with MDX (parameter in both) -> Failed (MDX return empty set)
  • Compare SQL with MDX (parameter in assert) -> OK
  • Compare SQL with MDX (parameter in test system) -> Failed (MDX return empty set)
  • Compare SQL with MDX (no parameter) -> OK
  • Compare SQL with SQL (parameter in both) -> OK
  • Compare SQL with SQL (parameter in assert) -> Failed (SQL test system return empty set)
  • Compare SQL with SQL (parameter in test system) -> Failed (SQL assert return empty set)
Coordinator
Mar 17, 2014 at 7:22 PM
I'm sorry for you but the problem is not in NBi but in your queries.

I've checked all the cases that you've submitted but all them are green:
<test name="Compare MDX with SQL (parameter in both)">
        <system-under-test>
            <execution>
                <query connectionString="@AsAdventureWorks2012">
                    select [Measures].[Customer Count] on 0, strToMember(@YearMember) on 1 from [Adventure Works]
                    <parameter name="@YearMember">[Date].[Calendar Year].[CY 2006]</parameter>
                </query>                
            </execution>
        </system-under-test>
        <assert>
            <equalTo>
                <query connectionString="@SqlAdventureWorks2012">
                    select @YearName, @ValueCount
                    <parameter name="@YearName">CY 2006</parameter>
                    <parameter name="@ValueCount" sql-type="Int">2677</parameter>
                </query>
            </equalTo>
        </assert>
    </test>
    <test name="Compare MDX with SQL (parameter in assert) ">
        <system-under-test>
            <execution>
                <query connectionString="@AsAdventureWorks2012">
                    select [Measures].[Customer Count] on 0, [Date].[Calendar Year].[CY 2006] on 1 from [Adventure Works]
                </query>                
            </execution>
        </system-under-test>
        <assert>
            <equalTo>
                <query connectionString="@SqlAdventureWorks2012">
                    select @YearName, @ValueCount
                    <parameter name="@YearName">CY 2006</parameter>
                    <parameter name="@ValueCount" sql-type="Int">2677</parameter>
                </query>
            </equalTo>
        </assert>
    </test>
    <test name="Compare SQL with MDX (parameter in both)">
        <system-under-test>
            <execution>
                <query connectionString="@SqlAdventureWorks2012">
                    select @YearName, @ValueCount
                    <parameter name="@YearName">CY 2006</parameter>
                    <parameter name="@ValueCount" sql-type="Int">2677</parameter>
                </query>            
            </execution>
        </system-under-test>
        <assert>
            <equalTo>
                <query connectionString="@AsAdventureWorks2012">
                    select [Measures].[Customer Count] on 0, strToMember(@YearMember) on 1 from [Adventure Works]
                    <parameter name="@YearMember">[Date].[Calendar Year].[CY 2006]</parameter>
                </query>
            </equalTo>
        </assert>
    </test>
    <test name="Compare SQL with MDX (parameter in test system)">
        <system-under-test>
            <execution>
                <query connectionString="@SqlAdventureWorks2012">
                    select @YearName, @ValueCount
                    <parameter name="@YearName">CY 2006</parameter>
                    <parameter name="@ValueCount" sql-type="Int">2677</parameter>
                </query>            
            </execution>
        </system-under-test>
        <assert>
            <equalTo>
                <query connectionString="@AsAdventureWorks2012">
                    select [Measures].[Customer Count] on 0, [Date].[Calendar Year].[CY 2006] on 1 from [Adventure Works]
                </query>
            </equalTo>
        </assert>
    </test>
    <test name="Compare SQL with SQL (parameter in assert)">
        <system-under-test>
            <execution>
                <query connectionString="@SqlAdventureWorks2012">
                    select 'CY 2006', 2677
                </query>            
            </execution>
        </system-under-test>
        <assert>
            <equalTo>
                <query connectionString="@SqlAdventureWorks2012">
                    select @YearName, @ValueCount
                    <parameter name="@YearName">CY 2006</parameter>
                    <parameter name="@ValueCount" sql-type="Int">2677</parameter>
                </query>
            </equalTo>
        </assert>
    </test>
    <test name="Compare SQL with SQL (parameter in test system)">
        <system-under-test>
            <execution>
                <query connectionString="@SqlAdventureWorks2012">
                    select @YearName, @ValueCount
                    <parameter name="@YearName">CY 2006</parameter>
                    <parameter name="@ValueCount" sql-type="Int">2677</parameter>
                </query>            
            </execution>
        </system-under-test>
        <assert>
            <equalTo>
                <query connectionString="@SqlAdventureWorks2012">
                    select 'CY 2006', 2677
                </query>
            </equalTo>
        </assert>
    </test>
More, your test failures don't match with the way NBi is implemented. I could have a bug at two places; the engine executing the query not dealing with parameters (it was the case for AdoMD in version 1.4) and the second place is the deserialisation of the xml into an effective IDbParameter (it was the case for some assertions in version 1.4). But in no way the assertion and the system-under-test can influence each other. So if parameter is working for system-under-test in "Compare MDX with MDX (parameter in both)", it will be the case for the system-under-test in "Compare MDX with SQL (parameter in both)". But your report says that this resultset is empty. If your two queries are the same, it's just impossible that they don't give the same results when executed.

AFAIK, an empty result for an MDX query is not uncommon if your parameters are not correct (and it could be tricky to check this).
Marked as answer by MrLynch on 3/18/2014 at 8:25 AM
Mar 18, 2014 at 3:25 PM
Thanks for spending time helping me getting this right.

I have finally found the combinations of (human) errors, that led the test cases to fail. Other than the typical "I-do-not-need-to-read-the-entire-error-message" and stupid typos the most important learning point was that SQL queries do not like [new line] in text parameters, e.g.:
<parameter name="@StatusType" >
    Closed
</parameter>
whereas this works:
<parameter name="@StatusType" >Closed</parameter>
The query simply resturns an empty set, when there is a [new line] in the parameter. It does however works when the sql-type is Int.

I never thought my passion for indentation and short code lines would give me this much trouble. :-)
Coordinator
Mar 18, 2014 at 4:23 PM
Huuuum, interesting remark about the [new line] and [tab] for the parameter. I'll try to override this behaviour in 1.5
Coordinator
Mar 18, 2014 at 10:22 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.