Python - Script Debug DBMS

# %%

import datetime

from dateutil.relativedelta import relativedelta

#------------------------------------------------------------------------------------------------

#BEGIN Time

t_aFull=""

t_bFull=""

def diff(t_bFull, t_aFull):

    t_diffFull = relativedelta(t_bFull,t_aFull)  # later/end time comes first!

    return '{h}h {m}m {s}s'.format(h=t_diffFull.hours, m=t_diffFull.minutes, s=t_diffFull.seconds)

t_aFull = datetime.datetime.now()

print("========================== ")

print("Time Starting: ",t_aFull)

print("==========================")

#------------------------------------------------------------------------------------------------

########################################Script Body##############################################

# %%

#------------------------------------------------------------------------------------------------

#Script de Tempo End

t_bFull = datetime.datetime.now()

#diff(t_bFull,t_aFull)

print("========================== ")

print("Time Finish: ",t_bFull)

print("==========================")

print("Time Total: ",diff(t_aFull,t_bFull))

print("==========================")

#------------------------------------------------------------------------------------------------

SQL - scripts DBA

/*query DBAs */ --================================================================================= /*Consulta Tamanho das Tabelas*/ SELECT CASE when t.NAME = '/BIC/FZSD_C23' then 'WS - Vendas Integradas Histórico' when t.NAME = '/BIC/VZSD_C23F' then 'FACTVIEW - INFOCUBE ZSD_C23 ' ELSE '???' END as ETL, t.NAME AS Entidade, COUNT(DISTINCT(p.partition_id)) AS partition_id, SUM(DISTINCT(p.rows)) AS Registros, COUNT(DISTINCT(c.column_id)) AS Qtd_Cols, SUM(a.total_pages) * 8 AS EspacoTotalKB, SUM(a.used_pages) * 8 AS EspacoUsadoKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUsadoKB, MAX(t.modify_date) AS Ultima_Atualizacao, MAX(t.create_date) AS Ultima_Atualizacao2 FROM sys.tables t INNER JOIN sys.all_columns c ON t.OBJECT_ID = c.OBJECT_ID INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE upper(t.NAME) LIKE upper( '%logy%' ) --t.NAME NOT LIKE 'xpto%' and --t.is_ms_shipped = 0 and --i.OBJECT_ID > 255 GROUP BY t.Name -- ,p.partition_id ORDER BY Ultima_Atualizacao DESC , 4 desc --Registros DESC ; --================================================================================= /*Consulta Colunas das Tabelas like*/ SELECT distinct t.NAME AS Entidade, c.Name AS Coluna , c.max_length, c.precision, c.object_id FROM sys.tables t INNER JOIN --select * from sys.all_columns c ON t.OBJECT_ID = c.OBJECT_ID INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE upper(c.NAME) LIKE upper( '%logy%' ) --t.NAME NOT LIKE 'xpto%' and --t.is_ms_shipped = 0 and --i.OBJECT_ID > 255 order by 1,2 --================================================================================= /*Consulta Global Variables select @@VERSION ; select @@SERVERNAME ; select @@CONNECTIONS ; select @@MAX_CONNECTIONS ; select @@CPU_BUSY ; select @@ERROR ; select @@IDENTITY ; select @@IDLE ; select @@IO_BUSY ; select @@LANGID ; select @@LANGUAGE ; --select @@MAXCHARLEN ; select @@PACK_RECEIVED ; select @@PACK_SENT ; select @@PACKET_ERRORS ; select @@ROWCOUNT ; select @@SPID ; select @@TEXTSIZE ; select @@TIMETICKS ; select @@TOTAL_ERRORS ; select @@TOTAL_READ / @@TOTAL_WRITE ; select @@TRANCOUNT ; */

Power BI Service - Power Query - DataFormat.Error: Invalid cell value '#N/A'

 There are two points to be evaluated:

1. If you want to discard the errors

