文藝復興

文藝復興

Python connect to SQL Server - Python

Posted by: bart30508 | in Python, SQL Server | 2 months, 1 week ago |

import pyodbc import pandas as pd from datetime import datetime

JSON_MODIFY技巧/限制 - SQL Server

Posted by: bart30508 | in SQL Server | 2 months, 1 week ago |

前陣子同事詢問到此Function - Json_Modify,他想要改變的是JSON Array,因此我提供以下解法。

數學建模的好處,最小化生產成本 - Data Science

Posted by: bart30508 | in Data Science | 2 months, 1 week ago |

情境,有個需求,配置給某AM工廠多少產量,可以最小化生產成本?

Export all of Report on Power Bi Server - Power Bi

Posted by: bart30508 | in PowerBI, Powershell | 2 months, 1 week ago |

<# .SYNOPSIS Export of all SSRS reports datasources and images .DESCRIPTION This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer database to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -. Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension. Please change the "Configuration data" below to your enviroment. Works with SQL Server 2005 and higher versions in all editions. Requires SELECT permission on the ReportServer database. .NOTES Author : Olaf Helper Requires: PowerShell Version 1.0, Ado.Net assembly .LINK GetSqlBinary: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getsqlbinary.aspx #>

Power Bi Cloud And On-Premise Upload Report Through PowerShell - PowerBi

Posted by: bart30508 | in PowerBI, Powershell | 2 months, 1 week ago |

PowerBi On-Premise Upload

<# https://github.com/microsoft/ReportingServicesTools
Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

https://www.powershellgallery.com/packages/PublishPBIXFile/1.0.0.1/Content/PublishPBIXFile.ps1 #>

# Code By SB 2019
$ReportServerURI = 'http://localhost:8080/Reports'          # Input Local path of powerbi file
$filePath = "C:\Users\Export_PBI_SSRS\Inventory.pbix"       # Input Local path of powerbi file
$PBIxfileName = "Inventory"                                 # INput your Powerbi File Name
$FolderName ='TestingByMax'                                 # Input PowerBI server Folder Name Where you wann to deploy
$Username ='Username'
$password ='Password'                          
$ReportServerName ='DBServerName'                           #input SQL server where POWERBI database installed
$ReportServerDatabase = 'ReportServer'                      #input PowerBi Database Name

$ConnectionString ='data source=Client01\SQL2019;initial catalog=Client_SB_1'  # input New Connection String / Client ConnectionString

$FolderLocation = '/'
$FolderPath = $FolderLocation + $FolderName

write-host "Deployment Started ..." -ForeGroundColor Yellow 
$session = New-RsRestSession -ReportPortalUri $ReportServerURI
Write-RsRestCatalogItem -WebSession $session -Path $filePath -RsFolder $folderPath -Description $Description -Overwrite
$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
$dataSources[0].DataModelDataSource.AuthType = ‘Windows'
$dataSources[0].DataModelDataSource.Username = $ConnectionString 
$dataSources[0].DataModelDataSource.Secret = $password

Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

$ID =  $dataSources[0].Id

#$Query = " Update [DataModelDataSource] SET ConnectionString = Username From [dbo].[DataModelDataSource] Where DataSourceID ='" + $ID  + "' "
#Invoke-Sqlcmd -Query $Query -ServerInstance CPMSUNRSQL17\CPMSRINST17 -Database ReportServerPowerBI

$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
$dataSources[0].DataModelDataSource.Username = $Username
$dataSources[0].DataModelDataSource.Secret = $password
Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

write-host "Deployment Done . . ." -ForeGroundColor Green

Upload a whole Folder

<# https://github.com/microsoft/ReportingServicesTools
Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

https://www.powershellgallery.com/packages/PublishPBIXFile/1.0.0.1/Content/PublishPBIXFile.ps1 #>

$SourceDirectory = 'C:\Users\Export_PBI_SSRS'
# Code By SB 2019
$ReportServerURI = 'http://localhost/Reports' # Input Local path of powerbi file
#$filePath = "C:\Users\chenc89\Desktop\jobdata\Develop\Powerbi_Owner\Export_PBI_SSRS\Inventory.pbix"     # Input Local path of powerbi file
$FolderName ='TestingByMax/GReports'       # Input PowerBI server Folder Name Where you wann to deploy
$Username ='UserName'
$password ='Password'                          
$ReportServerName ='PBIDBServeName'                #input SQL server where POWERBI database installed
$ReportServerDatabase = 'ReportDBName'           #input PowerBi Database Name 
$ConnectionString ='data source=Client01\SQL2019;initial catalog=Client_SB_1'  # input New Connection String / Client ConnectionString
$FolderLocation = '/'
$FolderPath = $FolderLocation + $FolderName


foreach($filePath in Get-ChildItem $SourceDirectory -Filter *.pbix)
{
$PBIxfileName = $filePath -replace '.pbix','' # INput your Powerbi File Name
Write-host ""

write-host $PBIxfileName -ForegroundColor Green 
try{
    write-host "Deployment Started ..." -ForeGroundColor Yellow 
    $session = New-RsRestSession -ReportPortalUri $ReportServerURI
    Write-RsRestCatalogItem -WebSession $session -Path $filePath -RsFolder $folderPath -Description $Description -Overwrite
    $datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
    $dataSources[0].DataModelDataSource.AuthType = ‘Windows'
    $dataSources[0].DataModelDataSource.Username = $ConnectionString 
    $dataSources[0].DataModelDataSource.Secret = $password

    Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

    $ID =  $dataSources[0].Id

    #$Query = " Update [DataModelDataSource] SET ConnectionString = Username From [dbo].[DataModelDataSource] Where DataSourceID ='" + $ID  + "' "
    #Invoke-Sqlcmd -Query $Query -ServerInstance CPMSUNRSQL17\CPMSRINST17 -Database ReportServerPowerBI

    $datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
    $dataSources[0].DataModelDataSource.Username = $Username
    $dataSources[0].DataModelDataSource.Secret = $password
    Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

    write-host "Deployment Done . . ." -ForeGroundColor Green 
}catch [System.IO.IOException] {
     $msg = "Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
     Write-Error msg
}catch [System.Web.Services.Protocols.SoapException]{
 if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
            {
                Write-Error "Report: $reportName already exists." 
            }
            else
            {
                $msg = "Error uploading report: $reportName. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
}

}

Power Bi Clouid Upload

import requests
from requests_toolbelt.multipart.encoder import MultipartEncoder

groupId = "your workspace ID"
reportName = "reportName that you choose to appear on the server"
accessToken = "xx"

url = 'https://api.powerbi.com/v1.0/myorg/groups/' + groupId + '/imports?datasetDisplayName=' + reportName
headers = {
    'Content-Type': 'multipart/form-data',
    'authorization': 'Bearer ' + accessToken
}
file_location = '/xx.pbix'
# you need this dictionary to convert a binary file into form-data format
# None here means we skip the filename and file content is important 
files = {'value': (None, open(file_location, 'rb'), 'multipart/form-data')}

mp_encoder = MultipartEncoder(fields=files)

r = requests.post(
    url=url,
    data=mp_encoder,  # The MultipartEncoder is posted as data, don't use files=...!
    # The MultipartEncoder provides the content-type header with the boundary:
    headers=headers
)

PublishPBIXFile.ps1

(Install不了可以直接執行)

 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 4

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)

Authors

bart30508 (146)

Feeds

RSS / Atom

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.