刷新连接到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（伊朗标准
时间）处理数据集中的数据时发生错误隐藏
详细信息消息：The为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％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）
并将其作为网址导入到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桌面客户端上运行仪表板时，对Scopus API的请求是从客户端的IP地址发送的，并且如果将您的客户端IP地址设置为可访问Scopus，则您将通过API获得对Scopus数据的完全访问权限也一样但是，当您在Power BI网站上运行仪表板时，对Scopus API的请求可能是从Power BI服务器的IP地址发送的，该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屋！
