Expand my Community achievements bar.

Webinar: Adobe Customer Journey Analytics Product Innovations: A Quarterly Overview. Come learn for the Adobe Analytics Product team who will be covering AJO reporting, Graph-based Stitching, guided analysis for CJA, and more!
SOLVED

Adobe Analytics Connector to PowerBI

Avatar

Level 1

Hi - I am building out a Power BI dashboard by pulling in data from Adobe Analytics using the native connector that's provided. When retrieving the data by selecting the desired dimensions and measures, it's fetching only 10 records (be advised that it's not even top 10 when I cross checked). I did some Google research and understood that I need to change the M-code to 50000 which is the max capacity and changed the code to show top 50000.

 

This seems to have resolved the issue on the surface. However, when I add granularity, let's say day level granularity it's again showing me only 10 records (Not top 10 records) when compared to Adobe Analytics.

 

Basically, before changing the code, it was restricting to 10 records at an overall level. Now, it's able to show me greater than 10 records but when granularity is added it's again restricting to showing 10 records at each specified granularity. Any expert advice on this would be much appreciated on how to bypass this.

For the same level of granularity/dimension I am able to see 1,000+ records in Adobe Analytics Webpage. Below is the modified M-code for reference. Connection details change to retain privacy.

 

Modified M-code below:

let
Source = AdobeAnalytics.Cubes([HierarchicalNavigation=true]),
#"Source" = Source{[Name="Name"]}[Data],
companyname.prod.esntls = #"company group"{[Id="companyname.prod.esntls"]}[Data],
#"Added Items" = Cube.Transform(companyname.prod.esntls,
{
{Cube.ApplyParameter, "Top", {50000, "eVar 01}, "DateRange", {#date(2022, 1, 1), #date(2022, 12, 31)}},
{Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month"}},
{Cube.AddAndExpandDimensionColumn, "evar1", {"evar1"}, {"(eVar 01) Page URL"}},
{Cube.AddMeasureColumn, "Visits", "visits"}
}),
TopRows = Table.FirstN(#"Added Items", 50000)
in
#"Added Items"

 

1 Accepted Solution

Avatar

Correct answer by
Employee

I believe this 'limitation' is based on the shift in 'reporting philosophy' (which Workspace and the 2.0 report API are based on). Namely, large breakdowns (ie 50k, but 50k) should be done in Data Warehouse. 

The way breakdowns are requested has changed between the 1.4 and 2.0 report API.

It might not hurt to follow the docs below to review (in the Workspace debugger) the JSON behind the 2.0 report API. This might give you some ideas on how to work around the state limit and get more data. Please understand the reporting engine was not optimized for larger/massive breakdowns (ie 50k by 50k). Such requests could tie up a report suite allotted reporting resources. 

 

Pro Tip: Since each breakdown line item is an individual call to the reporting server, put the smallest cardinality dimension first in a breakdown.

 

https://developer.adobe.com/cja-apis/docs/endpoints/reporting/debugger/#using-the-debugger 

https://experienceleague.adobe.com/docs/analytics-learn/tutorials/apis/using-analysis-workspace-to-b...

View solution in original post

2 Replies

Avatar

Administrator

Hi @Venkata_Sai_VarunKa

 

This is a pretty complex question you've asked haha. So in this link to Microsoft Power Query guide, it states that there are some limitations with integrating PBI and AA. In it, it states:

Limitations and issues

You should be aware of the following limitations and issues associated with accessing Adobe Analytics data.

  • Adobe Analytics has a built-in limit of 50 K rows returned per API call.

That limitation in communication between the two programs may be what's holding you up. Thanks to @BrianTophamAdobe for finding this document!

Avatar

Correct answer by
Employee

I believe this 'limitation' is based on the shift in 'reporting philosophy' (which Workspace and the 2.0 report API are based on). Namely, large breakdowns (ie 50k, but 50k) should be done in Data Warehouse. 

The way breakdowns are requested has changed between the 1.4 and 2.0 report API.

It might not hurt to follow the docs below to review (in the Workspace debugger) the JSON behind the 2.0 report API. This might give you some ideas on how to work around the state limit and get more data. Please understand the reporting engine was not optimized for larger/massive breakdowns (ie 50k by 50k). Such requests could tie up a report suite allotted reporting resources. 

 

Pro Tip: Since each breakdown line item is an individual call to the reporting server, put the smallest cardinality dimension first in a breakdown.

 

https://developer.adobe.com/cja-apis/docs/endpoints/reporting/debugger/#using-the-debugger 

https://experienceleague.adobe.com/docs/analytics-learn/tutorials/apis/using-analysis-workspace-to-b...