Category Archives

46 Articles

Generating perfect test data using Visual Studio and custom Data Generators

In many development projects the need arises to fill a database with some random data. With random data I do not mean mere gibberish, but actual data such as an email address, phone number, SSN, numeric value, dates, datetimes, etc.

With Visual Studio 2010 you can do this very easily. All you need is an edition of Visual Studio 2010 that includes the database projects (I think you will need Ultimate, but am not sure). And of course you will need a database, I used SQL Express in this example.

However, turns out that the data generators included in Visual Studio are somewhat limited. Visual Studio can generate data for many datatypes in SQL Server, but for example generating a phone number, SSN or email address is not possible.

However, one can easily create custom data generators, which is exactly what we will do. I will build custom data generators for email addresses and one that generators Lorem Ipsum text.

Building the custom data generators
Start with creating a new class library project. In the solution explorer, right-click the project and click ‘Add Reference’. Click the ‘.NET’ tab and select the following components: Microsoft.Data.Schema, Microsoft.Data.Schema.SQL and Microsoft.Data.Schema.Tools. Click ‘OK’ to add these references.

Open your class (I renamed my class and class file to LoremIpsumGenerator) and add the following using statements:

Now we need to inherit the Generator class by changing our class definition to:

Also add the DatabaseSchemaProviderCompatibilityAttribute to your class declaration as so:

Now we need to add an output property. This will be used to wire up the output of your generator to Visual Studio Data Generation Plans.

Also, if you need to specify some parameters to your data generator we need to create some input properties. I have created three: one for the number of words to generate, one to indicate if the data needs to start with ‘Lorem Ipsum…’ every time and one to specify if just random words can be selected or if we are more interested in sentences. Also note the default settings at the bottom.

Then all we need to do is override the OnGenerateNextValues() method. Since I wanted to Unit test my generator I have just one line in this method, which class a public void method (which my testing command line project can call). In this function (called Generate) I built the output based on Lorem Ipsum and set my Output property.

I will not discuss how exactly I managed to get the Lorem Ipsum words returned. If you want to know you can download the source code. Also, my email address generator follows the same structure of output, inputs (in this case none) and a generate function that creates the email address string and sets the output property.

Registering the data generators
If your data generators build successfully, we need to sing the generators with a strong name. To do this, right-click your project’s name and choose properties. Go to the Signing tab and select the ‘Sign the assembly’ check box and under ‘Choose a strong name key file’ choose ‘New…’. Follow the wizard and enter a password. Your screen now looks like this:

Now save all, and rebuild your solution.

Next we will need to create an XML file to accompany your DLL. Just add a XML file to your project and name it [YourProjectsName].Extensions.xml. My project (and thus my dll) is named MyDataGeneratorsLibrary, so the filename of the XML is: MyDataGeneratorsLibrary.Extensions.xml

Open the XML and replace the contents with this (you will have to fill in your own values).

As you can see, we need an Extension element for each generator. In my library I have created two generators (one for Lorem Ipsum text, and one for email addresses). Also you will have to specify the correct type (which is your classname and namespace) and the assembly (which again is your DLL file / project name). Last item to enter is the public key token. To get it open your Visual Studio Command prompt and use

to get the public key token returned to you. You will need to enter the correct path and file name. Copy paste the public key token into the XML.

One last thing I did is right click the XML and chose Properties. I changed the ‘Build Action’ to ‘Content’ and the ‘Copy to Output Directory’ property to ‘Copy always’. It does not matter in any way but it makes the next step just a bit easier.

Now, build your solution and navigate to your solutions directory. In there go to the bin\Debug folder, and copy your DLL and XML file.

Now it is time to register the data generators with Visual Studio. Be sure your Visual Studio 2010 instance is closed and navigate to the following folder: C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Extensions . It is good practice to keep your own extensions in a separate folder, so I created a folder ‘CustomGenerators’ and I advise you do the same. Next, paste your DLL and XML files into this folder.

Start Visual Studio.

Using the data generators to generate data
In Visual Studio, add a new project to your solution or create a new solution. Select SQL Server Database Project as the project type and enter a fancy name. I assume you already have built your database schema. Right click on your project name and choose ‘Import Objects and Settings’ and follow the wizard to get your database schema into your project. Next, add a new Data Generation Plan and open it. Select the table you want to use and specify the number of rows to insert.

You will see a list of the columns in the table. To the right (under ‘Generator’) you can specify which generator you want to use to generate data with. It should contain your custom generators now. Select it and click the preview button to test your generator: . Here is my screenshot:

