Solved

SQL Server - SSIS to SSRS Table Usage Tracking

Posted on 2014-02-03
6
255 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
6 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 65

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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

776 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