-
Notifications
You must be signed in to change notification settings - Fork 1
/
getCalculatedFields.py
304 lines (253 loc) · 13.1 KB
/
getCalculatedFields.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
# import libraries we are going to use
import pandas as pd
import numpy as np
from pandas.io.json import json_normalize #package for flattening json into pandas df
from tableau_api_lib import TableauServerConnection
from tableau_api_lib.utils.querying import get_sites_dataframe
# create a function that we will call in Tableau Prep
def get_calculated_fields(input):
# set credentials to log in to your Tableau Server / Tableau Online. I'm using a personal token
tableau_server_auth = {
'dev-environment': {
'server': 'https://10ax.online.tableau.com/', # update for your server
'api_version': '3.11', # update for your version of the API
'personal_access_token_name': 'PAT-NAME', # update with your personal access token's name
'personal_access_token_secret': 'PAT-SECRET', # update with your personal access token's secret
'site_name': "", # set it to '' if accessing your default site
'site_url': 'YOUR-SITE-CONTENT-URL' # set it to '' if accessing your default site
}
}
# log in to the server. If the connection was successful, we get 200 code
connection = TableauServerConnection(tableau_server_auth, env='dev-environment')
connection.sign_in()
# get info about my server
my_server = connection.server_info()
print(my_server.json())
# GraphQL query to get a list of calculations and dashboards where these calculations are used
query_dashboards = """"
query dataDictionary_dashboards {
calculatedFields {
id
name
description
dataType
formula
downstreamDashboards {
id
name
path
}
}
}
"""
# GraphQL query to get a list of workbooks for every dashboard
query_workbooks = """"
query dataDictionary_workbooks {
dashboards {
id
workbook {
name
projectName
updatedAt
owner {
username
email
}
}
}
}
"""
# GraphQL query to get a list of datasources and dashboards connected to them
query_datasources = """"
query dataDictionary_datasources {
datasources {
id
name
__typename
upstreamDatabases {
name
__typename
connectionType
dataQualityWarnings{
warningType
authorDisplayName
updatedAt
}
}
downstreamDashboards {
id
}
}
}
"""
# sending the query_dashboards to the Metadata API
response_dashboards = connection.metadata_graphql_query(query=query_dashboards)
# The server sends us an HTTP response containing our query results in JSON format
response_dashboards = response_dashboards.json()['data']['calculatedFields']
# print the response
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Response in JSON:")
print(response_dashboards)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
# converting json response to a Pandas dataframe
dashboards_normalized=pd.json_normalize(
response_dashboards,
record_path=['downstreamDashboards'],
meta=['id','name','description','dataType','formula'],record_prefix='Dashboard ',
meta_prefix='Calculation ')
# print the data frame
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Table with the list of calculated fields and related dashboards:")
print(dashboards_normalized)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
# sending the query_workbooks to the Metadata API
response_workbooks = connection.metadata_graphql_query(query=query_workbooks)
# The server sends us an HTTP response containing our query results in JSON format
response_workbooks = response_workbooks.json()['data']['dashboards']
# print the response
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Response in JSON:")
print(response_workbooks)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
# converting json response to normalised dataframe
workbooks_normalized=pd.json_normalize(response_workbooks)
workbooks_normalized.rename(columns={"id":"Dashboard id"},inplace=True)
# print the data frame
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Table with the list of workbooks and related dashboards:")
print(workbooks_normalized)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
# join workbooks and dashboards based on the Dashboard id field
workbooks_dashboards_df = pd.merge(left = dashboards_normalized, right = workbooks_normalized, how = 'left', left_on='Dashboard id', right_on='Dashboard id')
# print the data frame
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Table with the list of calculated fields, workbooks, and dashboards:")
print(workbooks_dashboards_df)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
# sending the query_datasources to the Metadata API
response_datasources = connection.metadata_graphql_query(query=query_datasources)
# The server sends us an HTTP response containing our query results in JSON format
response_datasources = response_datasources.json()['data']['datasources']
# print the response
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Response in JSON:")
print(response_datasources)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
# converting json response to normalised dataframe
#first, get data on Upstream databases for each data source
datasources_data = pd.json_normalize(response_datasources,
record_path=['upstreamDatabases'],
meta=['id','name','__typename'],
record_prefix='Upstream Database ',
meta_prefix='Datasource ',
errors='ignore')
# print the data frame
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Table with the list of data sources:")
print(datasources_data)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
#next, get data on dashboards for each data source
dashboards_data = pd.json_normalize(response_datasources,
record_path=['downstreamDashboards'],
meta=['id'],
record_prefix='Dashboard ',
meta_prefix='Datasource ',errors='ignore')
# print the data frame
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Table with the list of data sources and related dashboards:")
print(dashboards_data)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
# then get the data on data qualityt warnings
dataQualityWarnings_data = pd.json_normalize(response_datasources,
record_path=['upstreamDatabases','dataQualityWarnings'],
meta=['id'],
record_prefix='Upstream Database Data Quality Warning ',
meta_prefix='Datasource ',
errors='ignore')
# print the data frame
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Table with the list of data qualityt warnings:")
print(dataQualityWarnings_data)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
#last, joining these 3 tables based on the Datasource id field
# first, datasources and dashboards on datasource id field
datasources_dashboards_df = pd.merge(
left = datasources_data,
right = dashboards_data,
how = 'outer',
left_on='Datasource id',
right_on='Datasource id')
datasources_dashboards_df.drop(columns=['Upstream Database dataQualityWarnings'],inplace=True)
# print the data frame
#print(datasources_dashboards_df)
# and lastly adding data qualityt warnings data to this table, as well on datasource id field
datasources_combined_df = pd.merge(
left = datasources_dashboards_df,
right = dataQualityWarnings_data,
how = 'left',
left_on='Datasource id',
right_on='Datasource id')
# print the data frame
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Table with the combined list of data sources:")
print(datasources_combined_df)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
# joining datasources_combined_df and workbooks_dashboards_df on Dashboard id field
combined_df = pd.merge(
left = datasources_combined_df,
right = workbooks_dashboards_df,
how = 'outer',
left_on='Dashboard id',
right_on='Dashboard id')
# print the data frame
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
print("Combined final table:")
print(combined_df)
print("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-")
# replace NaN values with 'None'
combined_df = combined_df.replace(np.nan, '', regex=True)
# renaming columns before outputting them to Tableau Prep
combined_df.rename(
columns={"Upstream Database __typename":"Upstream Database type",
"Upstream Database connectionType":"Upstream Database connection type",
"Datasource __typename":"Datasource type",
"Upstream Database Data Quality Warning warningType": "Upstream Database Data Quality Warning type",
"Upstream Database Data Quality Warning authorDisplayName": "Upstream Database Data Quality Warning author",
"Upstream Database Data Quality Warning updatedAt": "Upstream Database Data Quality Warning last updated",
"Calculation dataType": "Calculation data type",
"workbook.name": "Workbook name",
"workbook.projectName": "Workbook project",
"workbook.updatedAt": "Workbook last updated",
"workbook.owner.username": "Workbook owner"},
inplace=True)
# converting all columns to strings
all_columns = list(combined_df) # Creates list of all column headers
combined_df[all_columns] = combined_df[all_columns].astype("str")
# sign out of the server
connection.sign_out()
return combined_df
# set the structure for data set coming to Tableau Prep
def get_output_schema():
return pd.DataFrame({
"Upstream Database name":prep_string(),
"Upstream Database type":prep_string(),
"Upstream Database connection type":prep_string(),
"Datasource id":prep_string(),
"Datasource name":prep_string(),
"Datasource type":prep_string(),
"Dashboard id":prep_string(),
"Upstream Database Data Quality Warning type":prep_string(),
"Upstream Database Data Quality Warning author":prep_string(),
"Upstream Database Data Quality Warning last updated":prep_string(),
"Dashboard name":prep_string(),
"Dashboard path":prep_string(),
"Calculation id":prep_string(),
"Calculation name":prep_string(),
"Calculation description":prep_string(),
"Calculation data type":prep_string(),
"Calculation formula":prep_string(),
"Workbook name":prep_string(),
"Workbook project":prep_string(),
"Workbook last updated":prep_string(),
"Workbook owner":prep_string()
})