Presentation

You are currently browsing articles tagged Presentation.

image

Regular readers of this blog know that I was one of the speakers at our Belgian SQL Server Days event last Thursday and Friday.

I have to admit that I underestimated the amount of work a little: working full time for a customer, preparing a one-hour session, preparing the SQL Server Days (luckily we’ve got a great team at the SQLUG for this part!) and still having some kind of personal life as well.

On day two we had the pleasure of welcoming 279 attendees all ready to attend sessions in our five different tracks.  And about 70 of those people were in my room!!  I had practically no stress before or during the start of my session, except for the moment when I had to get the microphone wire attached to my head somehow (luckily the technician was still around!)…  In fact, I was more stressed for my dry run session three days earlier.

Would I do it again?  Ow yeah, absolutely!  Even though it requires quite some time in preparation, the applause at the end of the session makes up for that! Smile

And now the moment you’ve all been waiting for: my slides and demos are available for download from my Skydrive.

References

For those seeking more information, here are some references related to my session content:

The official Data Quality Services blog (a lot of useful content on DQS!!)

SSIS DQS Matching Transformation

SSIS DQS Domain Value Import

Creating a Synchronous Transformation with the Script Component

Fuzzy Lookup Transformation

DQS Cleansing Transformation

Finding Similar Strings With Fuzzy Logic Functions Built Into MDS

Have fun, and see you next time!

Valentino.

Share

Tags: , ,

SQL Server Days 2013For the first time ever am I not only participating in the organization of the SQL Server Days, I’ll be speaking as well!  Exciting times!

In my session, Cleaning up the mess with SSIS and DQS, you’ll learn a couple of tricks on dealing with dirty data.

Here’s the abstract for my session:

Are you loading data from exotic sources such as Excel and flat file?

Or are you dealing with manually-entered data through an application that allows, well, practically anything?  And do you sometimes run into trouble because the incoming data is not as expected?

Then you should really join us in this session in which I’ll demonstrate (yes, demos!) several different techniques that can be used to cleanse your dirty data!

Check out the agenda for more details!

We’ve got speakers from all over the world: South-Africa, Italy, Slovenia, UK, US and of course… Belgium!

Not registered yet?  Do it now!

Ow, and did you know we’ve introduced a special feature?  It’s called Bring a colleague for free.  If you register you can register an additional person for free, as long as there aren’t 50 of those free ones registered that is so be fast!

Bring your colleague

In the meantime: have fun and see you there!

Valentino.

Share

Tags: , , , , ,

Thanks all for attending our presentation about Data Visualization Tips & Tricks at this year’s Community Day.  I hope you’ve enjoyed it as much as we have!

The slides and SSRS project can be downloaded from my SkyDrive through this link.

As said before: let me know if you’re stuck with visualizing something and I’ll see if I can find a trick for it!

Have fun!

Valentino.

Share

Tags: , , ,

In case you’ve never heard of Community Day, despite that it’s the largest Belgian community event of the year, it’s an event in which several user groups join forces to provide a nice learning experience to the attendees.

The participating user groups this year are VISUG, AZUG, Belgian C++ user group, BIWUG, CLUG, Pro-Exchange, SCUG, SQLUG, ACCB.net/VBIB, WinTalks, DotNetHub, MADN, TechNine, IAMCT and PHP Benelux.

And guess what: I’m a speaker! My colleague Koen Verbeeck and myself have once more decided to share a presenting slot that day.  And we’ll be sharing some Data Visualization Tips & Tricks with you!

Koen will be talking about how to best visualize data: should I use a pie chart or would a bar chart provide a more interesting result, is it really a good idea to use red and green, that kind of stuff.

When he’s finished I’ll be taking over to show you some cool things you can do with SQL Server Reporting Services.  Yes, that’s lots of demos and practically no slides!  Cool?

The expected level is 300.  Don’t worry though, we know that the Community Day crowd consists mainly of developers so we’ll be taking that into account.

Ow, and I’m accepting topic suggestions! If you’ve been wondering about how to get something visualized but never got it to work, don’t hesitate to contact me by either posting a comment here or through email (found on my About page).  Provide a clear description of what you’d like to achieve, with a mock-up example if needed.  I’ll be selecting the most interesting proposals to fill some minutes with. Looking forward to some useful suggestions! Smile

