search

Seddryck / NBi

NBi is a testing framework (add-on to NUnit) for Business Intelligence and Data Access. The main goal of this framework is to let users create tests with a declarative approach based on an Xml syntax. By the means of NBi, you don't need to develop C# or Java code to specify your tests! Either, you don't need Visual Studio or Eclipse to compile your test suite. Just create an Xml file and let the framework interpret it and play your tests. The framework is designed as an add-on of NUnit but with the possibility to port it easily to other testing frameworks.
http://www.nbi.io
Apache License 2.0
106 stars 37 forks source link

Support needed around PBRS and reports #460

Open bthakks opened 5 years ago

bthakks commented 5 years ago

Trying to do a unit test against a SSRS report that is published on Power BI Report Server. The Report dataset is a query and not a stored procedure, so wondering will nUnit extract the dataset from the report. The code i used to do the test is the one below.

<?xml version="1.0" encoding="utf-8"?>
<testSuite name="SSRS" xmlns="http://NBi/TestSuite">
<settings>

        <default apply-to="system-under-test">

            <connectionString>@BIDEV</connectionString>
        </default>
        <default apply-to="assert">
            <connectionString>@BIUAT</connectionString>
        </default>
    </settings>
<test name="SSRS">
<system-under-test>
  <execution>
    <report
      source="Data Source=SQLServer\DEV17;Database=ReportServer_PBI_dev;Integrated Security=True;"
      path="/SCUK/Enterprise Solution/IATI/SCUK/"
      name="ReportTitle"
      dataset="ReportDataSet"
    >

    </report>
  </execution>
</system-under-test>
<assert>
 <row-count>
                <more-than or-equal="true">10</more-than>
            </row-count>
</assert>
</test>
</testSuite>

The Sql query in the dataset is Select * from schema.table

The error message thrown by nUnit is NBi.NUnit.Runtime.TestSuite.SSRS: System.Data.OleDb.OleDbException : Invalid object name 'schema.table.

Any advise on

Seddryck commented 5 years ago

NBi has never been tested with Power BI Report Server. Anyway PBRS is "just" a "kind of" SSRS and it should work (without too many changes). I've no PBRS available to debug this, but I could probably help you if you give me the result of the query at https://github.com/Seddryck/NBi/blob/develop/NBi.Core/Report/ReportServerListDataSet.sql on Data Source=SQLServer\DEV17;Database=ReportServer_PBI_dev;Integrated Security=True; (don't forget to provide the SQL parameters as specified in your test) ... I'm especially interested by the column CommandText.

bthakks commented 5 years ago

The output from the query requested is

ReportPath ReportName DataSetName DataSourceName CommandType CommandText
/SCUK/Enterprise Solution/IATI/SCUK/ IATINoTitleAndNoDescription DS_ResultsBaseline DV Text Select * from SSRS.Check_NoTitle_NoDesc
Seddryck commented 5 years ago

Based on your feedback, I can confirm that it should work and there is no difference on PBRS and SSRS.

In your previous message, you told me that the error message was:

NBi.NUnit.Runtime.TestSuite.SSRS: System.Data.OleDb.OleDbException : Invalid object name 'schema.table.

But I assume it was

NBi.NUnit.Runtime.TestSuite.SSRS: System.Data.OleDb.OleDbException : Invalid object 'SSRS.Check_NoTitle_NoDesc'.

Have you checked that this schema/table was effectively existing on @BIDEV ? That sounds to be the main reason that this test is failing.

bthakks commented 5 years ago

Okay so my configuration file with global connection string was incorrect. I pointed to the Report Server and was not aware that i should be pointing to the server from where the data will be extracted from. I was in the impression that nUnit will extract Data Source connection details from the ReportServer itself. Thanks for looking in to it and helping to resolve my issue, much appreciated. One last thing if i can dare to ask, is there any place where we have sample unit test. It will be really useful for new user like me. Thanks again

bthakks commented 5 years ago

So how do i compare data set between reports in two environment? I tried with this code but its complaining about invalid child element 'execution' in namespace.

<?xml version="1.0" encoding="utf-8"?>
<testSuite name="SSRS" xmlns="http://NBi/TestSuite">
<settings>

        <default apply-to="system-under-test">

            <connectionString>@BIDEV</connectionString>
        </default>
        <default apply-to="assert">
            <connectionString>@BIUAT</connectionString>
        </default>
    </settings>
