I am trying to make some changes to the columns that I have selected for a particular table because those columns have been removed from the source data. I discovered this when I tried to refresh. I right clicked ‘Removed other columns’ in the Applied Steps list and had to wait a little over half an hour for the Choose Columns window to open. Once it had opened I searched for the columns that I wanted to delete but couldn’t find them. Eventually it dawned on me that in the long wait for the window to open it had gone back to the source data and was now only showing me the columns that were available. I therefore had nothing to remove. All I could do was click the OK button.
First question: Is this enough? Have I now replaced the original list of fields that I want to keep with a new list, even though I didn’t actually tick or untick anything?
Second question: How long should I wait, watching those dots float across the top of the screen, before I assume that the Choose Columns action has failed.
After clicking on the OK button the Choose Columns window closed and left behind a blank area in the centre of the screen with just an error message relating to one of the fields that I wanted to delete (Expression Error: The column ‘tcrmb_COSCStatus’ of the table wasn’t found) and those dots. It’s been like that for 45 minutes now.
The data source for this is the Contacts table from Microsoft CRM Online. It usually takes over an hour to refresh my model but the Choose Columns operation isn’t downloading all the data (is it?). I thought that was to come when I click Close and Apply.
Third question: If Choose Columns window/process is broken – is there another way to fix this, i.e., to get rid of the columns that no longer exist?
Choose Columns has finally finished, after 91 minutes. It also took roughly 35 minutes to even open Choose Columns. To do nothing more complicated than remove some fields the whole operation has already taken over two hours, not including time spent reviewing the data, that’s just to open and close Choose Columns. I have three other tables to go through the same process with – then I have to apply the changes, then I have to actually refresh the data – which always takes forever.
I can (sort of) grudgingly accept that the actual refresh will take a long time. But does Choose Columns take so long?
I have an advertised 100 mbps internet connection which is currently giving me 110 mbps according to fast.com. I don't think the problem's at my end.
I think Power BI (Desktop/Pivot etc.) is a fantastic suite of products but everything to do with getting the data is excruciating. I have happier experiences getting data from other sources so I guess that the problem is between Power BI and CRM Online, which is pretty poor considering that Power BI is sold as the preferred method of reporting from CRM Online.
You use the SSMS connect your Power BI model. Then profilie Power BI Desktop Model using SQL Server Profiler using the following steps. More details, please review this article.
1. Get Power BI Desktop local port.
Run Windows Command Prompt as Administrator
Copy, paste and run the following command:
TASKLIST /FI “imagename eq msmdsrv.exe” /FI “sessionname eq
2. Connect Power BI model using “localhost:local port” for Server Type.
3. Find and tick all of the events from the list and deselect all other events, then untick “Show all events”. To trace all the events in Power BI model.
In addition, if all the events workd fine and high efficiently. I personally suggest you create a support ticket here. So that you will get more professional help.
Very interesting post and link. I think I could learn a lot.
Can I just clarify, are you recommending that I connect to my Power BI model, turn on Profiler, then run my refresh?