Solved

SQL Server - SSIS to SSRS Table Usage Tracking

Posted on 2014-02-03
6
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

615 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