More details about Community Day:

  • Utopolis Mechelen, Spuibeekstraat 5, 2800 Mechelen
  • June 20th 2013, 0830 – 1900
  • Twitter: @ComDayBe
  • Five tracks!

What are you waiting for? Register now before all seats are taken!

See you there and in the meantime: have fun!

Valentino.

Share

Tags: , , , ,

Source: http://commons.wikimedia.org/wiki/File:Estrangela.jpgThis post is a follow-up to my presentation on Automating SSRS Deployments Using Built-in SQL Server Tools.

During that presentation, I demonstrated that you can use the Reporting Services web service in a custom application to get a list of your deployed objects from the server.  Using that functionality you’d be able to generate the batch script containing the RS commands which in their turn use the RSS scripts which I’ve demonstrated.

For those who missed the presentation but ended up here nevertheless, you can download the slides and demo code through my other follow-up post.

However, that’s not the only option!

In this post I’ll be using a technique which is one of my favorite methods of saving work.  Instead of typing a lot of code yourself, type just a bit of code to generate the code that you actually want.  Or, in other words and more concrete: today I’ll be using T-SQL to generate the RS commands for the batch file!

I will be querying some SSRS system tables, so take into account that the code may need changes for future versions of SQL Server, but I can confirm that it should work with both SQL Server 2008 and 2012.  SQL Server 2005 probably too but I don’t have that running anymore so can’t test it out.  Don’t hesitate to leave a comment if you can confirm that this will run on 2005.

The database on which you need to run these queries is called ReportServer, or ReportServer$YourInstance if you didn’t install it with the default instance name.

Figuring It All Out

One of the tougher parts of writing these queries was figuring out how the system tables can be linked with each other.  I studied some of the stored procedures and combined that knowledge with what I actually saw as content on my test system.  I’ll first share a couple of generic queries which may prove useful in case you just want to query the tables.  Then I’ll share the queries that will produce the RS statements.

Usually you’re only interested in the objects that belong to your project.  So each of the following queries has a filter to only show the content of my project folder, called Awesome Reports.

List Of Objects Dependent On Data Source

The following query shows a list of all objects that have a data source dependency.

--list of objects dependent on data sources
select
    case C.[Type]
        when 1 then 'Folder'
        when 2 then 'Report'
        when 3 then 'Resource'
        when 4 then 'Linked Report'
        when 5 then 'Data Source'
        when 6 then 'Report Model'
        when 7 then '???' --post comment if you know what 7 means
        when 8 then 'Shared Dataset'
        when 9 then 'Report Part'
    end ObjectType
    , C.Name
    , DS.Name DataSourceName
    , DSET_SRC.Name DatasetExternalDataSourceName
    , PARENT.Path
from dbo.Catalog C
inner join dbo.Catalog PARENT on PARENT.ItemID = C.ParentID
inner join dbo.DataSource DS on C.ItemID = DS.ItemID
left outer join dbo.DataSets DSET on DSET.LinkID = C.ItemID
left outer join dbo.DataSource DSET_SRC on DSET_SRC.ItemID = DSET.ItemID
where C.Type <> 5 --no data sources
    and C.Path like '/Awesome Reports/%'
order by c.Type, C.Name

So what does this return on my test system?

List of objects with data source dependency

In the case of a shared dataset, the DataSetDataSource name is what the data source is called inside the shared dataset.  To get to the real name of the data source, we need to join with the dbo.DataSource table through the dbo.DataSets table while using the right key fields.

List Of Datasets With Linked Data Source

The query below can be interesting to generate a list of your datasets with their data source dependency, including their location.

--list of datasets with linked data source
select DSET.Name DatasetName
    , DSET_PARENT.Path DatasetLocation
    , DS.Name DataSourceName
    , DS_PARENT.Path DataSourceLocation
from dbo.DataSets DSET
inner join dbo.Catalog DSET_C on DSET_C.ItemID = DSET.LinkID
inner join dbo.Catalog DSET_PARENT on DSET_PARENT.ItemID = DSET_C.ParentID
inner join dbo.DataSource DS on DS.ItemID = DSET.ItemID
inner join dbo.Catalog DS_C on DS_C.ItemID = DS.Link
inner join dbo.Catalog DS_PARENT on DS_PARENT.ItemID = DS_C.ParentID
where DSET_C.Path like '/Awesome Reports/%'

