?
Solved

SQL Server - SSIS to SSRS Table Usage Tracking

Posted on 2014-02-03
6
Medium Priority
?
273 Views
Last Modified: 2016-02-10
Experts,

Is there a way for me to analyze and map which TABLES are used by my SSRS Reports so that I can gain understanding to th is question:

Which SSRS Reports are IMPACTED by which SSIS ETL Packages/Processes....?

Thanks
0
Comment
Question by:MIKE
5 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39829844
Sadly, neither SSRS or SSIS have a facility to do this. You can programatically traverse SSIS data flow metadata so could do this in theory (one can algorithmically collapse a graph of such dependencies down to a minimal form), but doing this to SQL is quite a bit harder. I'm not aware of any off the shelf tooling that does a decent job of this.


in short the answer is NO.
0
 
LVL 17

Author Comment

by:MIKE
ID: 39829866
Ever use SQL Profiler..?

Also, to break this request down,.. I think if I can "extract which tables are used in an ETL Process",.. I have SQL where I can matchup to "SQL Table Usage Statistics" that I believe may give me the needed info.

IS there a way, via SQL Script or Stored Proc that will extract which TABLES are being utlized/updated within a SSIS ETL Package..?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39829997
>Which SSRS Reports are IMPACTED by which SSIS ETL Packages/Processes....?
Just out of wild curiosity, identify the individual that asked this question, and why they are asking.
0
 
LVL 17

Author Comment

by:MIKE
ID: 39830054
Main Task,.... my project requirement IS:

1. Identify LONG RUNNING ETL process. DONE
2. Identify what SSRS Reports are imacted by these LONG RUNNING ETL processes
3. Identify ways to rewrite/optimize ETL to reduce processing times (if possible)

SO,.. I'm on Requirement #2,.... anyone know the best way to handle #2??
0
 
LVL 2

Accepted Solution

by:
allen_rich earned 2000 total points
ID: 39832066
To be able to track Tables usage. I will give a query which you can use to get SSRS statistics of your reports on this in the following article and Analyze performance of your reporting services reports by using SSRS statistics.

For example, If you want to see the used reports,  then you can do the following:

SELECT
    COUNT(Name) AS ExecutionCount,
    Name,
    SUM(TimeDataRetrieval) AS TimeDataRetrievalSum,
    SUM(TimeProcessing) AS TimeProcessingSum,
    SUM(TimeRendering) AS TimeRenderingSum,
    SUM(ByteCount) AS ByteCountSum,
    SUM([RowCount]) AS RowCountSum
FROM
    (
     SELECT
        TimeStart,
        Catalog.Type,
        Catalog.Name,
        TimeDataRetrieval,
        TimeProcessing,
        TimeRendering,
        ByteCount,
        [RowCount]
     FROM
        Catalog
     INNER JOIN ExecutionLog
        ON Catalog.ItemID = ExecutionLog.ReportID
     WHERE
        Type = 2
    ) AS RE
GROUP BY
    Name
ORDER BY
    COUNT(Name) DESC,
    Name

Hope it helps
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question