• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

SQL Server - SSIS to SSRS Table Usage Tracking

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
MIKE
Asked:
MIKE
1 Solution
 
Surendra NathTechnology LeadCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
allen_richCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now