If you have any input properties specified you can change the values by selecting the row and opening the properties window, see below screenshot for my Lorem Ipsum generator.

That concludes my introduction into custom data generators. Download the source code (with the Lorem Ipsum and email address generators) on Github.

Multilingual SSRS reports – Scenario 3: Report Definition Customization Extension

This is the third post in my series about multilingual SSRS reports. If you missed the introduction, you can find it here.

I am looking for feedback! Please let me know what you think!

The full code of the solution I describe here is available on Github.

What we will be doing in this post is developing something which called a Report Definition Customization Extension (RDCE), which is available since SQL Server 2008. The MSDN page is here: http://msdn.microsoft.com/en-us/library/dn296363.aspx

Let’s first start with the basics: what is a RDCE and what can it be used for?
Simply put a RDCE transforms an existing report definition (RDL) and modifies it just before the SSRS rendering engine renders the report. In other words building an RDCE allows you to interfere with the report rendering process and make some last minute changes just before rendering starts. In a schema it looks like this:

As this post is about making reports multilingual you might already have guessed that one of the uses of an RDCE is doing translations. However, you can also change the report’s look by hiding elements and even change the data set returned. This is useful if you not only need to translate labels in your report but also want to actually return text strings from your dataset in a different language. Another option would be for example to deal with right-to-left languages by changing your report’s layout.

Developing a RDCE
An RDCE is a .NET class library, where you will have to select .NET framework 3.5 as the target framework. This class library will need a reference to Microsoft.ReportingServices.Interfaces.dll. This contains the interface you will need to implement (see below). So let’s get started.

First off, start Visual Studio and create a new class library project (targeting .NET framework 3.5).

Include a reference to the aforementioned Microsoft.ReportingServices.Interfaces.dll file by right clicking ‘References’ in your Solution Explorer and choosing ‘Add Reference…’

The file is located under your SSRS installation directory. In my case the file was in C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin.

Now open your class file (I renamed it to MyRDCE.cs) and add the following line:

Then, implement IReportDefinitionCustomizationExtension by changing your class declaration to:

You can explicitly implement this interface to get an idea of the methods you will need to implement by right-clicking the interface name and choosing ‘Implement Interface Explicitly’.

Your code should now look like this:


Let’s start with the easiest method: IExtension.LocalizedName, which just returns the name for your RDCE. I replaced the line in this method with:

Yes, I know I should not have hardcoded the name here, but for demonstration purposes this will do.

As developing an RDCE is as hard as it is (never mind the debugging, testing, logging and deployment challenges you will have to deal with) I suggest adopting the framework Holger Schmeling introduced in his blog on RDCE’s. This framework allows you to componentize your transformations into smaller transformations instead of one lengthy bit of code. By doing so the RDCE can be more easily managed and understood. Notice that in this blog I will not deal with logging and error handling; however, in production that should be included. To use this framework we add an interface called ITransformation with one method named Transform. Just add a new Interface file to your solution, name it ITransform and copy paste the following code in (adapted from Holger’s blog):

The Transform() method takes a report definition in XML format and returns the modified version. The other two parameters provide the report and user context as returned from SSRS. This is handy to dynamically apply transforms based on these contexts.

Now it is time to add a list to hold all the transformations (Again thanks to Holger’s blog). Add the following code directly under the class statement in your RDCE class:

 

Also implement the ProcessReportDefinition() method that will be called by reporting services. This is the mother-transformation process. What we will do here is load the report and one-by-one call the registered transformations on the report. Find your ProcessReportDefinition() method and replace it with the following (you will need extra using statements).

This method first retrieves the report definition. Then all transformations are applied, after which the resulting report definition is returned and SSRS is informed about what we changed.

Following Holger’s framework we use the SSRS config file to define transformations. This might not be the ideal solution for your scenario since it involves editing the SSRS config file. However, on the plus side this allows you to register new transformations as required without having to redeploy or change the RDCE. Basically we need to change the rsreportserver.config (mine was in C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer) file and add something along these lines (do not forget to make a backup first!) as last entry in the <Extensions> node:

You can specify multiple Transformations by repeating the Transformation node as necessary. Note that Name and Type specify the name of the class and the namespace. Also, each Transformation can have custom properties set (name, value pairs).

Now, we can use the SSRS SetConfiguration() methods to get to the configuration settings in the rsreportserver.config file. This enables us to read the list of transformations registered and fill the list created earlier. We will do this by implementing our final function of the IReportDefinitionCustomizationExtension: SetConfiguration() (you will need another using statement at the top of your class).

 

