Composite Model Publishing after Dec 2020. SSAS + Excel Sheet

[AlexD43]

Hi All

I've had an existing report working fine but I wanted to add some specific targets per lead channel, so I created an Excel Sheet in a secure but shared location in a company folder, accessed through VPN as WFH) with 1 page which looks similar to the below.

Campaign	Conversion Name	Conversion Target	RPC Target
Test1	Mr Testy	30%	£62

This data was brought in to the existing table as a column (Conversion Name), then a couple of calcs all of which pull through fine into the desktop app using a combination of lookupvalue (Sheet1 'Conversion Target', Sheet 1 'Campaign'), and selectedvalue (SSAS Model 'CampaignName').
This information all appears fine on the Desktop App, however when I try to publish to 'My Workspace', in the same location as the same report used to publish I get good old 'Computer Says No'...

I've tried to schedule a reset, but it only lets me try to manage the data warehouse, which I'm not the administrator for.

Other reports are publishing fine using only SSAS source so it must be my arrangement for my Excel file location.

What do I need for this, in a way that keeps the Excel table secure?

Cheers

[AlexD43]

I've tried both and changed the connection type.
I think we have some funky permissions in our company-managed 365
rules preventing us giving open access; our PBI accounts are separately arranged as most employees don't need it.

I've actually arranged for the data to be copied into the PBIx file as a manual table which is now working, but ideally moving it to Excel at some point to allow someone else to update the table would be better so this is still relevant learning for me; just slightly lower priority as I've published what I needed to with a different method.

[parry2k]

@AlexD43 where is your excel file located? Can you put it on sharepoint/one drive for business and use it from there.

[AlexD43]

Hi. The file was on a local folder on a VPN network.

However when I move it to a company sharepointlocation I get this; which to me suggests our account default permission is not to share with external services, even when logged in with company credentials ???

Cheers

[v-yiruan-msft · Accepted solution]

Hi @AlexD43 ,

If I understand correctly, you have stored the excel file that was originally stored in on premises in SharePoint location, right? Have you updated the connection method of excel file (from connect to Excel to connect to Sharepoint) in the report? You can refer to the solution in the following threads to solve your problem.

SharePoint List Access to resource is forbidden

n:Expression.Error: Access to the resource is forbidden. (SharePoint & Unpivot)

Best Regards