In this case, just run:

  #"Removed errors" = Table.RemoveRowsWithErrors(StepBefore),

2. Whether to adjust errors due to some columns having value for analysis.

You should select the errors to punctually analyze the non-wrong values and then run the following scripts:  

  #"Kept errors" = Table.SelectRowsWithErrors(StepBefore),

  #"Replaced errors" = Table.ReplaceErrorValues(#"Kept errors", {{[ColumnWithError], null}}),

  

After detecting the column in error, you can delete the Step:  

  #"Kept errors" = Table.SelectRowsWithErrors(StepBefore),

I hope help us!!!

Power BI with Python 3.7 - Step by Step

 #Commands 

conda --version

python --version

conda info --envs

conda info

conda list

conda env list

conda list -n myenv scipy

conda search jupyter 

conda list

conda search tensorflow 

#Install/Uninstall Commands 

conda install --help (...)  

conda remove -n PyPWBI --all -y 

#IMPORT Environment 

conda create --name PyPWBI python=3.7 

conda deactivate 

conda activate PyPWBI 

source activate PyPWBI (validar...) 

conda install numpy pandas matplotlib seaborn scikit-learn jupyter nose scipy

conda install -c conda-forge squarify

conda install -c conda-forge pandas-profiling

conda install -c conda-forge schedule

pip install speedtest-cli

pip install openpyxl

pip install investpy

pip uninstall matplotlib pillow numpy

pip install matplotlib pillow numpy

pip install yfinance --upgrade --no-cache-dir

Power Query - Connect API REST

1. Example (in parts)

GetAccessToken:
 () => 
let
    url = "https://wwwXPTO.com/Auth/GetApiToken",
    body  = "{ ""user"": ""userXPTO"", ""pwd"": ""123$""} ",
    AccessToken = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"], Content=Text.ToBinary(body)])),
    access_token = AccessToken[token]
in
    access_token
========================================================
GetList:
let
  // Get_Data
  Query = () => let
    Source = Json.Document(Web.Contents("https://wwwXPTO.com/GetList", [Headers=[Authorization="Bearer " & GetAccessToken()]])),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{4}[Value]
in
    Value
in
  Query
========================================================
FAT_Table:
let
    Source = GetList(),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

========================================================

2. Example Full

