NONEMPTYCROSSJOIN : how does it work with linked measure groups ?

Hi,

I am experiencing a strange behaviour with both NONEMPTYCROSSJOIN() and NON(CROSSJOIN()) Mdx statements when working with linked measure groups.

Bascially, I have created a very simple "regular" MOLAP cube on SSAS2005 with one fact table and 3 dimensions. I have one single Measure Group for this cube.

Now, if I create an empty cube (no data source view) and add the measure group of my first cube as a Linked Measure Group, it seems like the Mdx function NonEmptyCrossJoin() never returns anything whereas it does on the underlying cube.

Same problem occurs when using NonEmpty(CrossJoin()).

Is there anything I need to parameter to have the NonEmpty baheviour work correclty on the linked measure group ? I've read there's a "NonEmpty behaviour" parameter for calculated members, but I'm not even using calculated measures.

Is such a behaviour a known issue ?

Do you need a more detailed example? if so, on which database ?

To give some backgroung, I have first noticed this problem after migrating a virtual cube from SSAS 2000.

Many thanks in advance

Rgds,

Francois

I eventually found an answer to this.

There was obviously some type of confusion in the migration of the virtual cube (which is based in SSAS2000 on 3 physical cubes) because not all of the sub cubes' dimensions where used.

I have recreated by linked measure groups manually, removed the unused dimension and the nonemptycrossjoin works fine now.

Hope that helps anyone having the same type of problem.

- F.

Non-Admin user can't create a linked report in My Reports...

A Non-Admin user can't create a linked report in his My Reports folder.
Reporting Services is set up with My Reports enabled with the default tasks
assigned to the My Reports role (this includes Create linked reports). This
same user can create sub-folders but not linked reports.
----
aspnet_wp!library!164!10/07/2004-17:42:44:: i INFO: Call to
CreateLinkedReport( '20041007 1742 SimpleReport', '/My
Reports/Submitted/Test', '/Standard Reports/Test/SimpleReport',
'Microsoft.ReportingServices.Library.Soap.Property[]' )
aspnet_wp!library!164!10/07/2004-17:42:44:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
permissions granted to user 'LABTM\RSFinancialAnalyst' are insufficient for
performing this operation., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
permissions granted to user 'LABTM\RSFinancialAnalyst' are insufficient for
performing this operation.Does the user have Read Properties permission to the report /Standard
Reports/Test/SimpleReport?
-Lukasz|||I discovered the problem... The Browser Role is not associated with the
"Create linked reports" task by default. Once I assigned the Create linked
reports task to the Browser Role the non-admin user could create linked
reports.
Thanks for the hint Lukasz.
Garry Lenz
"Garry Lenz" <glenz@.thoughtmill.com> wrote in message
news:%231hAadLrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> A Non-Admin user can't create a linked report in his My Reports folder.
> Reporting Services is set up with My Reports enabled with the default
tasks
> assigned to the My Reports role (this includes Create linked reports).
This
> same user can create sub-folders but not linked reports.
> ----
> aspnet_wp!library!164!10/07/2004-17:42:44:: i INFO: Call to
> CreateLinkedReport( '20041007 1742 SimpleReport', '/My
> Reports/Submitted/Test', '/Standard Reports/Test/SimpleReport',
> 'Microsoft.ReportingServices.Library.Soap.Property[]' )
> aspnet_wp!library!164!10/07/2004-17:42:44:: e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The
> permissions granted to user 'LABTM\RSFinancialAnalyst' are insufficient
for
> performing this operation., ;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The
> permissions granted to user 'LABTM\RSFinancialAnalyst' are insufficient
for
> performing this operation.
>

NON DOMAIN AND DOMAIN CONNECTION

Hi all,

it happen to me a strange problem:

i have a mdb file (in Access 2K) with SQL Server 2K linked tables who
runs on a workstation which is on a different domain that the SQL
Server. It works.

If i create a mdb file from a workstation which is a the domain of the
SQL Server and then i run it a my non-domain workstation i have error
message:

Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection

But if i reattached my tables it works.

If someone have an idea...

PS: same ODBC on both machinesRavieR (principle@.caramail.com) writes:
> it happen to me a strange problem:
> i have a mdb file (in Access 2K) with SQL Server 2K linked tables who
> runs on a workstation which is on a different domain that the SQL
> Server. It works.
> If i create a mdb file from a workstation which is a the domain of the
> SQL Server and then i run it a my non-domain workstation i have error
> message:
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection

I'm nor sure that I followed the confiuguration exactly, but trusted
connections in workgroups is usually an iffy thing. For instance, at
home I have a non-domain workstation, on which I run a non-domain
virtual machine where I have the beta version of SQL 2005. From the
host machine I cannot conenct with trusted connection to SQL2005 on
the virtual machine, but not the other way round.

I think you need to be logged with the same username on both, and the
two users need to have the same password.

You may be better off with SQL authentication in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Non- Admin connection

I am testing an Access Database on network workstations with linked tables
to SQL Server. I set up a DSN and have no problem logged in as myself with
Admin rights to the server. But when I login as a user with no admin rights
I am denied access with this message:
Connection failed:
SQL Server State '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'dayoubunc1\Sales'.
The network server is Windows 2003 Server, the workstation is Windows 2000
Pro and the SQL Server is a runtime version which came with a Veratis backup
system.
The purpose of moving the tables to SQL Server is security. A user can
steal an Access backend, so I don't want the user to have admin rights to
use the DB. What can I do.
Thanks and God Bless,
Mark A. Sam
Are you sure that it is related to admin rights? Maybe you just need to add a user named "Sales" to
the SQL Server permissions?
"Mark A. Sam" <msam@.Plan-It-Earth.Net> wrote in message
news:u2oqViHmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I am testing an Access Database on network workstations with linked tables
> to SQL Server. I set up a DSN and have no problem logged in as myself with
> Admin rights to the server. But when I login as a user with no admin rights
> I am denied access with this message:
> Connection failed:
> SQL Server State '28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'dayoubunc1\Sales'.
>
> The network server is Windows 2003 Server, the workstation is Windows 2000
> Pro and the SQL Server is a runtime version which came with a Veratis backup
> system.
> The purpose of moving the tables to SQL Server is security. A user can
> steal an Access backend, so I don't want the user to have admin rights to
> use the DB. What can I do.
> Thanks and God Bless,
> Mark A. Sam
>
|||Hello Scot,
I don't know how I would do that. I opened Enterprise manager and tried to
add a new user but the Network users weren't available. I am very
inexperienced with SQL Server, but I think I recall from another client db
that the users network users were listed. And thinking about they, they had
many users who only had to log in and the DB worked, even through terminal
service sessions. It seems as though I only set up the DSNs on those
workstations.
God Bless,
Mark
|||I tried adding full Control to the user for the SQL Server Folder as well as
the database folder, but it didn't get me any further. Authenticated Users
also have access to the SQL Folder.
Could it me that it is a Run Time Version (that is what I was told) of SQL
Server? Does the client need to purchase the full version?

Non- Admin connection

