Solved

SQL Server - SSIS to SSRS Table Usage Tracking

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to resize column length with primary ket 4 34
SQL: Transformation or Pivot 3 37
SQL - Subquery in WHERE section 4 34
Query Result column name from data 16 13
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 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