Now that we have a functioning RDCE framework it is time to put it to work. In this sample I will build a simple translator transformation which will translate some texts in a report by looking them up in a database table. I added a new class to my solution, named it MyTranslator and put it in the SSRSMultilingualRDCE.Transformations namespace as configured in the reportserver.config file above. The code pretty much speaks for itself. It implements the Transform method from the ITransformation interface, retrieves the user language preference setting and retrieves the translation from the database:

 

 

Deploying a RDCE

Now that we have successfully built a translator we’re done with development and are ready for deployment. To deploy your RDCE to the SSRS server follow these steps:

First, copy you RDCE assembly (.dll file, in my case SSRSMultilingualRDCE.dll) into the Reporting Services bin directory (which in my case was C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin). If you have referenced any assemblies not in the Global Assembly Cache (maybe you implemented some logging framework) do not forget to copy them here as well.

Next we need to modify the SSRS configuration, starting with the rsreportserver.config file. It is located in C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer. Open it and lookup the <Service> element and add the following to that element (I added it to the bottom):

 

 

 

 

Next configuration change will be done in the rssrvpolicy.config file in the same directory as before. Open the file and add a CodeGroup in the PolicyLevel element for your RDCE assembly, like so:

You will need to specify the correct Name for the CodeGroup (just let it match the nam of your RDCE) and the correct location and name of the DLL you copied.

You may have to set up the permissions in the web.config file as Holger reports in his blog, although I have not had to do that.

To make your reports work with the RDCE there is a little thing we need to do; we need to link our reports with the RDCE deployed. The official way of doing this is using the Reporting Services Management Service. However, to use it you either need to have a deployment tool or be willing to write one. The unsupported way however is directly updating the report properties in the SSRS catalog. Please note that this is unsupported and I give no guarantees whatsoever regarding damage afflicted or the working of this on future versions. If you want to follow along take the following steps:

Open SQL Server Management Studio and execute the following query on your SSRS Service Database (not the TempDB) (you will have to adapt the where clause to match the name of your report):

 

 

This returns among others the ItemID, which you will need for the next statement. What we need to do is add <RDCE>Name_Of_Your_RDCE</RDCE> to the properties of the reports that need to use the RDCE.

Your will need to execute a query that looks like mine:

I have simply copied and pasted the ItemID in the where clause and also copied and pasted the value of property from the previous query into this query. I have appended the <RDCE> element just before the closing of the properties element. My report will now use the RDCE.

Debugging

When rendering a report that uses a RDCE SSRS will display any errors thrown by your code. This helps in debugging. Also note that if you make any changes to the code you will have to recopy your assembly and restart the SSRS service for SSRS to pick up the new version.

 

Putting it all together

All that remains now is showing that this works. I have created a very simple report for this:

This report contains two textboxes with labels in them. These labels were defined in the database and the database contains a translation for it. As a result my RDCE will translate these labels, depending on the user’s preferred language.

So, when a user with preference for English logs in this will be displayed:

And when a user with preference setting for Dutch opens the report it displays as follows:

My solution works with a configuration database setting for the user’s preference. However, just as easily one could adapt the code to read the setting from the SharePoint profile (if using SSRS in SharePoint integrated mode) or for example from Active Directory.

That concludes my lengthy blog on RDCEs. You can download my solution on Github. It includes a database project that will create the simple database I used and enter some sample data. Also, it includes all code discussed and the sample report I used. The configuration changes are for security reasons not included.

I am looking for feedback! Please let me know what you think!

Installing Master Data Services add-in for Excel 2013

I recently picked up my new laptop, which of course runs Office 2013 and Windows 8.
When you try to install the Master Data Services add-in for SQL 2012 you may run into a warning that says you need Office 2010.

