OWC: Export to Excel
JackBigMac
problem using the 'Export to Excel' button in OWC. The first time the button
is clicked, the pivottable is shown well in Excel. But when I click the same
button again, Excel hangs when the first Excel instance is still open. Our
users want to hold different views on the same cube next to each other to
analyze the data, that's why we want to show the cube in different instances
of Excel.
We tried several versions of Excel to test this. Our users generally use
Office 2003 (SP1). I'm using Office 2007 myself, but I still can't show the
same cube in two different instances of Excel.
How can I show two views on the cube in Excel?
Tia and greets.
Darren Gosbell
JackB...@discussions.microsoft.com says...
Once you have the pivot table in Excel you should be able to copy and
past the pivottable to a new sheet or even copy the entire sheet. In
this way your users would be able to create a workbook with multiple
views of the same cube.
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
JackBigMac
I made a copy of the first sheet where the pivottable is on. Then when I
choose different dimensions on the second sheet, a login box appears. When I
fill in my credentials, it pops up again. I'm using the same credentials
everytime, so my password is correct.
I guess that Excel can only handle one connection to the cube or something.
When I want to show two views of the cube in one instance of Excel, the
problem arises.
Do you manage to connect to a AS2000-cube two times in Excel?
Darren Gosbell
Excel and create multiple pivot tables? It sounds like something is
going wrong with the connection string.
I have actually written a small sample application in Excel which keeps
the filters for multiple pivot table in the one book synchronized.
http://geekswithblogs.net/darrengosbell/archive/2005/12/14/63085.aspx
In the sample I have a workbook with 3 pivot tables to the same cube, 2
are on the first sheet and the third is on a second sheet.
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <98BFD7DB-3968-49D1...@microsoft.com>,
JackB...@discussions.microsoft.com says...
JackBigMac
problem occurs. From the moment I create the second cube, the login box keeps
appearing whether I fill in the right credentials or not. The login box
doesn't pop up when I create only one pivottable. As you pointed out, you are
able to create multiple pivottables which connect to the same cube. So the
problem is probably not Excel, but maybe the way I connect to the cube or the
way security is configured within AS2000.
This is the content of my oqy (Microsoft Query) file which is the data
source for my pivottable:
QueryType=OLEDB
Version=1
CommandType=Cube
Connection=Provider=MSOLAP.2;Data Source=https://MyServer/olap;Initial
Catalog=PDW;Client Cache Size=25;Auto Synch Period=10000
CommandText=MyCube
In Analysis Manager on the server, several roles are configured. The user
which I use to login has a membership on one of this roles. The value for the
'Enforce on'-field is 'Server', 'Drillthrough' is set to 'Disabled'.
Are the settings for the query or the roles in order?
Tia and greets.
Darren Gosbell
is that my connections are via TCP and yours are via HTTPS so that is
probably where your issue lies.
From the way you are talking would I be correct in guessing that you
have setup your virtual directory with basic authentication?
I'm guessing there may be IIS related issues involved here. Have you
checked out the IIS logs and/or the event logs on the computer running
IIS?
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <B51998CF-331B-4275...@microsoft.com>,
JackB...@discussions.microsoft.com says...
JackBigMac
Directory Security of this virtual directory, 'Integrated Windows
Authentication' and 'Digest authentication' are turned on.
I noticed that also the first time I export the cube to Excel via OWC, a
logon box pops up. This shouldn't be the case at all, I guess. So I'd want to
focus on this first. Can you confirm that this logon box should not popup?
I also checked the eventlog. In the Security section, I noticed that a logon
with my user credentials is immediately followed by a logoff. Logon and
Logoff entries are added to the event viewer as long as I keep connected to
the cube.
This is a extract of the IIS logfile:
2006-06-27 08:18:06 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.150
Light+DCube 401 1 0
2006-06-27 08:18:06 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.150
Light+DCube 401 1 0
2006-06-27 08:18:07 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.150 Light+DCube 200 0 0
2006-06-27 08:18:07 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.150
Light+DCube 401 1 0
2006-06-27 08:18:07 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.150 Light+DCube 200 0 0
2006-06-27 08:18:07 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.150
Light+DCube 401 1 0
2006-06-27 08:18:07 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.150 Light+DCube 200 0 0
2006-06-27 08:18:07 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.150 Light+DCube 200 0 0
2006-06-27 08:18:13 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.31
Light+DCube 401 1 0
2006-06-27 08:18:13 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.31 Light+DCube 200 0 0
2006-06-27 08:18:13 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.31
Light+DCube 401 1 0
2006-06-27 08:18:13 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.31 Light+DCube 200 0 0
2006-06-27 08:18:17 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.150
Light+DCube 401 1 0
2006-06-27 08:18:17 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.150
Light+DCube 401 1 0
2006-06-27 08:18:17 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.150 Light+DCube 200 0 0
2006-06-27 08:18:17 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.150
Light+DCube 401 1 0
2006-06-27 08:18:17 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.150 Light+DCube 200 0 0
2006-06-27 08:18:17 10.180.10.132 POST /OLAP/msolap.asp - 443 - 10.33.16.150
Light+DCube 401 1 0
2006-06-27 08:18:17 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.150 Light+DCube 200 0 0
2006-06-27 08:18:17 10.180.10.132 POST /OLAP/msolap.asp - 443 DOMAIN\JohnDoe
10.33.16.150 Light+DCube 200 0 0
As you can see, there are some 401 HTTP errorcodes in the log. But also some
200 HTTP codes. So I'm wondering where the 401's come from.
Greets.
JackBigMac
You can also open only one connection per process when using HTTP to connect
to Analysis Services, because the data pump library only supports one HTTP
session per process. If you attempt to open a second HTTP session for the
same process, the session cookies for the first session are dropped, in
effect closing the first connection to open the second connection.
How is it possible that you manage two open the same cube twice in Excel?
Are you not connecting via msolap.asp?
The article is located at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_datapump.asp.
Greets
Darren Gosbell
connectivity does not have any issue with multiple pivot tables.
I can't test your exact configuration from my laptop as it cannot run
digest authentication. But just running windows authentication works
with multiple pivot tables. I suspect it is just seamlessly re-
connecting.
The login box is most likely popping up because of the digest
authentication option. But that leads me to ask - if you don't want to
be prompted for a username/password and you can use integrated windows
security, why are you using a http connection? Have you considered just
connect directly to the AS server? It should perform better and it will
only use integrated security, so you never get prompted for credentials.
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <FE966682-54C3-4010...@microsoft.com>,
JackB...@discussions.microsoft.com says...
Arvind.C...@gmail.com
I connect to cube created in AS2005 through Excel 2003 to pivot table.
I need to restrict cube access through roles to which usernames(windows
users) are assigned.
though i created diffrents roles for different users and created data
restrictions on dimensions in analysis services 2005, when i try to
access cube via analysis services it is unable to detect the current
user and apply restrictions accordingly.
Simillarly No restrictions are applied when i access through
Excel-2003.
Can somebody suggest any solution.
Regards
Arvindc