On my system I get the following result:

List of datasets with their data source dependency

Generating The RS Commands

Using the knowledge we got by examining above queries, we can now build some queries that generate the RS commands.  And here they are:

--generate DeployDataset commands
select '%RS% -i "%SCRIPTLOCATION%\DeployDataset.rss" -s %REPORTSERVER% -l %TIMEOUT% -e %ENDPOINT% -v dataset="'
     + DSET_C.Name + '" -v datasetLocation="' + DSET_PARENT.Path
     + '" -v dataSourceName="' + DS.Name
     + '" -v dataSourceLocation="' + DS_PARENT.Path + '"'
from dbo.DataSets DSET
inner join dbo.Catalog DSET_C on DSET_C.ItemID = DSET.LinkID
inner join dbo.Catalog DSET_PARENT on DSET_PARENT.ItemID = DSET_C.ParentID
inner join dbo.DataSource DS on DS.ItemID = DSET.ItemID
inner join dbo.Catalog DS_C on DS_C.ItemID = DS.Link
inner join dbo.Catalog DS_PARENT on DS_PARENT.ItemID = DS_C.ParentID
where DSET_C.Path like '/Awesome Reports/%'

--generate DeployReport commands
select '%RS% -i "%SCRIPTLOCATION%\DeployReport.rss" -s %REPORTSERVER% -l %TIMEOUT% -e %ENDPOINT% -v name="'
    + C.Name + '" -v folder="' + PARENT.Path + '"'
FROM dbo.Catalog AS C
inner join dbo.Catalog PARENT on PARENT.ItemID = C.ParentID
where C.Type = 2 --report
    and C.Path like '/Awesome Reports/%'

--generate LinkReportToDataSource commands
select '%RS% -i "%SCRIPTLOCATION%\LinkReportToDataSource.rss" -s %REPORTSERVER% -l %TIMEOUT% -e %ENDPOINT% -v reportName="'
    + REP.Name + '" -v parent="' + REP_PARENT.Path + '" -v dataSource="'
    + DS.Name + '" -v dataSourceLocation="' + DS_PARENT.Path + '"'
from dbo.Catalog AS REP
inner join dbo.Catalog REP_PARENT on REP_PARENT.ItemID = REP.ParentID
inner join dbo.DataSource DS on REP.ItemID = DS.ItemID
inner join dbo.Catalog DS_C on DS_C.ItemID = DS.Link
inner join dbo.Catalog DS_PARENT on DS_PARENT.ItemID = DS_C.ParentID
where REP.Type = 2 --report
    and REP.Path like '/Awesome Reports/%'
order by REP.Name asc, DS.Name asc

--generate LinkReportToDataset commands
SELECT '%RS% -i "%SCRIPTLOCATION%\LinkReportToDataset.rss" -s %REPORTSERVER% -l %TIMEOUT% -e %ENDPOINT% -v reportName="'
    + REP.Name + '" -v reportLocation="' + REP_PARENT.Path + '" -v datasetName="'
    + DSET.Name + '" -v datasetLocation="' + DSET_PARENT.Path + '"'
from dbo.Catalog AS REP
inner join dbo.Catalog REP_PARENT on REP_PARENT.ItemID = REP.ParentID
inner join dbo.DataSets AS DSET ON REP.ItemID = DSET.ItemID
inner join dbo.Catalog DSET_C on DSET_C.ItemID = DSET.LinkID
inner join dbo.Catalog DSET_PARENT on DSET_PARENT.ItemID = DSET_C.ParentID
where REP.Path like '/Awesome Reports/%'

And here’s the output:

Generating the RS commands

Conclusion

It may not be a recommended practice to use the Reporting Services system tables, but as long as you’re only reading them and you keep in mind that your code may break in a next version of SQL Server, they can surely be useful!

Have fun!

Valentino.

Share

Tags: , , , ,

« Older entries

© 2008-2017 BI: Beer Intelligence? All Rights Reserved