Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

Permission Errors while deploying SSAS Cube in sql server 2008 r2

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
gvamsimba
Asked:
gvamsimba
  • 11
  • 9
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
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
 
gvamsimbaAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
gvamsimbaAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Can you check the query of the data source view?
0
 
Pawan KumarDatabase ExpertCommented:
Try this..

Open excel - > New Query -> From database -> From SSAS database -> Select View/Table -> Test Connection -> Ok,  You should be done.
0
 
gvamsimbaAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
gvamsimbaAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Are you dragging the columns from the table in the Data Source View pane to the Attributes pane?
0
 
gvamsimbaAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
gvamsimbaAuthor Commented:
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
 
gvamsimbaAuthor Commented:
Hi Pawan, how do message you my email ?
0
 
gvamsimbaAuthor Commented:
how do I message my email ?
0
 
Pawan KumarDatabase ExpertCommented:
click on my name , there you will get a message button. click on it and you will send the email.
0
 
gvamsimbaAuthor Commented:
done.
0
 
Pawan KumarDatabase ExpertCommented:
done. SSAS project. Excel file is attached in my last comment, Thank you!
0
 
gvamsimbaAuthor Commented:
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
 
gvamsimbaAuthor Commented:
good.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now