let 
    tenantId = "435435435345435435",
    clientId = "34543543534535345543",
    clientSecret = "35435435435435435435",
    grantType = "client_credentials",
    resource = "https://graph.windows.net/",
    endpointUsers = "/users",
    apiVersion = "?api-version=1.6",
    baseURL="https://login.microsoftonline.com",
    relativePath = "/"&tenantId&"/oauth2/token",
    url = baseURL & relativePath,
   body2 = [
          client_id=clientId,
          grant_type=grantType,
          client_secret=clientSecret,
          resource=resource],
    Source = Json.Document(Web.Contents(url, [Headers=[#"Accept" = "application/json", #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],Content = Text.ToBinary(Uri.BuildQueryString(body2))])), 
    accessToken = Source[access_token],
    tokenType = Source[token_type],
    usersURL = tenantId & endpointUsers &apiVersion,
    fullAccessToken = tokenType&" "&accessToken,
    Users = Json.Document(Web.Contents(resource & tenantId & endpointUsers &apiVersion,[Headers=[ContentType="application/json", Authorization=fullAccessToken, RelativePath=usersURL]])),
    value = Users[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"odata.type", "objectType", "objectId", "deletionTimestamp", "accountEnabled", "ageGroup", "assignedLicenses", "assignedPlans", "city", "companyName", "consentProvidedForMinor", "country", "createdDateTime", "creationType", "department", "dirSyncEnabled", "displayName", "employeeId", "facsimileTelephoneNumber", "givenName", "immutableId", "isCompromised", "jobTitle", "lastDirSyncTime", "legalAgeGroupClassification", "mail", "mailNickname", "mobile", "onPremisesDistinguishedName", "onPremisesSecurityIdentifier", "otherMails", "passwordPolicies", "passwordProfile", "physicalDeliveryOfficeName", "postalCode", "preferredLanguage", "provisionedPlans", "provisioningErrors", "proxyAddresses", "refreshTokensValidFromDateTime", "showInAddressList", "signInNames", "sipProxyAddress", "state", "streetAddress", "surname", "telephoneNumber", "thumbnailPhoto@odata.mediaEditLink", "usageLocation", "userIdentities", "userPrincipalName", "userState", "userStateChangedOn", "userType"}, {"odata.type", "objectType", "objectId", "deletionTimestamp", "accountEnabled", "ageGroup", "assignedLicenses", "assignedPlans", "city", "companyName", "consentProvidedForMinor", "country", "createdDateTime", "creationType", "department", "dirSyncEnabled", "displayName", "employeeId", "facsimileTelephoneNumber", "givenName", "immutableId", "isCompromised", "jobTitle", "lastDirSyncTime", "legalAgeGroupClassification", "mail", "mailNickname", "mobile", "onPremisesDistinguishedName", "onPremisesSecurityIdentifier", "otherMails", "passwordPolicies", "passwordProfile", "physicalDeliveryOfficeName", "postalCode", "preferredLanguage", "provisionedPlans", "provisioningErrors", "proxyAddresses", "refreshTokensValidFromDateTime", "showInAddressList", "signInNames", "sipProxyAddress", "state", "streetAddress", "surname", "telephoneNumber", "thumbnailPhoto@odata.mediaEditLink", "usageLocation", "userIdentities", "userPrincipalName", "userState", "userStateChangedOn", "userType"})
in
    #"Expanded Column1"

========================================================

3. example with Body parameters

let

    url = "https://j?????????????????",

    body  = "{ ""hCreationDate"": { ""$gte"": ""2021-08-01T00:00:00.000Z"",""$lte"": ""2021-08-03T00:00:00.000Z"" } } ",

    vData = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json" , Authorization="Bearer " & GetAcessToken()], Content=Text.ToBinary(body)])),

    results = vData[results],

    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "action", "feedId", "segment", "userId", "hCreationDate"}, {"_id", "action", "feedId", "segment", "userId", "hCreationDate"})

in 

    #"Expanded Column1"

DAX - CALCULATE Actualized vs. Planned

Measure:
 .Inflation_PLAN = CALCULATE(DIM_Prem_Plan[.Vlr_Inflation_PLAN], FILTER(DIM_Prem_Plan, DIM_Prem_Plan[PK_Plan]IN VALUES(FAT_DB[PK_Plan])))

Power Query - Connect API REST API Bearer

 

FNC_Access_Token

() => 

let

    url = "https://XPTO_SITE_TOKEN",

    body  = "{ ""user"": ""XPTO_USER"", ""pwd"": ""XPTO_PASS""} ",

    AccessToken = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"], Content=Text.ToBinary(body)])),

    access_token = AccessToken[token]

in

    access_token

FNC_Table_Prep

let

  // Get_Data

  Query = () => let

    Source = Json.Document(Web.Contents("XPTO_SITE_SEARCH", [Headers=[Authorization="Bearer " & Fnc_Access_Token()]])),

    #"Converted to Table" = Record.ToTable(Source),

    Value = #"Converted to Table"{4}[Value]

in

    Value

in

  Query

DIM_Table

let

    Source = Fnc_Table_Prep(),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "user", "phone", "profile", "hLastUpdate", "active", "activationCode", "userName", "birthday", "gender", "followingCategories", "isProfileComplete", "name", "dob"}, {"_id", "user", "phone", "profile", "hLastUpdate", "active", "activationCode", "userName", "birthday", "gender", "followingCategories", "isProfileComplete", "name", "dob"})

in

    #"Expanded Column1"