用QLIK从Anaplan提取数据

[#1]

Qlik has twodata analytics tools—the older one, QlikView, and the modern one, Qlik Sense. They both share similar scripting and the same data files. Data extracted with QlikView can be used by Qlik Sense and vice versa. Many enterprises still rely on QlikView and the QlikView Publisher to handle data reloads while they use the modern capabilities of Qlik Sense for the presentation layer. In this article, we will describe how to easily extract Anaplan data with QlikView.

您可能知道，在new Anaplan REST API 2.0the authentication process can be done using a generated token. This makes the automation process quick and easy since you don’t need to verify your username and password at every step along the way. The process we will follow to extract Anaplan data is described below:

Prototyping with Postman

1. 我们喜欢在将其部署在QLIK之前，然后在名为Postman的工具中测试连接。这是一种一般的好实践和生产力黑客，无论您将连接部署到哪种工具，我都建议您使用。第一步是从Anaplan获得令牌。我们这样做的方式是发送一个“邮政”命令该网址：https://auth.anaplan.com/token/authenticate。To make this work, you only need a valid user and password, which you’ll add in theAuthorization标签。令牌将位于“tokenvalue“ 场地。另一个好的做法是拥有专用的数据集成/提取帐户。通过不使用个人帐户，您只能在您希望扩展自动化过程时控制使用什么权利并更改它们。
2. 下一步是获取Anaplan中设置的导出操作的ID。请注意，为了从Anaplan提取数据，您需要首先设置导出操作。我们不会描述在此处如何完成此事，因为该主题有很多材料。回到获得导出操作ID，您首先需要工作区和模型ID。当您在Anaplan打开模型时，您可以轻松地通过URL获取它们。然后，您需要发送一个”GET”命令该网址：https://api.anaplan.com/2/0/workspaces/{workspaceID}/models/{model ID}/exports。这样，您将检索Anaplan中已经设置的导出操作的ID。对于此步骤，您还需要在标题tab called “Authorization”。Here you’ll add the token you retrieved in the first step. Make sure to add “AnaplanAuthToken“在它的面前，否则电话将行不通。
3. 装备出口操作ID的最后一步, you are now ready to run the actual export action. You’ll need to send a “邮政”命令该网址：https://api.anaplan.com/2/0/workspaces/{workspaceID}/models/{model ID}/exports/{export action ID}/tasks。Again, you’ll need to add a few fields in the标题标签。第一个是“Authorization” field you are already familiar with, and the second one is “内容类型”。通常，邮递员有一个“内容类型”字段，但是您无法对其进行编辑，因此第二次再次定义它，让您覆盖值。将这个新字段中的值设置为“application/json”。
4. 下一步是完全可选的，因为您要导出的文件具有与上一步中触发的导出操作相同的名称和ID。但是，如果您想检查一下，您需要发送一个“GET”命令该网址：https://api.anaplan.com/2/0/workspaces/{workspaceid}/型号/{模型ID}/文件。Don’t forget to add the “Authorization” field with the token again. When this is run, you’ll most likely get a bunch of files with various file extensions. You’ll want to scroll down to the bottom and find the one that matches your export action by name and ID. You’ll be able to see how many chunks the file has. If it’s more than one, you’ll need to do the next step. If not, you can skip it since the ID of the file chunk will always be 0.
5. 因此，如果您的文件有多个块，则必须获取其ID。为此，您需要运行另一个”GET”用以下URL命令：https://api.anaplan.com/2/0/workspaces/{workspaceid}/型号/{模型ID}/文件/{file ID}/chunks。Same as before, you’ll need to add the “Authorization”标题标签。
   
6. Finally, we’ll check what kind of data we’ll be getting when we extract the file. For this, you need to run a “GET” command like this:https://api.anaplan.com/2/0/workspaces/{workspaceid}/models/{model id}/files/{file id}/chunks/{块ID}。Also, in the Headers tab, you’ll need to add the “Authorization”字段带有令牌值。但是，这次您必须添加另一个字段，也称为“接受”。As with “内容类型” in step 3, this “接受”字段已经由Postman定义，但不能编辑。因此，当您现在定义它时，您会给它一个值“应用/八位字节”。When you run the command successfully, you’ll get a table with data.
   

Automating the Process in QlikView

如果您一直在关注Postman测试，则在QlikView中进行该过程时，您将拥有所有ID。因此，让我们跳入。

1. 是前提the QlikView REST Connector installed. If you haven’t installed it yet, you can download it from这里。Then, start by creating a new app in QlikView. Pressctrl + e或从“文件”下拉菜单中选择“”Edit Script”。现在，当您在脚本编辑器中时，您可以在左下角看到一堆选项卡。在“数据”选项卡下，您应该可以选择从下拉菜单连接到多个数据库。选择REST连接器，然后单击“Connect…”。将弹出一个新窗口，您必须填写URL（https://auth.anaplan.com/token/authenticate) and your credentials under the “Authorization” section. Click “好的”，应该建立连接。现在，您必须引入提取的信息，因此再次从左下角的“数据”选项卡中再次单击“选择…” button. From there, expand the “根”路径并打勾“tokenInfo” one. Then press “好的” to insert the select statement created by this wizard.
   
2. 接下来，您将创建一个可存储令牌的变量。因此，在脚本的末尾，编写以下代码：LET vToken=’AnaplanAuthToken ‘&Peek(‘tokenValue’, -1, ‘tokenInfo’);这不仅会存储令牌，还会添加急需的“AnaplanAuthToken”绳子在它的前面。
   
   
3. If you followed the Postman prototyping process, then you’ll already have the IDs you need to automate the process with fewer steps. So, next, you’ll either have to get the list of export actions, or you can move straight to the export action itself if you have the ID. In this article, we will be moving on to the latter. So now you’ll need to create a new connection the same way you did it in step 1. Obviously, the URL will be different:https://api.anaplan.com/2/0/workspaces/{workspaceID}/models/{model ID}/exports/{export action ID}/tasks，但是这次您需要用本文填写“请求正文”字段 -{“localName”: “en_US”}。您还需要将“身份验证”部分保留在其默认情况下（匿名）。然后，只需向下滚动一点，在“附加请求参数”部分下，您需要添加以下内容查询标题:
   –Name: Authorization , Value: *the current token* (don’t put the variable here, since QlikView won’t take it)
   –名称：内容类型，值：应用程序/JSON
   Click “好的”并应创建连接。
   
   
4. 同样，您将必须单击“选择…”左下角的按钮，并添加新表格。
   
   Then you’ll need to make a small change in the connection script in order to make the process automated. We’ll have to insert our token variable in place of the token we used to make the connection.
   
   
5. Again, if you followed the Postman prototyping process, then you’ll already have the file IDs and the chunk IDs. Even if you didn’t, you probably know them, since the file name and ID is identical to the export action name and ID, and most likely your file will only have one chunk—in which case, it’s ID will be 0. You can refer to the Postman section if you wish to double-check those values. So, all that’s left is to make the final connection through the database connector. Click “Connect…”并适当填写参数。这里要注意的一件事是，与前两个连接不同，这将是一个GET, not a邮政action. In theQuery Headers部分，您必须再次使用完整的令牌，而不是使用“Authorization“ 场地。但是，这次不是添加“内容类型”字段，而是添加一个“接受”字段的值应用/八位字节。
   
   
6. Finally, you’ll have to add the file/s to QlikView by clicking the “选择…” button once again and selecting the new tables available. Click “好的” to add the script. Make sure you change the connection code again by removing the token and adding your variable, as we did in step 4. And voilà! You’ve added the Anaplan extract to QlikView and automated the process along the way. You can now store the table in a .QVD format and use it in your QlikView or Qlik Sense apps.
   
   Please keep in mind that the approach described assumes that all your IDs are static! If you want to change the model or want to use a different export action, then you'll have to change the respective IDs in the chain!
   Also, if an action is deleted and recreated with the same name, just note that the ID will still be different and will need to be switched. Keep a close eye on the model and be sure to be updated about any changes so that the automation can keep working smoothly!

Have fun creating your analytics dashboards!