I am testing an Access Database on network workstations with linked tables
to SQL Server. I set up a DSN and have no problem logged in as myself with
Admin rights to the server. But when I login as a user with no admin rights
I am denied access with this message:
Connection failed:
SQL Server State '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user
'dayoubunc1\Sales'.
The network server is Windows 2003 Server, the workstation is Windows 2000
Pro and the SQL Server is a runtime version which came with a Veratis backup
system.
The purpose of moving the tables to SQL Server is security. A user can
steal an Access backend, so I don't want the user to have admin rights to
use the DB. What can I do.
Thanks and God Bless,
Mark A. SamAre you sure that it is related to admin rights? Maybe you just need to add
a user named "Sales" to
the SQL Server permissions?
"Mark A. Sam" <msam@.Plan-It-Earth.Net> wrote in message
news:u2oqViHmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I am testing an Access Database on network workstations with linked tables
> to SQL Server. I set up a DSN and have no problem logged in as myself with
> Admin rights to the server. But when I login as a user with no admin righ
ts
> I am denied access with this message:
> Connection failed:
> SQL Server State '28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> 'dayoubunc1\Sales'.
>
> The network server is Windows 2003 Server, the workstation is Windows 2000
> Pro and the SQL Server is a runtime version which came with a Veratis back
up
> system.
> The purpose of moving the tables to SQL Server is security. A user can
> steal an Access backend, so I don't want the user to have admin rights to
> use the DB. What can I do.
> Thanks and God Bless,
> Mark A. Sam
>|||Hello Scot,
I don't know how I would do that. I opened Enterprise manager and tried to
add a new user but the Network users weren't available. I am very
inexperienced with SQL Server, but I think I recall from another client db
that the users network users were listed. And thinking about they, they had
many users who only had to log in and the DB worked, even through terminal
service sessions. It seems as though I only set up the DSNs on those
workstations.
God Bless,
Mark|||I tried adding full Control to the user for the SQL Server Folder as well as
the database folder, but it didn't get me any further. Authenticated Users
also have access to the SQL Folder.
Could it me that it is a Run Time Version (that is what I was told) of SQL
Server? Does the client need to purchase the full version?

No tables in Linked Excel Server view

This allowed me to create a linked server, but when I try and view the
tables, it does not show any tables.
I assumed the tables would be the sheet names, no?
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'H:\IT\Shared\tblInItem.xls',
NULL,
'Excel 5.0'
GO
--
-Tobias Mazzei
Wraith SystemsNot really - see example F:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
You need to name the cell range. I think I saw a VBA procedure some where
(online) that automates range naming - provided there's only a single table
per worksheet.
ML|||Okay, I found the skinny on this. I hope my pain helps others on this.
Apparently I goofed in setting up the linked servers to begin with.
The standard procedure for setting these up is to
1) Create the Excel file(s)
2) Name the columns using the range name function, these names will show up
in your "tables" list once you establish the link
3) Make sure you have symin AND setupadmin rights first
4) Use Enterprise Mgr to right click on linked servers and create new link
-or-
Use Sql Query Analyzer and build a statement like:
DECLARE @.RC int
DECLARE @.server nvarchar(128)
DECLARE @.srvproduct nvarchar(128)
DECLARE @.provider nvarchar(128)
DECLARE @.datasrc nvarchar(4000)
DECLARE @.location nvarchar(4000)
DECLARE @.provstr nvarchar(4000)
DECLARE @.catalog nvarchar(128)
-- Set parameter values
-- Remote Server Name
SET @.server = 'xcltblInItemUom'
-- Program you are importing from
SET @.srvproduct = 'Excel'
-- Type of Data Link Engine
SET @.provider = 'Microsoft.Jet.OLEDB.4.0'
-- Important, Use full domain namespace
SET @.datasrc = '\\svrsbs3hyc\Company\IT\Shared\tblInIte
mUom.xls'
SET @.provstr = 'Excel 8.0'
-- Execute command to link server to Master.DBO
EXEC @.RC = [master].[dbo].[sp_addlinkedserver]
@.server, @.srvproduct, @.provider,
@.datasrc, @.location, @.provstr, @.catalog
Note that if you are not programming directly ON the SQL server you MUST use
the UNC of the location of the excel files. (most of us dont use the server
to program on, so this is important)
5) Depending on your security policies, choose link server properties after
link, and under security, select Be made without using a security context
6) when referencing the query, the sheet is designated by a '$' after the na
me
7) Rinse, repeat for each file you need to link.
This operation works, although I am sure that there are more glamourous ways
of doing this.
For those beginners out there that didn't get this stuff in MCDBA courses,
good luck!!
-Tobias Mazzei
Wraith Systems
"ML" wrote:

> Not really - see example F:
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> You need to name the cell range. I think I saw a VBA procedure some where
> (online) that automates range naming - provided there's only a single tabl
e
> per worksheet.
>
> ML