There is an easy fix: just install the Master Data Services add-in for SQL 2012 SP1 (get it here: http://www.microsoft.com/en-us/download/details.aspx?id=35581 . Be sure to pick 32 / 64 bit to match your Office version.

Oh and of course you will have to get the other pre-req as well: Visual Studio 2010 Tools for Office runtime (find it here: http://www.microsoft.com/en-my/download/details.aspx?id=35594).

See http://support.microsoft.com/kb/2774422 for more information.

MDS / DQS integration on a domain controller

Normally I would never advice you installing anything on a domain controller, let alone SQL, MDS and DQS. However if you have BI demo machine you will probably have all this (and more) running on the same box. At least I do J

If you do you will probably get this error message when you try to enable the DQS integration from Master Data Services Configuration Manager after you successfully installed DQS and MDS.

When clicking the button ‘Enable integration with Data Quality Services’ an error will pop-up:

Here is where it gets a bit confusing. If you read the error message closely, it seems that MDS is looking for a local account on your machine instead of a domain account. However, with it being a domain controller, you cannot create local accounts…

To make this work you need to do the following:

  1. Add a Windows User Login into SQL Server for [YourDomain]\MDS_ServiceAccounts.

     

  2. Then run the following query against your DQS_MAIN database, which creates a user on the DQS_MAIN database which maps to the login you just created and adds the user to the DQS_Administrator role. Of course you can also do this using the UI. Make sure to enter your DOMAIN in the query below before executing.

    use [DQS_MAIN]
    GO
    IF NOT EXISTS (SELECT * FROM SYS.SYSUSERS WHERE NAME = ‘MDS_ServiceAccounts’)
    CREATE USER [MDS_ServiceAccounts] FOR LOGIN [YourDomain\MDS_ServiceAccounts]
    exec sp_addrolemember @rolename=N’dqs_administrator’,@membername=N’MDS_ServiceAccounts’
  3. When done go back to the Master Data Services configuration manager and hit the button again. Now it should come back with:

Victory ! J

 

SQL Server 2012 and SharePoint 2013 – Better Together session on repeat

June 10th, we will be hosting a SQL Server 2012 and SharePoint 2013 – better together session aimed at partners at our Microsoft office in the Netherlands! This is the third delivery, because the first two deliveries were overbooked and highly valued.

More information at http://blogs.microsoft.nl/blogs/sharepointonline/archive/2013/02/08/uitnodiging-sql-server-2012-and-sharepoint-2013-better-together.aspx

Looking forward to meeting you there!

(Please note that this session will be in Dutch..)

SQL Server 2012 Unboxing

Just about every new consumer technology device will be greeted with “unboxing” videos on YouTube. A lot of the people I talk to really need to start unboxing SQL Server 2012 and start to understand what is in the box. Most of them already have access to SQL Server 2012 and still think it is just a database. There is so much more! This post is aimed to providing a quick overview of what exactly is in the box with pointers to where you can find documentation.

  1. Database Engine (SSDE)
    First off, let’s start with the product that gave SQL its name: the database. This is without doubt the best known product of the whole SQL suite and also the most used. More often than not this is also the only product people use and know. Find out more here: http://technet.microsoft.com/en-us/library/ms187875.aspx
  2. Data Quality Services (DQS / SSDQS)
    Introduced with SQL Server 2012, DQS is a knowledge-driven data quality solution that works on the premise of specifying what defines data quality in a knowledge base and using to cleanse data automatically during ETL (see SSIS below), Master Data Management (see MDS below) processes or manually.
    See: http://technet.microsoft.com/en-us/library/ff877925.aspx
  3. Analyis Services (SSAS)
    Analysis Services is SQL Server’s analytical database or cube. It features both more traditional cubes and tabular models, provides self-service analysis capabilities and includes data mining. See http://technet.microsoft.com/en-us/library/bb522607.aspx
  4. Integration Services (SSIS)
    Integration Services is a full-blown ETL tool and can be used for all sorts of data integration solution. SSIS features a drag and drop interface to build the solution and provides a lot of components out of the box with connectors to and from just about any database, file storage or file format. If need be, you can also use the power of .NET to build the exact behavior required. SSIS also integrates with DQS to use data quality knowledge bases during ETL processes. For more info visit: http://technet.microsoft.com/en-us/library/ms141026.aspx
  5. Master Data Services (MDS)
    Master Data Services enables users to build a Master Data Management solution on top of SQL Server. MDS integrates with DQS to make data quality aspects a part of the overall MDM solution. See http://technet.microsoft.com/en-us/library/ee633763.aspx
  6. Reporting Services (SSRS)
    Reporting Services is the enterprise reporting solution that delivers web-enabled reports that can get information from a variety sources and be rendered in various formats (including Excel, Word and PDF). Also, reports can be retrieved on demand, on subscription bases or based on a alert. Find out more here: http://technet.microsoft.com/en-us/library/ms159106.aspx
  7. StreamInsight
    StreamInsight is Microsoft’s Complex Event Processor (CEP). CEP technology enables high throughput and real-time (low latency) processing of streams of data (events). Examples include financial trading, Web analytics, sensor data, etc. StreamInsight is provides a familiar development platform based on .NET to quickly start using real-time information. See: http://technet.microsoft.com/en-us/library/ee391416.aspx

That concludes the quick unboxing of SQL Server 2012. Although there is a lot more to say (about features, but also around editions and capabilities) , this should give you a good idea of what is in the box. Bottom line: there is a lot more to SQL Server than just a database!

%d bloggers like this: