1 2 Previous Next 15 Replies Latest reply on May 3, 2017 11:09 AM by Jeff D

    historical_events hist_view_id has no matching values with views id in workgroup database

    Jasson Giannini

      Well the title pretty much says it all. I am not able to match historical events with views views_stats, views_metrics_aggregations. Most examples I have seen have matching values between the two. Any idea on how to reconcile or test?

       

       

      I initially cam accross this issue in this thread: Getting Views by Device Type from the Workgroup Database

       

      Here is my code:

       

      SELECT "historical_events"."id" AS "id",

        "historical_events"."historical_event_type_id" AS "historical_event_type_id",

        "historical_events"."worker" AS "worker",

        "historical_events"."duration_in_ms" AS "duration_in_ms",

        "historical_events"."is_failure" AS "is_failure",

        "historical_events"."details" AS "details",

        "historical_events"."created_at" AS "created_at",

        "historical_events"."hist_actor_user_id" AS "hist_actor_user_id",

        "historical_events"."hist_target_user_id" AS "hist_target_user_id",

        "historical_events"."hist_actor_site_id" AS "hist_actor_site_id",

        "historical_events"."hist_target_site_id" AS "hist_target_site_id",

        "historical_events"."hist_project_id" AS "hist_project_id",

        "historical_events"."hist_workbook_id" AS "hist_workbook_id",

        "historical_events"."hist_view_id" AS "hist_view_id",

        "historical_events"."hist_datasource_id" AS "hist_datasource_id",

        "historical_events"."hist_comment_id" AS "hist_comment_id",

        "historical_events"."hist_tag_id" AS "hist_tag_id",

        "historical_events"."hist_group_id" AS "hist_group_id",

        "historical_events"."hist_licensing_role_id" AS "hist_licensing_role_id",

        "historical_events"."hist_schedule_id" AS "hist_schedule_id",

        "historical_events"."hist_task_id" AS "hist_task_id",

        "historical_events"."hist_data_connection_id" AS "hist_data_connection_id",

        "historical_events"."hist_config_id" AS "hist_config_id",

        "historical_events"."hist_capability_id" AS "hist_capability_id",

        "historical_event_types"."type_id" AS "type_id",

        "historical_event_types"."name" AS "name",

        "historical_event_types"."action_type" AS "action_type",

        "hist_workbooks"."id" AS "id (hist_workbooks)",

        "hist_workbooks"."workbook_id" AS "workbook_id",

        "hist_workbooks"."name" AS "name (hist_workbooks)",

        "hist_workbooks"."repository_url" AS "repository_url",

        "hist_workbooks"."size" AS "size",

        "hist_workbooks"."revision" AS "revision",

        "hist_users"."id" AS "id (hist_users)",

        "hist_users"."user_id" AS "user_id",

        "hist_users"."name" AS "name (hist_users)",

        "hist_users"."domain_name" AS "domain_name",

        "hist_users"."email" AS "email",

        "hist_users"."system_user_id" AS "system_user_id",

        "hist_users"."system_admin_level" AS "system_admin_level",

        "hist_users"."hist_licensing_role_id" AS "hist_licensing_role_id (hist_users)",

        "hist_users"."site_admin_level" AS "site_admin_level",

        "hist_users"."publisher_tristate" AS "publisher_tristate",

        "hist_views"."id" AS "id (hist_views)",

        "hist_views"."view_id" AS "view_id",

        "hist_views"."name" AS "name (hist_views)",

        "hist_views"."repository_url" AS "repository_url (hist_views)",

        "hist_views"."revision" AS "revision (hist_views)",

        "view_metrics_aggregations"."id" AS "id (view_metrics_aggregations)",

        "view_metrics_aggregations"."view_id" AS "view_id (view_metrics_aggregations)",

        "view_metrics_aggregations"."day_index" AS "day_index",

        "view_metrics_aggregations"."month_index" AS "month_index",

        "view_metrics_aggregations"."year_index" AS "year_index",

        "view_metrics_aggregations"."view_count" AS "view_count",

        "view_metrics_aggregations"."device_type" AS "device_type",

        "views"."id" AS "id (views)",

        "views"."name" AS "name (views)",

        "views"."repository_url" AS "repository_url (views)",

        "views"."description" AS "description",

        "views"."created_at" AS "created_at (views)",

        "views"."locked" AS "locked",

        "views"."published" AS "published",

        "views"."read_count" AS "read_count",

        "views"."edit_count" AS "edit_count",

        "views"."datasource_id" AS "datasource_id",

        "views"."workbook_id" AS "workbook_id (views)",

        "views"."index" AS "index",

        "views"."updated_at" AS "updated_at",

        "views"."owner_id" AS "owner_id",

        "views"."fields" AS "fields",

        "views"."title" AS "title",

        "views"."caption" AS "caption",

        "views"."sheet_id" AS "sheet_id",

        "views"."state" AS "state",

        "views"."sheettype" AS "sheettype",

        "views"."site_id" AS "site_id",

        "views"."repository_data_id" AS "repository_data_id",

        "views"."first_published_at" AS "first_published_at",

        "views"."revision" AS "revision (views)",

        "views"."for_cache_updated_at" AS "for_cache_updated_at",

        "views"."luid" AS "luid",

        "views"."thumbnail_id" AS "thumbnail_id",

        "views_stats"."id" AS "id (views_stats)",

        "views_stats"."user_id" AS "user_id (views_stats)",

        "views_stats"."view_id" AS "view_id (views_stats)",

        "views_stats"."nviews" AS "nviews",

        "views_stats"."time" AS "time",

        "views_stats"."site_id" AS "site_id (views_stats)",

        "views_stats"."device_type" AS "device_type (views_stats)"

      FROM "public"."historical_events" "historical_events"

        INNER JOIN "public"."historical_event_types" "historical_event_types" ON ("historical_events"."historical_event_type_id" = "historical_event_types"."type_id")

        LEFT JOIN "public"."hist_workbooks" "hist_workbooks" ON ("historical_events"."hist_workbook_id" = "hist_workbooks"."id")

        LEFT JOIN "public"."hist_users" "hist_users" ON ("historical_events"."hist_actor_user_id" = "hist_users"."id")

        LEFT JOIN "public"."hist_views" "hist_views" ON ("historical_events"."hist_view_id" = "hist_views"."id")

        LEFT JOIN "public"."view_metrics_aggregations" "view_metrics_aggregations" ON ("historical_events"."hist_view_id" = "view_metrics_aggregations"."view_id")

        LEFT JOIN "public"."views" "views" ON ("historical_events"."hist_view_id" = "views"."id")

        LEFT JOIN "public"."views_stats" "views_stats" ON ("views"."id" = "views_stats"."view_id")

        • 1. Re: historical_events hist_view_id has no matching values with views id in workgroup database
          Jeff D

          Hi Jasson, it would be helpful to see one of your sql queries.

          • 2. Re: historical_events hist_view_id has no matching values with views id in workgroup database
            Jasson Giannini

            Apologies, here it is!:

             

            SELECT "historical_events"."id" AS "id",

              "historical_events"."historical_event_type_id" AS "historical_event_type_id",

              "historical_events"."worker" AS "worker",

              "historical_events"."duration_in_ms" AS "duration_in_ms",

              "historical_events"."is_failure" AS "is_failure",

              "historical_events"."details" AS "details",

              "historical_events"."created_at" AS "created_at",

              "historical_events"."hist_actor_user_id" AS "hist_actor_user_id",

              "historical_events"."hist_target_user_id" AS "hist_target_user_id",

              "historical_events"."hist_actor_site_id" AS "hist_actor_site_id",

              "historical_events"."hist_target_site_id" AS "hist_target_site_id",

              "historical_events"."hist_project_id" AS "hist_project_id",

              "historical_events"."hist_workbook_id" AS "hist_workbook_id",

              "historical_events"."hist_view_id" AS "hist_view_id",

              "historical_events"."hist_datasource_id" AS "hist_datasource_id",

              "historical_events"."hist_comment_id" AS "hist_comment_id",

              "historical_events"."hist_tag_id" AS "hist_tag_id",

              "historical_events"."hist_group_id" AS "hist_group_id",

              "historical_events"."hist_licensing_role_id" AS "hist_licensing_role_id",

              "historical_events"."hist_schedule_id" AS "hist_schedule_id",

              "historical_events"."hist_task_id" AS "hist_task_id",

              "historical_events"."hist_data_connection_id" AS "hist_data_connection_id",

              "historical_events"."hist_config_id" AS "hist_config_id",

              "historical_events"."hist_capability_id" AS "hist_capability_id",

              "historical_event_types"."type_id" AS "type_id",

              "historical_event_types"."name" AS "name",

              "historical_event_types"."action_type" AS "action_type",

              "hist_workbooks"."id" AS "id (hist_workbooks)",

              "hist_workbooks"."workbook_id" AS "workbook_id",

              "hist_workbooks"."name" AS "name (hist_workbooks)",

              "hist_workbooks"."repository_url" AS "repository_url",

              "hist_workbooks"."size" AS "size",

              "hist_workbooks"."revision" AS "revision",

              "hist_users"."id" AS "id (hist_users)",

              "hist_users"."user_id" AS "user_id",

              "hist_users"."name" AS "name (hist_users)",

              "hist_users"."domain_name" AS "domain_name",

              "hist_users"."email" AS "email",

              "hist_users"."system_user_id" AS "system_user_id",

              "hist_users"."system_admin_level" AS "system_admin_level",

              "hist_users"."hist_licensing_role_id" AS "hist_licensing_role_id (hist_users)",

              "hist_users"."site_admin_level" AS "site_admin_level",

              "hist_users"."publisher_tristate" AS "publisher_tristate",

              "hist_views"."id" AS "id (hist_views)",

              "hist_views"."view_id" AS "view_id",

              "hist_views"."name" AS "name (hist_views)",

              "hist_views"."repository_url" AS "repository_url (hist_views)",

              "hist_views"."revision" AS "revision (hist_views)",

              "view_metrics_aggregations"."id" AS "id (view_metrics_aggregations)",

              "view_metrics_aggregations"."view_id" AS "view_id (view_metrics_aggregations)",

              "view_metrics_aggregations"."day_index" AS "day_index",

              "view_metrics_aggregations"."month_index" AS "month_index",

              "view_metrics_aggregations"."year_index" AS "year_index",

              "view_metrics_aggregations"."view_count" AS "view_count",

              "view_metrics_aggregations"."device_type" AS "device_type",

              "views"."id" AS "id (views)",

              "views"."name" AS "name (views)",

              "views"."repository_url" AS "repository_url (views)",

              "views"."description" AS "description",

              "views"."created_at" AS "created_at (views)",

              "views"."locked" AS "locked",

              "views"."published" AS "published",

              "views"."read_count" AS "read_count",

              "views"."edit_count" AS "edit_count",

              "views"."datasource_id" AS "datasource_id",

              "views"."workbook_id" AS "workbook_id (views)",

              "views"."index" AS "index",

              "views"."updated_at" AS "updated_at",

              "views"."owner_id" AS "owner_id",

              "views"."fields" AS "fields",

              "views"."title" AS "title",

              "views"."caption" AS "caption",

              "views"."sheet_id" AS "sheet_id",

              "views"."state" AS "state",

              "views"."sheettype" AS "sheettype",

              "views"."site_id" AS "site_id",

              "views"."repository_data_id" AS "repository_data_id",

              "views"."first_published_at" AS "first_published_at",

              "views"."revision" AS "revision (views)",

              "views"."for_cache_updated_at" AS "for_cache_updated_at",

              "views"."luid" AS "luid",

              "views"."thumbnail_id" AS "thumbnail_id",

              "views_stats"."id" AS "id (views_stats)",

              "views_stats"."user_id" AS "user_id (views_stats)",

              "views_stats"."view_id" AS "view_id (views_stats)",

              "views_stats"."nviews" AS "nviews",

              "views_stats"."time" AS "time",

              "views_stats"."site_id" AS "site_id (views_stats)",

              "views_stats"."device_type" AS "device_type (views_stats)"

            FROM "public"."historical_events" "historical_events"

              INNER JOIN "public"."historical_event_types" "historical_event_types" ON ("historical_events"."historical_event_type_id" = "historical_event_types"."type_id")

              LEFT JOIN "public"."hist_workbooks" "hist_workbooks" ON ("historical_events"."hist_workbook_id" = "hist_workbooks"."id")

              LEFT JOIN "public"."hist_users" "hist_users" ON ("historical_events"."hist_actor_user_id" = "hist_users"."id")

              LEFT JOIN "public"."hist_views" "hist_views" ON ("historical_events"."hist_view_id" = "hist_views"."id")

              LEFT JOIN "public"."view_metrics_aggregations" "view_metrics_aggregations" ON ("historical_events"."hist_view_id" = "view_metrics_aggregations"."view_id")

              LEFT JOIN "public"."views" "views" ON ("historical_events"."hist_view_id" = "views"."id")

              LEFT JOIN "public"."views_stats" "views_stats" ON ("views"."id" = "views_stats"."view_id")

            • 3. Re: historical_events hist_view_id has no matching values with views id in workgroup database
              Jeff D

              Try making this change:

               

              Instead of this:

              LEFT JOIN "public"."views" "views" ON ("historical_events"."hist_view_id" = "views"."id")

               

              use this:

              LEFT JOIN "public"."views" "views" ON ("hist_views"."view_id" = "views"."id")

               

              More generally, don't join IDs directly from the historical_events table to the target table -- go through the corresponding hist_xxx table.  historical_events doesn't store the actual object id, it stores an id to the hist_xxx table, so there's a level of indirection.  In case you're wondering why: if the underlying object gets deleted, the hist_xxx table has information that can be used to help identify the object.

              • 4. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                Jasson Giannini

                Hi Jeff,

                 

                this looks like it has worked if I used the device type field pulled from Views_stats. However it has sent my total from 14k hits to ~4 Million. Do you know how I bring that back down to reality?

                 

                Code:

                 

                SELECT "historical_events"."id" AS "id",

                  "historical_events"."historical_event_type_id" AS "historical_event_type_id",

                  "historical_events"."worker" AS "worker",

                  "historical_events"."duration_in_ms" AS "duration_in_ms",

                  "historical_events"."is_failure" AS "is_failure",

                  "historical_events"."details" AS "details",

                  "historical_events"."created_at" AS "created_at",

                  "historical_events"."hist_actor_user_id" AS "hist_actor_user_id",

                  "historical_events"."hist_target_user_id" AS "hist_target_user_id",

                  "historical_events"."hist_actor_site_id" AS "hist_actor_site_id",

                  "historical_events"."hist_target_site_id" AS "hist_target_site_id",

                  "historical_events"."hist_project_id" AS "hist_project_id",

                  "historical_events"."hist_workbook_id" AS "hist_workbook_id",

                  "historical_events"."hist_view_id" AS "hist_view_id",

                  "historical_events"."hist_datasource_id" AS "hist_datasource_id",

                  "historical_events"."hist_comment_id" AS "hist_comment_id",

                  "historical_events"."hist_tag_id" AS "hist_tag_id",

                  "historical_events"."hist_group_id" AS "hist_group_id",

                  "historical_events"."hist_licensing_role_id" AS "hist_licensing_role_id",

                  "historical_events"."hist_schedule_id" AS "hist_schedule_id",

                  "historical_events"."hist_task_id" AS "hist_task_id",

                  "historical_events"."hist_data_connection_id" AS "hist_data_connection_id",

                  "historical_events"."hist_config_id" AS "hist_config_id",

                  "historical_events"."hist_capability_id" AS "hist_capability_id",

                  "historical_event_types"."type_id" AS "type_id",

                  "historical_event_types"."name" AS "name",

                  "historical_event_types"."action_type" AS "action_type",

                  "hist_workbooks"."id" AS "id (hist_workbooks)",

                  "hist_workbooks"."workbook_id" AS "workbook_id",

                  "hist_workbooks"."name" AS "name (hist_workbooks)",

                  "hist_workbooks"."repository_url" AS "repository_url",

                  "hist_workbooks"."size" AS "size",

                  "hist_workbooks"."revision" AS "revision",

                  "hist_users"."id" AS "id (hist_users)",

                  "hist_users"."user_id" AS "user_id",

                  "hist_users"."name" AS "name (hist_users)",

                  "hist_users"."domain_name" AS "domain_name",

                  "hist_users"."email" AS "email",

                  "hist_users"."system_user_id" AS "system_user_id",

                  "hist_users"."system_admin_level" AS "system_admin_level",

                  "hist_users"."hist_licensing_role_id" AS "hist_licensing_role_id (hist_users)",

                  "hist_users"."site_admin_level" AS "site_admin_level",

                  "hist_users"."publisher_tristate" AS "publisher_tristate",

                  "hist_views"."id" AS "id (hist_views)",

                  "hist_views"."view_id" AS "view_id",

                  "hist_views"."name" AS "name (hist_views)",

                  "hist_views"."repository_url" AS "repository_url (hist_views)",

                  "hist_views"."revision" AS "revision (hist_views)",

                  "view_metrics_aggregations"."id" AS "id (view_metrics_aggregations)",

                  "view_metrics_aggregations"."view_id" AS "view_id (view_metrics_aggregations)",

                  "view_metrics_aggregations"."day_index" AS "day_index",

                  "view_metrics_aggregations"."month_index" AS "month_index",

                  "view_metrics_aggregations"."year_index" AS "year_index",

                  "view_metrics_aggregations"."view_count" AS "view_count",

                  "view_metrics_aggregations"."device_type" AS "device_type",

                  "views"."id" AS "id (views)",

                  "views"."name" AS "name (views)",

                  "views"."repository_url" AS "repository_url (views)",

                  "views"."description" AS "description",

                  "views"."created_at" AS "created_at (views)",

                  "views"."locked" AS "locked",

                  "views"."published" AS "published",

                  "views"."read_count" AS "read_count",

                  "views"."edit_count" AS "edit_count",

                  "views"."datasource_id" AS "datasource_id",

                  "views"."workbook_id" AS "workbook_id (views)",

                  "views"."index" AS "index",

                  "views"."updated_at" AS "updated_at",

                  "views"."owner_id" AS "owner_id",

                  "views"."fields" AS "fields",

                  "views"."title" AS "title",

                  "views"."caption" AS "caption",

                  "views"."sheet_id" AS "sheet_id",

                  "views"."state" AS "state",

                  "views"."sheettype" AS "sheettype",

                  "views"."site_id" AS "site_id",

                  "views"."repository_data_id" AS "repository_data_id",

                  "views"."first_published_at" AS "first_published_at",

                  "views"."revision" AS "revision (views)",

                  "views"."for_cache_updated_at" AS "for_cache_updated_at",

                  "views"."luid" AS "luid",

                  "views"."thumbnail_id" AS "thumbnail_id",

                  "views_stats"."id" AS "id (views_stats)",

                  "views_stats"."user_id" AS "user_id (views_stats)",

                  "views_stats"."view_id" AS "view_id (views_stats)",

                  "views_stats"."nviews" AS "nviews",

                  "views_stats"."time" AS "time",

                  "views_stats"."site_id" AS "site_id (views_stats)",

                  "views_stats"."device_type" AS "device_type (views_stats)"

                FROM "public"."historical_events" "historical_events"

                  INNER JOIN "public"."historical_event_types" "historical_event_types" ON ("historical_events"."historical_event_type_id" = "historical_event_types"."type_id")

                  LEFT JOIN "public"."hist_workbooks" "hist_workbooks" ON ("historical_events"."hist_workbook_id" = "hist_workbooks"."id")

                  LEFT JOIN "public"."hist_users" "hist_users" ON ("historical_events"."hist_actor_user_id" = "hist_users"."id")

                  LEFT JOIN "public"."hist_views" "hist_views" ON ("historical_events"."hist_view_id" = "hist_views"."id")

                  LEFT JOIN "public"."view_metrics_aggregations" "view_metrics_aggregations" ON ("historical_events"."hist_view_id" = "view_metrics_aggregations"."view_id")

                  LEFT JOIN "public"."views" "views" ON ("hist_views"."view_id" = "views"."id")

                  LEFT JOIN "public"."views_stats" "views_stats" ON ("views"."id" = "views_stats"."view_id")

                • 5. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                  Jeff D

                  You can change the SELECT to choose a particular date range.

                  • 6. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                    Jasson Giannini

                    Hi Jeff,

                     

                    I kept the created at filter the same, is that what you mean?

                    Before join change:

                    view2.PNG

                    After join change:

                    view1.PNG

                    • 7. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                      Jeff D

                      I'm not sure what you mean by "bring this back to reality".  Tableau can handle 4 million rows without a problem.

                      • 8. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                        Jasson Giannini

                        I mean back to the actual number  of views. There were 14k, which is what the 'traffic to views' dash shows on tableau server. But once I make this join change it shows 4 million views. Clearly this isn't accurate.

                        • 9. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                          Jeff D

                          I think the SQL joins are not quite right (you may be getting all historical events, not just those associated with a view).  Instead of using SQL, use Tableau directly to join the tables.  The preview window will show you the partial results, and you can get a better sense of what your data looks like.

                          • 10. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                            Matt Francis

                            I've just hit on the same problem. A view of a workbook/view via the _view_stats table isn't found in the historic_events table.

                             

                            Did you get an answer to your issue?

                            • 11. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                              Jeff D

                              Hi Matt, are you using the same query as Jasson?

                               

                              Some more information:

                               

                              - views_stats has a counter for each view that tracks the number of times the view was accessed

                               

                              - historical_events is an audit log that has information for each specific event (each access)

                               

                              Although you can join these tables (not directly -- you have to use hist_views to map from hist_view_id to view_id), I'm not sure how useful this would be.  The information is at a different level of granularity: views_stats has aggregated data, historical_events has unaggregated data.

                               

                              Matt, if this doesn't answer your question, I suggest you start another thread and provide more details about what you're doing.

                              • 12. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                                Jasson Giannini

                                Hi Jeff,

                                 

                                So I actually am using tables, I just was pulling the SQL so I could paste it here. Below are the tables.

                                 

                                historicalevents.PNG

                                • 13. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                                  Jeff D

                                  HI Jasson, you're joining historical_events directly to views.  That won't work correctly, since historical_events does not contain the view_id.  As I mentioned above, you need to use hist_views to map from hist_view_id to view_id.

                                  • 14. Re: historical_events hist_view_id has no matching values with views id in workgroup database
                                    Jasson Giannini

                                    My apologies, that was my old view. The one with your suggestion is below, the one that gives me 4 million views

                                     

                                    historical_events.PNG

                                    1 2 Previous Next