美文网首页
PowerShell 处理 Analysis Server 模型

PowerShell 处理 Analysis Server 模型

作者: BI罗 | 来源:发表于2020-03-29 13:08 被阅读0次

    1.本地电脑的powershell安装所需的模块:

    安装的时候网速会很慢,可以下载v2安装提高模块的安装速度,压缩包有视频教程
    链接:百度云
    =========================================================
    模块的安装步骤:
    1.管理员运行powershell

    2.命令:Set-ExecutionPolicy
    (这是修改powershell的执行策略)
    ExecutionPolicy: Unrestricted

    1. Get-ExecutionPolicy -list
      查看修改后的执行策略

    4.根据以下的链接教程开始安装,安装模块如果提示不受信任的库 填写 Y继续

    Install-Module -Name Az.AnalysisServices
    安装Azure AnalysisServices 的模块

    Install-Module -Name SqlServer -AllowClobber
    注意:安装sqlserver模块后,
    出现问题可以去C:\Program Files\WindowsPowerShell\Modules删除SqlServer文件夹,
    再一次执行安装以下命令

    Install-Module -Name SqlServer -AllowClobber
    

    5.Get-Module SqlServer -ListAvailable
    查看sqlserver模块的版本

    1. Import-Module SqlServer
      导入模块不报错就代表成功一半

    7.Invoke-ProcessASDatabase -Server "as地址"
    不报错就ok了

    8.测试 Invoke-ASCmd -Server:as地址 -Query:xmla脚本

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

    2.相关命令语法链接:

    Invoke-ProcessASDatabase

    Invoke-ProcessASDatabase -Server "l\l2019"  -DatabaseName "test-1"  -RefreshType "Full"
    

    Invoke-ASCmd命令

    刷新分区:

    $query1 = '{
        "refresh": {
          "type": "full",
          "objects": [
            {
              "database": "test_part",
              "table": "part_tabel",
              "partition": "Partition1"
            }
          ]
        }
      }'
      $query_all = '{
        "refresh": {
          "type": "full",
          "objects": [
            {
              "database": "test_part",
              "table": "part_tabel",
              "partition": "Partition1"
            },
            {
              "database": "test_part",
              "table": "part_tabel",
              "partition": "Partition2"
            }
          ]
        }
      }'
      $ETL_date_day =(get-date).adddays(-2)| get-date -format "dd"
    
    $query=if ($ETL_date_day -eq "01") {$query_all} else {$query1}
    Invoke-ASCmd -Server:l\l2019 -Query:$query
      
    

    创建分区:

    创建分区的xmls的语句可以从ssms获得



    注意:单引号的拼接以及双引号的转义
    原句:[Query="select * from [dbo].[part_1] where date>=' " &x2&" ' "]
    $query='{
      "createOrReplace": {
        "object": {
          "database": "test_part",
          "table": "part_tabel",
          "partition": "Partition8"
        },
        "partition": {
          "name": "Partition8",
          "source": {
            "type": "m",
            "expression": [
              "let",
              "    x1=Date.AddMonths(Date.AddDays(DateTime.LocalNow(), -2),-3),//etl date",
              "    x2=Date.ToText(#date(Date.Year(x1),Date.Month(x1),1)),   //4 month ago",
              "",
              "    all = Sql.Database(\"l\\l2019\", ",
              "            \"test\",",
              "             [Query=\"select  * from [dbo].[part_1] where date>='+"'"+'\"&x2&\"'+"'"+'\"])",
              "    ",
              "in",
              "    all"
            ]
          },
          "annotations": [
            {
              "name": "QueryEditorSerialization",
              "value": [
                "<?xml version=\"1.0\" encoding=\"UTF-16\"?><Gemini xmlns=\"QueryEditorSerialization\"><AnnotationContent><![CDATA[<RSQueryCommandText>let",
                "    x1=Date.AddMonths(Date.AddDays(DateTime.LocalNow(), -2),-3),//etl date",
                "    x2=Date.ToText(#date(Date.Year(x1),Date.Month(x1),1)),   //4 month ago",
                "",
                "    all = Sql.Database(\"l\\l2019\", ",
                "            \"test\",",
                "             [Query=\"select  * from [dbo].[part_1] where date>='+"'"+'\"&x2&\"'+"'"+'\"])",
                "    ",
                "in",
                "    all</RSQueryCommandText><RSQueryCommandType>Text</RSQueryCommandType><RSQueryDesignState></RSQueryDesignState>]]></AnnotationContent></Gemini>"
              ]
            }
          ]
        }
      }
    }
    '
      Invoke-ASCmd -Server:l\l2019 -Query:$query
    
    #annotations这段不能去掉,避免其他错误
    

    Invoke-Sqlcmd

    Invoke-Sqlcmd创建视图

    $query="create view [dbo].[1_test_fact_201912] as select * from [dbo].[fact_201912]"
    Invoke-Sqlcmd -Query $query -ServerInstance "云端或本地数据库的地址" -Database "test_databse" -Username "admin" -Password "mypassword"
    

    在vscode运行powershell脚本,而脚本里面有中文,需要当前脚本的编码与电脑的powershell的编码一致,否则vscode无法运行此脚本。
    右击powershell的顶部,查看编码


    查看vscode的编码



    最后 :PowerShell 在线教程

    相关文章

      网友评论

          本文标题:PowerShell 处理 Analysis Server 模型

          本文链接:https://www.haomeiwen.com/subject/bogyuhtx.html