<test name="SSRS">
<system-under-test>
  <execution>
    <report
      source="Data Source=SQLServer\DEV17;Database=ReportServer_PBI_dev;Integrated Security=True;"
      path='/SCUK/Enterprise Solution/IATI/SCUK/'
      name='IATINoTitleAndNoDescription'
      dataset='DS_ResultsBaseline'
    >

    </report>
  </execution>
</system-under-test>
<assert>
 <equalTo>
 <execution>
    <report
      source="Data Source=SQLServer\UAT17;Database=ReportServer_PBI_uat;Integrated Security=True;"
      path='/SCUK/Enterprise Solution/IATI/SCUK/'
      name='IATINoTitleAndNoDescription'
      dataset='DS_ResultsBaseline'
    >

    </report>
  </execution>
        </equalTo>       
</assert>
</test>
</testSuite>

Error Message


NBi.NUnit.Runtime.TestSuite.ExecuteTestCases:
System.ArgumentException : The test suite is not valid. Check with the XSD. 1 error has been found during the validation of the test-suite:
    At line 28: The element 'assert' in namespace 'http://NBi/TestSuite' has invalid child element 'execution' in namespace 'http://NBi/TestSuite'. List of possible elements expected: 'equalTo, subset-of, superset-of, evaluate-rows, syntacticallyCorrect, fasterThan, count, contain, equivalentTo, subsetOf, contained-in, exists, is, ordered, linkedTo, matchPattern, successful, row-count, all-rows, no-rows, some-rows, single-row, unique-rows, reference-exists' in namespace 'http://NBi/TestSuite'.
'''
Seddryck commented 5 years ago

You will find many examples of tests written with NBi at https://github.com/Seddryck/NBi/tree/develop/NBi.Testing/Acceptance/Resources/Positive

About comparing two result-sets initializing their respective query from some reports, you will need to use syntax 2.0 http://www.nbi.io/docs/syntax-2-0/

You will also find more info on what you can achieve with the new syntax result-set at http://www.nbi.io/docs/primitive-result-set/

Unfortunately, I’m on a flight and I can’t check the exact status of #358 but I’m confident that it should have no impact on your case. If it has tell me, will try to fix that.

bthakks commented 5 years ago

Not sure if i can get my head around the new syntax. Please correct me if i am wrong, but dont think we have any sample unit test with the new syntax. Also those samples are really really useful for me to have a head start on setting up different test. Thanks for pointing me to that location. Now i set up the unit test for comparing data set in two report in different environment (for now both pointing to same database), but doesnt seems to like my code as its throwing error message 

<?xml version="1.0" encoding="utf-8"?>
<testSuite name="SSRS" xmlns="http://NBi/TestSuite">
<settings>

        <default apply-to="system-under-test">

            <connectionString>@BIDEV</connectionString>
        </default>
        <default apply-to="assert">
            <connectionString>@BIUAT</connectionString>
        </default>
    </settings>
<test name="SSRS">

<system-under-test>
<result-set>
 <query>
    <report
      source="Data Source=FARR-SQL-DTU\DEV17;Database=ReportServer_PBI_dev;Integrated Security=True;"
      path='/SCUK/Enterprise Solution/IATI/SCUK/'
      name='IATINoTitleAndNoDescription'
      dataset='DS_ResultsBaseline'
    >

    </report>
    </query>
  </result-set>
</system-under-test>

<assert>
<equalTo>
<result-set>
 <query>
    <report
      source="Data Source=FARR-SQL-DTU\DEV17;Database=ReportServer_PBI_dev;Integrated Security=True;"
      path='/SCUK/Enterprise Solution/IATI/SCUK/'
      name='IATINoTitleAndNoDescription'
      dataset='DS_ResultsBaseline'
    >

    </report>
    </query>
  </result-set>
  </equalTo>
  </assert>

</test>
</testSuite>

Error

NBi.NUnit.Runtime.TestSuite.ExecuteTestCases:
System.ArgumentException : The test suite is not valid. Check with the XSD. 2 errors have been found during the validation of the test-suite:
    At line 17: The element 'system-under-test' in namespace 'http://NBi/TestSuite' has invalid child element 'result-set' in namespace 'http://NBi/TestSuite'. List of possible elements expected: 'execution, resultSet, members, structure, data-type' in namespace 'http://NBi/TestSuite'.
    At line 33: The element 'equalTo' in namespace 'http://NBi/TestSuite' has invalid child element 'result-set' in namespace 'http://NBi/TestSuite'. List of possible elements expected: 'column, resultSet, query, xml-source' in namespace 'http://NBi/TestSuite'.
Seddryck commented 5 years ago

Sounds that you are not using the latest version of NBi ... your version is outdated.

On 16 Mar 2019, at 14:26, bthakks notifications@github.com wrote:

Not sure if i can get my head around the new syntax. Please correct me if i am wrong, but dont think we have any sample unit test with the new syntax. Also those samples are really really useful for me to have a head start on setting up different test. Thanks for pointing me to that location. Now i set up the unit test for comparing data set in two report in different environment (for now both pointing to same database), but doesnt seems to like my code as its throwing error message

<?xml version="1.0" encoding="utf-8"?>

@BIDEV @BIUAT

Error

NBi.NUnit.Runtime.TestSuite.ExecuteTestCases: System.ArgumentException : The test suite is not valid. Check with the XSD. 2 errors have been found during the validation of the test-suite: At line 17: The element 'system-under-test' in namespace 'http://NBi/TestSuite' has invalid child element 'result-set' in namespace 'http://NBi/TestSuite'. List of possible elements expected: 'execution, resultSet, members, structure, data-type' in namespace 'http://NBi/TestSuite'. At line 33: The element 'equalTo' in namespace 'http://NBi/TestSuite' has invalid child element 'result-set' in namespace 'http://NBi/TestSuite'. List of possible elements expected: 'column, resultSet, query, xml-source' in namespace 'http://NBi/TestSuite'. — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

bthakks commented 5 years ago

I downloaded the latest release 1.19 and replaced framework folder, but still the same error message, as one below

NBi.NUnit.Runtime.TestSuite.ExecuteTestCases:
System.ArgumentException : The test suite is not valid. Check with the XSD. 2 errors have been found during the validation of the test-suite:
    At line 17: The element 'system-under-test' in namespace 'http://NBi/TestSuite' has invalid child element 'result-set' in namespace 'http://NBi/TestSuite'. List of possible elements expected: 'execution, resultSet, scalar, members, structure, data-type' in namespace 'http://NBi/TestSuite'.
    At line 48: The 'system-under-test' start tag on line 29 position 2 does not match the end tag of 'test'. Line 48, position 3.

nUnit Test

<?xml version="1.0" encoding="utf-8"?>
<testSuite name="SSRS" xmlns="http://NBi/TestSuite">
<settings>

        <default apply-to="system-under-test">

            <connectionString>@BIDEV</connectionString>
        </default>
        <default apply-to="assert">
            <connectionString>@BIUAT</connectionString>
        </default>
    </settings>
<test name="SSRS">

<system-under-test>
<result-set>
 <query>
    <report
      source="Data Source=FARR-SQL-DTU\DEV17;Database=ReportServer_PBI_dev;Integrated Security=True;"
      path='/SCUK/Enterprise Solution/IATI/SCUK/'
      name='IATINoTitleAndNoDescription'
      dataset='DS_ResultsBaseline'
    >

    </report>
    </query>
  </result-set>
<system-under-test>

<assert>
<equalTo>
<result-set>
 <query>
    <report
      source="Data Source=FARR-SQL-DTU\DEV17;Database=ReportServer_PBI_dev;Integrated Security=True;"
      path='/SCUK/Enterprise Solution/IATI/SCUK/'
      name='IATINoTitleAndNoDescription'
      dataset='DS_ResultsBaseline'
    >

    </report>
    </query>
  </result-set>
  </equalTo>
  </assert>

</test>
</testSuite>
Seddryck commented 5 years ago

Oki, result-set is not supported in v1.19, use resultSet (should be just a syntax change not a feature change) or download the latest versions of the 1.20 beta (we're close to RC).

For expl (compatible with 1.19 and resultSet), you should take a look to https://github.com/Seddryck/NBi/blob/release/v1.19/NBi.Testing/Acceptance/Resources/Positive/ResultSetConstraint.nbits

for compatible with v1.20: https://github.com/Seddryck/NBi/blob/develop/NBi.Testing/Acceptance/Resources/Positive/ResultSetConstraint.nbits

bthakks commented 5 years ago

Okay it seems its not possible with 1.19 with what i am trying. I want to compare two query in two different environment that is extracted from the SSRS Report. My test fails as it is not expecting 'query' namespace under resultSet!

Error Message

NBi.NUnit.Runtime.TestSuite.ExecuteTestCases:
System.ArgumentException : The test suite is not valid. Check with the XSD. 1 error has been found during the validation of the test-suite:
    At line 32: The element 'resultSet' in namespace 'http://NBi/TestSuite' has invalid child element 'query' in namespace 'http://NBi/TestSuite'. List of possible elements expected: 'row' in namespace 'http://NBi/TestSuite'.

Test Query

<?xml version="1.0" encoding="utf-8"?>
<testSuite name="SSRS" xmlns="http://NBi/TestSuite">
<settings>

        <default apply-to="system-under-test">

            <connectionString>@BIDEV</connectionString>
        </default>
        <default apply-to="assert">
            <connectionString>@BIUAT</connectionString>
        </default>
    </settings>
<test name="SSRS">

<system-under-test>
<resultSet>
 <query>
    <report
      source="Data Source=FARR-SQL-DTU\DEV17;Database=ReportServer_PBI_dev;Integrated Security=True;"
      path='/SCUK/Enterprise Solution/IATI/SCUK/'
      name='IATINoTitleAndNoDescription'
      dataset='DS_ResultsBaseline'
    />          
    </query>
  </resultSet>
</system-under-test>

<assert>
<equalTo>
<resultSet>
 <query>
    <report
      source="Data Source=FARR-SQL-DTU\DEV17;Database=ReportServer_PBI_dev;Integrated Security=True;"
      path='/SCUK/Enterprise Solution/IATI/SCUK/'
      name='IATINoTitleAndNoDescription'
      dataset='DS_ResultsBaseline'
    />          
    </query>
  </resultSet>
  </equalTo>
  </assert>
</test>
</testSuite>
bthakks commented 5 years ago

@Seddryck , i tested again with 1.20 and seems this is still not supported or am i missing anything here. System under test and assert both referring to the dataset query in SSRS report. The example you have, assert is csv file, so wondering if its possible to test against the other instance of SSRS? Thanks

Seddryck commented 5 years ago

Use the last-beta and following paths: system-under-test/result-set/query/report and assert/equal-to/result-set/query/report.

bthakks commented 5 years ago

Wondering how i install the beta package as the file download are different to the stable release. I do not see 3 folders but download nbi.framework.1.21.0-beta0264.nupkg! how do i extract the three folders to replace in my environment? Thanks for taking time to look into it and reply...

On Sat, Jul 27, 2019 at 3:27 PM Cédric L. Charlier notifications@github.com wrote:

Use the last-beta and following paths: system-under-test/result-set/query/report and assert/equal-to/result-set/query/report.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Seddryck/NBi/issues/460?email_source=notifications&email_token=AGENHJ6562BJMVTW3NMPLTLQBRLM5A5CNFSM4G6O7NQKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD26MOAY#issuecomment-515688195, or mute the thread https://github.com/notifications/unsubscribe-auth/AGENHJ7GIM3VJYDUN24KLALQBRLM5ANCNFSM4G6O7NQA .

-- Baiju Thakkar

Seddryck commented 5 years ago

Download framework.zip and replace the content of this folder.

On 28 Jul 2019, at 14:14, bthakks notifications@github.com wrote:

Wondering how i install the beta package as the file download are different to the stable release. I do not see 3 folders but download nbi.framework.1.21.0-beta0264.nupkg! how do i extract the three folders to replace in my environment? Thanks for taking time to look into it and reply...

On Sat, Jul 27, 2019 at 3:27 PM Cédric L. Charlier notifications@github.com wrote:

Use the last-beta and following paths: system-under-test/result-set/query/report and assert/equal-to/result-set/query/report.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Seddryck/NBi/issues/460?email_source=notifications&email_token=AGENHJ6562BJMVTW3NMPLTLQBRLM5A5CNFSM4G6O7NQKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD26MOAY#issuecomment-515688195, or mute the thread https://github.com/notifications/unsubscribe-auth/AGENHJ7GIM3VJYDUN24KLALQBRLM5ANCNFSM4G6O7NQA .

-- Baiju Thakkar — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.