Excel Power QueryとMySQLを使用してレポートを自動化することの落とし穴

みなさんこんにちは。

新しい2016年が到来しました。つまり、退屈な機械作業を簡素化するためにツールを更新するときです。 分析部門、マーケティング部門、営業部門は、レポートの更新時に次のような困難に直面することがよくあります。

1.複数のソースからデータを収集する必要があります。

2.レポートはExcelでコンパイルされるため、処理されるデータの量に大きな制限が課せられます。

3.事前に構成されたアンロード開発者に変更を加えることは、通常最速ではありません。



レポートを毎週または毎日更新する必要がある場合、この手順はほとんどの患者にとっても非常にストレスになります。 Excel Power Queryアドインを使用し、MySQLにデータを書き込むことで、ほとんどのレポートの更新を「更新」ボタンをクリックするだけに減らすことができます。

1.任意の数のソースからのデータが、SQLクエリを介して通常のExcelテーブルにインポートされます。

2.大規模なデータベースからでも、データのごく一部のみをExcelに書き込むことができます（たとえば、必要な列のみでグループ化した目的の日付範囲の合計）。

3. SQLクエリを変更するだけで、レポートを変更できます。 次に、標準のExcelツールを使用して目的のレポートを生成します。



この記事では、（Yandex Metricsからすべてのキーワードの統計をダウンロードする例を使用して）簡単なMySQLデータベースを構成して自動的に入力し、Power Queryアドインを使用して1つのボタンでExcelのレポートを更新する方法を示します。 Power Queryには、SQLクエリ（特に動的クエリ）を扱う際の非常に奇妙な機能があります。これについては、記事の後半で説明します。





MySQL（または他の一般的なデータベース）の選択は非常に明白です-無料で、比較的単純で、技術的なトリックなしでかなり大きなデータベースを操作する能力。 例として、Amazon Webサービスを使用します：安価（ほとんどの場合、使用するインスタンスは12か月間無料です）。



それでは、始めましょう（既製のデータを持つデータベースが既にある場合は、すぐにExcelのセクションに進むことができます）：

1. AWSに登録済みで（まだアカウントがない場合）、最も単純なt2.microインスタンスを起動し、SSHにアクセスします。 最後の投稿habrahabr.ru/post/265383に簡単な説明があります。 Amazon Linux AMIのリストに最初のインスタンスオプションが必要なことに注意してください。 必要なポートでインスタンスへのアクセスを許可するルールを設定する必要があります。





セキュリティ上の理由から、IPアドレスに制限を設定することをお勧めします。 動的IPがある場合、これは問題のあるオプションです。 また、IPによるMYSQLへのアクセスを制限すると、Excelでエラーが発生する場合があります。 IPを設定すると、すべてが機能します。



2.ドキュメントdocs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-ug.pdfで説明されているチームを連続して実行します。 「チュートリアル：Amazon LinuxにLAMP Webサーバーをインストールする」の章が必要です。 「sudo mysql_secure_installation」コマンドを実行するときに入力したパスワードを覚えておいてください。 便宜上、この章の最後に記載されているようにphpMyAdminをインストールします。 ドキュメントから「sudo sed -i -e 's / 127.0.0.1 / your_ip_address / g' / etc / ht tpd / conf.d / phpMyAdmin.conf」という行をコピーする場合、「httpd »余分なスペースが表示されます。



これらの手順の後、ページで次のページが開きます。





3.セットアップ中に入力されたrootユーザーとパスワードの下に移動します。 「外部」から（つまり、Excelから）データベースにアクセスするには、root以外のユーザーが必要です。 Users-> Add userメニューのphpMyAdminインターフェースで取得します。 statsユーザーを追加し、パスワードを設定し、SELECTおよびINSERT特権を割り当てます。 合計：





4.次に、データデータベースを作成します。





5.この例では、Yandex Metricsのキーワードの訪問の統計でベースを埋めます。 これを行うには、seoテーブルを作成します（id列のA_I（自動インクリメント）オプションをオンにする必要があることに注意してください）：





