刷新连接到 Scopus 数据库的 Power BI 数据集 [英] Refresh Power BI dataset connected to Scopus database
问题描述
我使用 Power BI 准备了一个科学计量仪表板，它通过调用 API 密钥直接连接到 Scopus 数据库.在我的 Power Bi 桌面上，它可以毫无错误地刷新数据，但在将仪表板发布到 Power Bi 网站后，它无法刷新并返回凭据错误:
I prepared a scientometric dashboard using Power BI which is connected directly to Scopus database by calling API keys. On my Power Bi desktop it can refresh data without any error but after publishing the dashboard to power bi website it can not be refreshed and returns a credential error:
上次刷新失败:2019 年 11 月 27 日星期三 12:32:39 GMT+0330(伊朗标准)时间)处理数据集中的数据时出现错误.隐藏详细信息:为 Web 源提供的凭据是无效的.(来源在 https://api.elsevier.com/content/search/scopus.)表:API-Scopus-All.簇URI:WABI-EAST-ASIA-A-PRIMARY-redirect.analysis.windows.net 活动ID:7edc8fb9-5513-465d-a35b-70cc5629d0d0 请求ID:2edb255e-20fe-d1db-6b7d-2cf1b6681fc5 时间:2019-11-27 09:02:39Z
Last refresh failed: Wed Nov 27 2019 12:32:39 GMT+0330 (Iran Standard Time) There was an error when processing the data in the dataset.Hide details Message: The credentials provided for the Web source are invalid. (Source at https://api.elsevier.com/content/search/scopus.) Table: API-Scopus-All. Cluster URI: WABI-EAST-ASIA-A-PRIMARY-redirect.analysis.windows.net Activity ID: 7edc8fb9-5513-465d-a35b-70cc5629d0d0 Request ID: 2edb255e-20fe-d1db-6b7d-2cf1b6681fc5 Time: 2019-11-27 09:02:39Z
以下代码是我在 Power BI 中的查询.此外，我在桌面上的凭据是基本"，用户名"= 我的 apikey
following code is my query in Power BI. Moreover my credential on desktop is "Basic" with "User name"= my apikey
我只从代码中删除了我的 apikey.任何人想要重现结果，都应该用 APIKEY 替换他/她的 Scopus apikey.此外，应提供对 Scopus 数据库的访问.
I only deleted my apikey from code. Anyone wants to reproduce results, should replace his/her Scopus apikey with APIKEY. Moreover access to Scopus database should be provided.
感谢您对解决凭据问题的任何帮助.谢谢
I appreciate any help for solving the credential issue. thanks
let
Source = 1000, //the total value from a total rows api?
Starts = List.Generate(()=>0, each _ < Source, each _ + 25),
#"Converted to Table" = Table.FromList(Starts, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents(
"https://api.elsevier.com/",
[
RelativePath="content/search/scopus/",
Query=
[
view="complete",
count="25",
query="AFFIL ( {Environmental Research Center} OR {Institute for Environmental Research} ) AND AFFIL ( {Tehran University of Medical Sciences} OR {Tehran University of Medical Science} ) AND AFFIL ( {Netherlands})",
apiKey="APIKEY",
limit="40",
start=""&[Column1]
]
]
))),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"search-results"}, {"Custom.search-results"}),
#"Expanded Custom.search-results" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom.search-results", {"opensearch:totalResults", "opensearch:startIndex", "opensearch:itemsPerPage", "opensearch:Query", "link", "entry"}, {"Custom.search-results.opensearch:totalResults", "Custom.search-results.opensearch:startIndex", "Custom.search-results.opensearch:itemsPerPage", "Custom.search-results.opensearch:Query", "Custom.search-results.link", "Custom.search-results.entry"}),
#"Expanded Custom.search-results.opensearch:Query" = Table.ExpandRecordColumn(#"Expanded Custom.search-results", "Custom.search-results.opensearch:Query", {"@role", "@searchTerms", "@startPage"}, {"Custom.search-results.opensearch:Query.@role", "Custom.search-results.opensearch:Query.@searchTerms", "Custom.search-results.opensearch:Query.@startPage"}),
#"Expanded Custom.search-results.link" = Table.ExpandListColumn(#"Expanded Custom.search-results.opensearch:Query", "Custom.search-results.link"),
#"Expanded Custom.search-results.link1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.link", "Custom.search-results.link", {"@_fa", "@ref", "@href", "@type"}, {"Custom.search-results.link.@_fa", "Custom.search-results.link.@ref", "Custom.search-results.link.@href", "Custom.search-results.link.@type"}),
#"Expanded Custom.search-results.entry" = Table.ExpandListColumn(#"Expanded Custom.search-results.link1", "Custom.search-results.entry"),
#"Expanded Custom.search-results.entry1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry", "Custom.search-results.entry", {"@_fa", "link", "prism:url", "dc:identifier", "eid", "dc:title", "dc:creator", "prism:publicationName", "prism:issn", "prism:eIssn", "prism:volume", "prism:pageRange", "prism:coverDate", "prism:coverDisplayDate", "prism:doi", "pii", "dc:description", "citedby-count", "affiliation", "prism:aggregationType", "subtype", "subtypeDescription", "author-count", "author", "authkeywords", "article-number", "source-id", "fund-acr", "fund-no", "fund-sponsor", "openaccess", "openaccessFlag"}, {"Custom.search-results.entry.@_fa", "Custom.search-results.entry.link", "Custom.search-results.entry.prism:url", "Custom.search-results.entry.dc:identifier", "Custom.search-results.entry.eid", "Custom.search-results.entry.dc:title", "Custom.search-results.entry.dc:creator", "Custom.search-results.entry.prism:publicationName", "Custom.search-results.entry.prism:issn", "Custom.search-results.entry.prism:eIssn", "Custom.search-results.entry.prism:volume", "Custom.search-results.entry.prism:pageRange", "Custom.search-results.entry.prism:coverDate", "Custom.search-results.entry.prism:coverDisplayDate", "Custom.search-results.entry.prism:doi", "Custom.search-results.entry.pii", "Custom.search-results.entry.dc:description", "Custom.search-results.entry.citedby-count", "Custom.search-results.entry.affiliation", "Custom.search-results.entry.prism:aggregationType", "Custom.search-results.entry.subtype", "Custom.search-results.entry.subtypeDescription", "Custom.search-results.entry.author-count", "Custom.search-results.entry.author", "Custom.search-results.entry.authkeywords", "Custom.search-results.entry.article-number", "Custom.search-results.entry.source-id", "Custom.search-results.entry.fund-acr", "Custom.search-results.entry.fund-no", "Custom.search-results.entry.fund-sponsor", "Custom.search-results.entry.openaccess", "Custom.search-results.entry.openaccessFlag"}),
#"Expanded Custom.search-results.entry.link" = Table.ExpandListColumn(#"Expanded Custom.search-results.entry1", "Custom.search-results.entry.link"),
#"Expanded Custom.search-results.entry.link1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.link", "Custom.search-results.entry.link", {"@_fa", "@ref", "@href"}, {"Custom.search-results.entry.link.@_fa", "Custom.search-results.entry.link.@ref", "Custom.search-results.entry.link.@href"}),
#"Expanded Custom.search-results.entry.affiliation" = Table.ExpandListColumn(#"Expanded Custom.search-results.entry.link1", "Custom.search-results.entry.affiliation"),
#"Expanded Custom.search-results.entry.affiliation1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.affiliation", "Custom.search-results.entry.affiliation", {"@_fa", "affiliation-url", "afid", "affilname", "affiliation-city", "affiliation-country"}, {"Custom.search-results.entry.affiliation.@_fa", "Custom.search-results.entry.affiliation.affiliation-url", "Custom.search-results.entry.affiliation.afid", "Custom.search-results.entry.affiliation.affilname", "Custom.search-results.entry.affiliation.affiliation-city", "Custom.search-results.entry.affiliation.affiliation-country"}),
#"Expanded Custom.search-results.entry.author-count" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.affiliation1", "Custom.search-results.entry.author-count", {"@limit", "@total", "$"}, {"Custom.search-results.entry.author-count.@limit", "Custom.search-results.entry.author-count.@total", "Custom.search-results.entry.author-count.$"}),
#"Expanded Custom.search-results.entry.author" = Table.ExpandListColumn(#"Expanded Custom.search-results.entry.author-count", "Custom.search-results.entry.author"),
#"Expanded Custom.search-results.entry.author1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.author", "Custom.search-results.entry.author", {"@_fa", "@seq", "author-url", "authid", "authname", "surname", "given-name", "initials", "afid"}, {"Custom.search-results.entry.author.@_fa", "Custom.search-results.entry.author.@seq", "Custom.search-results.entry.author.author-url", "Custom.search-results.entry.author.authid", "Custom.search-results.entry.author.authname", "Custom.search-results.entry.author.surname", "Custom.search-results.entry.author.given-name", "Custom.search-results.entry.author.initials", "Custom.search-results.entry.author.afid"}),
#"Expanded Custom.search-results.entry.author.afid" = Table.ExpandListColumn(#"Expanded Custom.search-results.entry.author1", "Custom.search-results.entry.author.afid"),
#"Expanded Custom.search-results.entry.author.afid1" = Table.ExpandRecordColumn(#"Expanded Custom.search-results.entry.author.afid", "Custom.search-results.entry.author.afid", {"@_fa", "$"}, {"Custom.search-results.entry.author.afid.@_fa", "Custom.search-results.entry.author.afid.$"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.search-results.entry.author.afid1",{"Column1", "Custom.search-results.opensearch:startIndex", "Custom.search-results.opensearch:itemsPerPage", "Custom.search-results.opensearch:Query.@role", "Custom.search-results.opensearch:Query.@searchTerms", "Custom.search-results.opensearch:Query.@startPage", "Custom.search-results.link.@_fa", "Custom.search-results.link.@type", "Custom.search-results.entry.@_fa", "Custom.search-results.entry.link.@_fa", "Custom.search-results.entry.link.@ref", "Custom.search-results.entry.link.@href", "Custom.search-results.entry.prism:issn", "Custom.search-results.entry.prism:eIssn", "Custom.search-results.entry.prism:volume", "Custom.search-results.entry.prism:pageRange", "Custom.search-results.entry.dc:description", "Custom.search-results.entry.affiliation.@_fa", "Custom.search-results.entry.author-count.@limit", "Custom.search-results.entry.author.@_fa", "Custom.search-results.entry.author.afid.@_fa", "Custom.search-results.entry.article-number", "Custom.search-results.entry.source-id", "Custom.search-results.link.@href"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.search-results.entry.citedby-count", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.search-results.entry.prism:doi", "DOI"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "URL", each "https://doi.org/"&[DOI]),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom1", "Custom.search-results.entry.prism:coverDate", "Custom.search-results.entry.prism:coverDate - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Custom.search-results.entry.prism:coverDate - Copy", "Date"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Custom.search-results.entry.prism:coverDate", "Cover date"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Cover date", type date}})
in
#"Changed Type3"
问题与IP客户端无关:因为如果我在 Scopus 中进行查询:(https://dev.elsevier.com/search.html#!/Scopus_Search/ScopusSearch) 生成一个 URL:(https://api.elsevier.com/content/search/scopus?query=AFFIL%20(%20%7BEnvironmental%20Research%20Center%7D%20%20OR%20%20%7BInstitute%20for%20Environmental%20Research%7D%20)%20%20AND%20%20AFFIL%20(%20)20%7BTehran%20University%20of%20Medical%20Sciences%7D%20%20OR%20%20%7BTehran%20University%20of%20Medical%20Science%7D%20)%20AND%20%20AFFIL%20(%20%7BNether)7D)&apiKey="MY-API-KEY")并将其作为网址导入 PBI，它运行良好，发布到 PBI 网页后可以毫无问题地刷新.但问题是这个简单的查询只会返回 Scopus 搜索项目的一页.我需要所有，因此我更改了上述代码，但发布到PBI后无法刷新！
The problem is not related to IP client: because if I make my query in Scopus: (https://dev.elsevier.com/search.html#!/Scopus_Search/ScopusSearch) to produce a URL: (https://api.elsevier.com/content/search/scopus?query=AFFIL%20(%20%7BEnvironmental%20Research%20Center%7D%20%20OR%20%20%7BInstitute%20for%20Environmental%20Research%7D%20)%20%20AND%20%20AFFIL%20(%20%7BTehran%20University%20of%20Medical%20Sciences%7D%20%20OR%20%20%7BTehran%20University%20of%20Medical%20Science%7D%20)%20AND%20%20AFFIL%20(%20%7BNetherlands%7D)&apiKey="MY-API-KEY") and import it as web address to PBI it works well and can be refreshed without problem after publishing to PBI web. But the problem is with this simple query only one page of Scopus searched items will be returned. I need all, and because of it I changed the code as above, but it can not be refreshed after publish to PBI!
推荐答案
该问题可能与以下事实有关:默认情况下，Scopus API 使用客户端的 IP 地址来检查它是否订阅了 Scopus.当您在 Power BI 桌面客户端上运行仪表板时，会从您客户端的 IP 地址发送对 Scopus API 的请求，如果您的客户端 IP 地址设置为访问 Scopus，您将通过 API 获得对 Scopus 数据的完全访问权限以及.但是，当您在 Power BI 网站上运行仪表板时，对 Scopus API 的请求可能是从 Power BI 服务器的 IP 地址发送的，该地址可能未设置为访问 Scopus.根据您的用例，您可以通过联系 Scopus API 支持来请求使用身份验证令牌.
The issue is probably related to the fact that by default, the Scopus API uses the client's IP address to check if it has a subscription to Scopus. When you run the dashboard on your Power BI desktop client, requests to the Scopus API are sent from your client's IP address, and if your client IP address is set up for access to Scopus, you will get full access to Scopus data through the API as well. But when you run your dashboard on the Power BI website, requests to the Scopus API are probably sent from the Power BI server's IP address, which may not be set up for access to Scopus. Depending on your use case, you may be able to request the use of an authentication token by contacting Scopus API support.
这篇关于刷新连接到 Scopus 数据库的 Power BI 数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
