Solved

Permission Errors while deploying SSAS Cube in sql server 2008 r2

Posted on 2016-11-08
20
18 Views
Last Modified: 2016-11-11
Hi, I have created SSAS cube from my Data source View which is calling a view from my Database. I am now trying to deploy the Cube on local but getting permissions issue . It says login failed for user.

Can any one please let me know if i need any special permissions to deploy a cube and what are they ?

Many Thanks
0
Comment
Question by:gvamsimba
  • 11
  • 9
20 Comments
 
LVL 18

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
ID: 41879888
Have you added Windows login or SQL login?

You should use Windows account because Analysis Services runs on Windows. SSAS Service account(impersonation = service account)
2 things-
1. Database user account.
2. Impersonation Information - This the Windows credential/user account under which Analysis Services will be running when it tries to connect to that SQL Server.

Read more here - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/62e56047-442e-4d6e-9aa3-a3ae6a52e206/impersonation-mode-connecting-to-datasource?forum=sqlanalysisservices

<<Read excellent comment by Vincent Rainardi>>
0
 

Author Comment

by:gvamsimba
ID: 41880368
Hi Pawan, thanks this worked.

But one quick quesiton. how can I bring all the columns in my sql server view to the CUBE so that the user can connect via Excel to see the columns as is without any further analysis.
Many Thanks
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41880372
You can use SELECT * instead of SELECT col1,col2..etc in the View


CREATE VIEW [ViewName] AS
SELECT * FROM YourTableName


Usage - SELECT * FROM ViewName
0
 

Author Comment

by:gvamsimba
ID: 41880377
Pawan, I already have a view defined in the Database. Now I have created a data source in SSAS to this DB and also created a Data source View which is calling this view.

Now  I have created a new cube calling the above DSV which pulled all my columns in the view but  in the dimensions it only got the primary key. so After deploying the cube locally and when i try to connect to the cube from excel, I am only getting one column which is that primary key. How will i get all the remaining columns in excel ?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41880383
Can you check the query of the data source view?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41880391
Try this..

Open excel - > New Query -> From database -> From SSAS database -> Select View/Table -> Test Connection -> Ok,  You should be done.
0
 

Author Comment

by:gvamsimba
ID: 41880404
hi tats what my problem is. when I connect to cube via excel its only bringing up one column which is the Primary key. I then checked the dimnetion in the cube and I can see that it has got only one attribute which is this primary key. Do you think this might be the reason why excel is bringing only this column ? how should i pull the other columns into excel ?

Thanks
0
 
LVL 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 500 total points
ID: 41880425
Try below--

Use the steps below from <<>https://msdn.microsoft.com/en-us/library/cc280660.aspx>

1.      Open Dimension Designer for the dimension table. Double-click the Product dimension in Solution Explorer.
2.      In the Attributes pane, notice the Key attribute that was created by the Cube Wizard.
3.      On the toolbar of the Dimension Structure tab, make sure the Zoom icon to view the tables in the Data Source View pane is set at 100 percent.
4.      Drag the following columns from the Product table in the Data Source View pane to the Attributes pane: SELECT columns..
5.      On the File menu, click Save All.
0
 

Author Comment

by:gvamsimba
ID: 41880659
Hi Pawan, ok I have done that and now I can see the additional columns in  Excel but once I dragged the second column to row labels, the values are coming in the same column instead of in the next column. see below; Am I doing something wrong here ?

P00001DTSC
Pending
P00001FTSC
Preliminary
P00001RAWA
Pending
P00001RMEN
Approved
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41880691
Are you dragging the columns from the table in the Data Source View pane to the Attributes pane?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:gvamsimba
ID: 41880717
yes tats exactly what I have done and also deployed the cube . But the issue is now in excel. When I connect excel to the cube and drag the first column, its coming properly in the first column. But when I drag second column, the values are coming underneath the second column in excel. tat is the issue.

P00001DTSC
Pending
P00001FTSC
Preliminary
P00001RAWA
Pending
P00001RMEN
Approved
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41880873
I can see multiple columns. Check out 2 sheets from the excel attached.

It is not allowing me to attach the ssas project for you. Message me your email, I shall email you that.

Hope it helps.
Book1.xlsx
0
 

Author Comment

by:gvamsimba
ID: 41881021
Hi in my case, there are no measures, just dimensions. This is not a real cube. They just want to use cube to connect via excel to sort out some permissions issue.

P00001DTSC   Pending
0
 

Author Comment

by:gvamsimba
ID: 41882159
Hi Pawan, how do message you my email ?
0
 

Author Comment

by:gvamsimba
ID: 41882161
how do I message my email ?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41882176
click on my name , there you will get a message button. click on it and you will send the email.
0
 

Author Comment

by:gvamsimba
ID: 41882187
done.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41882212
done. SSAS project. Excel file is attached in my last comment, Thank you!
0
 

Author Comment

by:gvamsimba
ID: 41884002
Hi Pawan,

What we found out is that the reason for Excel hanging in my cube is because its not a PROPER cube as the users DO NOT want do any analysis. they just want to see those 10 columns in excel. What we are doing is just bring the 10 columns from the sql server db view straight into DSV and build a cube straight from that.
Then we have one dimension and the remaining 10 columns as the attributes. Since there are no proper measures build up in this cube, this is causing performance issues.

Now we r thinking either to build SSRS reports and give the report manager link to the users  OR build a proper cube in case our users want to do any multidimensional anlaysis.

Thanks a lot for your help.

Thanks
0
 

Author Closing Comment

by:gvamsimba
ID: 41884003
good.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Find out what the Office 365 disclaimer function is, why you would use it and its limited ability to create Office 365 signatures.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now