7 Replies Latest reply: Jun 20, 2013 5:00 PM by David Koschei RSS

    Governance Dashboard - 3 tiered architecture

    David Koschei

      Hi everyone,

       

      With the governance dashboard, I can easily see the sources of a QVD (perhaps a database).  I can also easily see the source of a QVW (should always be a QVD, unless it is a qvw that generates QVDs).  In the Data Lineage tab, I am trying to build a pivot table that will show me the source of the QVD for a QVW app.  In other words, I want to see what QVDs a data model is reading, and what databases those QVDs are reading from.

       

      Is there any way to do this in one object?  Or do I need to create a list of source QVDs, and then a list of QVD sources?

       

      Thank you

        • Re: Governance Dashboard - 3 tiered architecture
          David Koschei

          Separate issue with the Governance Dashboard, but I'm finding some Data Lineage processes (QVWs) that have sources, but those sources say used by 0 QVWs.

           

          The expression for Used by # QVWs is:

          count({$<LineageType={'QVW','QVX','QVD'}, DateNum={$(vMaxDate)}>} DISTINCT [QVW Name])

           

          If I replace it with:

          count({$<LineageType={'QVW','QVX','QVD'}, DateNum={$(vMaxDate)}>} DISTINCT Process)

           

          I get the correct counts in these sheets, however, the [QVW Name] is null in other places.  If I navigate to the QVW Overview tab, and unsuppress nulls for [QVW Name] (not sure why they are being suppressed in the first place], I can see these QVWs that use these sources, but the name is missing.

           

          Anyone else have this problem?  Anything I can do to fix it?

            • Re: Governance Dashboard - 3 tiered architecture
              Tyler Waterfall

              David,

              Good catch on the count(distinct Processes). It makes sense from a consistency standpoint to count the “processes” which use the sources listed in the chart on the Lineage tab. I can see how that number increases when I change it to count processes.

               

              The trick here, though, is that the processes being counted are ‘collected’ from the file metadata.  It is not necessary that the Governance Dashboard scan or even have access to those listed “Processes” in order for them to appear in this list.

               

              The field [QVW Name] in the dashboard is a list of all the QVWs physically scanned by the Dashboard.  (On a side note, this [QVW Name] is just a subset of the “DocName” which includes all documents scanned by the Dashboard as well as document names appearing in QVS, QVPR, and TaskResult logs.)

               

              So it is that you can have 80 processes listed in that chart but have only 38 [QVW Names] listed.  38 QVWs were actually scanned, but of the QVD, QVX, and QVWs scanned, 80 “processes” appeared in these files’ metadata (xml header).

               

              Does this make any sense?

              Tyler

            • Re: Governance Dashboard - 3 tiered architecture
              Tyler Waterfall

              David,

              Concerning the first Lineage question, I believe you would need more than one chart to show the multiple tiers of lineage, as you described.

              There might be some fancy way to consolidate this into one chart, but I've not figured that one out. Please do share if you do!

               

              Tyler

                • Re: Governance Dashboard - 3 tiered architecture
                  David Koschei

                  Hi Tyler, thank you for all of your responses.  I just started using this Goverance Dashboard this week, so I'm still trying to catch up to speed on how it works!

                   

                  For the lineage part, I ended up making a new chart, with dimensions:

                  Source_Type

                  Source,

                  =$(vQVWFolder) (a variable that extracts the QVWs file name out of Process),

                  =$(vQVWName) (QVW name out of Process),

                  =$(vTargetFolder),

                  =$(vTargetQVDName)

                   

                  and for the expression I just did:

                  Count({$<DateNum={$(vMaxDate)}>} DISTINCT 1)

                  Weird looking, but I just wanted a count of every record using vMaxDate (not entirely sure what LineageType does yet, but removing it kept my desired results).

                   

                  This seems to be working really well for everything I needed.  I'm able to add further set analysis for detecting additional errors (QVWs adding new sources outside of the 1st tier, data models not reading from QVDs, etc).

                   

                   

                   

                  I'm trying to comprehend your explanation of Process.  I have a QVW that is picked up as a Process, but not as a QVW (can't find it in the FileList table.. though I do find the QVDs it generates in FileList).  The dashboard should pick up the physical QVW.  Is that the bug that you reported?

                   

                  "It is not necessary that the Governance Dashboard scan or even have access to those listed “Processes” in order for them to appear in this list."

                  Sorry, I'm confused by this line.  Don't source and process both come from the meta data (i.e., both need to be scanned?)  Though as long as the dashboard keeps working as is, I'm satisfied with my current solution.

                   

                  Thank you for all of your help!  My next challenge is Publisher distribution/recipient data.  I just found that they do track this!  Such a powerful tool, this dashboard.  I'd also be curious to see if it collects data on .doc CALs somewhere, for better control on who has them.

                    • Re: Governance Dashboard - 3 tiered architecture
                      Tyler Waterfall

                      David,

                      Glad you are enjoying the Dashboard so far!

                       

                      And, I'm interested in trying out your chart. Would you be willing to define the variables you are using?

                       

                      Concerning the bug I mentioned, that is just an enhancement request for that chart to count distinct Process instead of count distinct [QVW Name].

                       

                      The fact that not all [Process]es that are QVWs are not physically scanned is not a bug. In the Config screen of the dashboard you define which folders and subfolders to scan for file metadata. The Dashboard then combs through those folders and subfolders and extracts the metadata from those files (which populates the [QVW Name] field). Unless all of the actual QVWs that show up in the Process list physically reside in the Documents to Scan folders/subfolders you define, their associated metadata will not appear in the Dashboard.

                       

                      Maybe an analogy will help. If you were to "scan" my geneology (just for me) you would find parents, a spouse, Grandparents, children, uncles, etc.. However, since you only "scanned" my geneology, you would not find the full geneology picture for my parents, though they show up in the list. I didn't scan their geneology. If you changed your scan to include myself and my parents, then you would see both me and my parents in the peson list ([QVW Name]), both of us in the parent list ([Process]), and just me and my children in the child list (Target).

                       

                      Concerning Publisher - let me know what you think. One outstanding request is for CAL usage monitoring, which does not currently exist in the Dashboard.

                       

                      Tyler

                        • Re: Governance Dashboard - 3 tiered architecture
                          David Koschei

                          Hi Tyler,

                           

                          What you're saying makes sense.  Except I have a .qvd in the default Sources straight table, that says Used by # QVWs 22.  When looking at the default Processes straight table, I count 23 records.  There are two records located in the exact same folder - but one is scanned, and the other is not.  I believe I have the root folder for both of them configured correctly, with no exclusions.  I'll let you know if I find more information on this.. I may have something configured wrong.  Even when I click on a process, I get the true list of sources above it, but they all say 0 for used by # qvws (so the record count is correct, but expression values is incorrect).  But using process instead has been working (I may need to fix the expressions in other objects now, though).

                           

                          As for the variables, I'm just doing basic string manipulation to extract the file name and folder name from the Process and Target fields.

                          vQVWName: Mid(Process, Index(Process, '\', -1) + 1)

                          vQVWFolder: SubField(Process, '\', -2)

                          vQVDName: Mid(Target, Index(Target, '\', -1) + 1)

                          vQVDFolder: SubField(Target, '\', -2)

                           

                          I'd vote +1 for the CAL usage monitoring.  I think I'll be able to manipulate Statistics > User Activity enough to see if people are utilizing their CALs.. but I'd like to have a master list of who has what type of license, and more importantly, if doc CAL, what documents.  We currently have to do this manually.

                           

                          EDIT: I did just find the Cal_Type field.  I tried to make a table with Cal_Type, DocName, and Authenticated_User, but it looks like it shows what Cal_Types documents were recently distributed to.  Makes sense.. doc CALS are technically unassigned as far as QlikView is concerned, until the document is opened, in which case it is assigned for 24 hours.  I wonder if there's a way to see whom we've reserved doc CAL slots to, so we can remove them when someone gets bumped up to named user.

                           

                          Thank you again for all of your help!