6. Yandex Metricsからキーワードの統計を取得するには、次のスクリプトを使用できます。 パラメーターとして、アップロードの開始日と終了日（$ startDateおよび$ endDate変数）、認証トークン（コードには取得方法の説明が含まれます）、統計情報を取得するカウンター番号、およびデータベースパラメーターを指定する必要があります：インスタンスID、ログイン（ 「統計」）、パスワード、データベース名（「データ」）があります。 このコードをインスタンスのルートフォルダーにコピーし、コマンド「php seo.php」を実行します。







データベースへの接続中にエラーが発生した場合、コンソールにエラーが表示され、実行が中断されます。 実行が成功した場合、選択した期間のキーワードの統計を取得します。





素晴らしい、データを受信しました。 Excelでそれらを取得する方法を見てみましょう。



Power Queryを使用してExcelでデータをアンロードする

Power Queryは、データをアップロードするExcelの機能を拡張するアドインです。 こちらからダウンロードできますwww.microsoft.com/en-us/download/details.aspx?id=39379 MySQLを使用するには、MySQLコネクタとVisual Studio（ディストリビューションからインストールするときに提供される）が必要になる場合があります。



1.インストール後、MySQLを選択します。





2.ベースとして、インスタンスのID（スクリプトの場合）ec2 -.... compute.amazonaws.comを指定します。 データベースデータ。 ログインを入力するには、「データベース」を選択します。





3.開いたウィンドウで、seoテーブルをダブルクリックして以下を取得します。





このウィンドウでは、列と行数を変更してクエリを管理できます。 データベースが小さい場合、機能します。 ただし、データサイズが20 MBを超える場合、ほとんどのコンピューターのExcelは、そのような要求から単にハングします。 さらに、リクエストの日付またはその他のパラメーターを変更すると便利です。



Power Queryの動的クエリは、組み込みのM言語msdn.microsoft.com/en-us/library/mt253322.aspxを使用して実行できますが、クエリはその中のパラメーターを変更するという点で非常に不安定です。 要求が「一定」のままになるように、次のことを行います。



1.最初に、必要なパラメーターを示すテーブルを作成します。 この例では、これはアンロードの日付です。 Excelは、セルの形式をテストとして設定することをお勧めします。Excelは、適切と思われるセルの形式を変更するのが好きだからです。





2.単純にこのテーブルを複製するPower Queryクエリ「From the table」を作成します。





3.クエリオプションで、必ず2番目の列の形式をテキストとして指定します。そうしないと、後続のSQLクエリが正しくなくなります。 次に、「閉じるとダウンロード」をクリックします。





合計で、通常のテーブルのPower Queryクエリを受け取り、そこからアンロードの開始と終了の値を取得します。



SQLクエリを作成するには、1つのオプションを無効にする必要があります。[設定と設定]-> [リクエストパラメータ]-> [プライバシー]に移動し、[プライバシーレベルを無視してパフォーマンスを改善する]を選択します。 OKをクリックします。





4.ここで、ポイント3からのテーブルの値を期間の開始と終了として示すデータベースに要求を行います。再び、Power Queryでデータベースに接続し、メニューの[詳細エディター]をクリックします。





たとえば、「2015」を含むキーワードをもたらした訪問の合計を取得します。 Mでは、クエリは次のようになります。

let

Source = MySQL.Database("ec2-....compute.amazonaws.com", "data", [Query="select sum(visits) from seo where startDate>='"&Text.From(1{0}[])&"' and endDate<='"&Text.From(1{1}[])&"' and query like '%2015%';"])

in

Source









startDateおよびendDateパラメーターには、ポイント3からのテーブルの値が示されます。「データベースに対するこの独自のクエリを完了するにはアクセス許可が必要です」というプロンプトが表示されたら、「アクセス許可の編集」をクリックし、すべてのパラメーターが正しくプルアップされていることを確認して、クエリを実行します。 これで、SQLクエリから受信した応答を、使い慣れた形式の通常のExcel式で処理できます。



5.重要！ 次回アンロードを更新するときは、次の方法でこれを行う必要があります（何らかの理由でエラーが発生する場合）。

-段落1から表の日付を変更する

-[データ]-> [接続]メニューに移動し、[すべて更新]をクリックします。





この場合、すべてのリクエストが正しく実行され、レポートが自動的に更新されます。 全体として、レポートを更新するには、クエリパラメータを変更し、[すべて更新]をクリックするだけです。