Saturday, April 2, 2022

Data Sources, ImpersonationMode, and AMO

Data Sources, ImpersonationMode, and AMO

We have an ASP.NET application that uses AMO to make cube modifications (adding and removing dimensions and measures) and process cubes, and we're running in to a strange problem with the code that removes dimensions.

The error that's coming back from the AMO code is  "The ImpersonationInfo for datasource 'AEDB' contains an
ImpersonationMode that can only be used by a server administrator."

I checked the impersonation mode setting on the data source, and it's set to  "ImpersonateServiceAccount". The service is running as LocalSystem in this case, and the SSAS and DB Engine are running on the same machine.

Taking the error message at it's word, I added the user to the server role, and the error went away. But I'd really rather avoid that if possible. The user has been granted all the relevant rights for modifying the database through an SSAS role - adding measures and dimensions works fine, as does removing measures. But not removing dimensions.

I tried changing the impersonation mode to "Use the credentials of the current user", and that cured the deletion problem but broke cube processing ("contains an ImpersonationMode that is not supported for processing operations").

So I'm not sure what to try. Is there a solution here that covers both cases (processing and modification)? Am I missing something?

BTW,
The AMO portion of the stack trace is:

Message: The ImpersonationInfo for datasource 'AEDB' contains an
ImpersonationMode that can only be used by a server administrator, .

    Source: Microsoft.AnalysisServices
    Stack Trace:
       at
Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadResponse
(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean
throwIfError)
       at Microsoft.AnalysisServices.AnalysisServicesClient.Alter
(IMajorObject obj, ObjectExpansion expansion, ImpactDetailCollection impact,
Boolean allowCreate)
       at Microsoft.AnalysisServices.Server.Update(IMajorObject obj,
UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings,
ImpactDetailCollection impactResult)
       at Microsoft.AnalysisServices.Server.SendUpdate(IMajorObject obj,
UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings,
ImpactDetailCollection impactResult)
       at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions
options, UpdateMode mode, XmlaWarningCollection warnings)
       at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions
options)
       at

how many connection sqlexpress supports

I see this question a lot, so I thought I would go ahead and post the answer here.

Q: How many connections does SQL Server Express support?

A: Unlike MSDE, SQL Server Express does not limit connections.  SQL Server Express supports:

  • 1 gb RAM (note the machine can have more memory, but 1gb will be used by SQL Express)
  • 1 processor (again, the machine can have more, but just 1 proc will be used by SQL Express)
  • 4 gb database size (note, there can be multiple databases, but none of them individually can be more than 4 gb)

Hope this helps,

Thanks< MJ


Reply:
Is it right that only 5 database users can connect to SQL Server express 2005 engine at the same time?

------------------------------------

How to Concatenate a member with a string

Hello MDX experts,

I have a question regarding concetenation of a string within MDX.  You will see 5 products below, but I am trying to add "TestProduct" string to each of the Product members below on the Rows (ie AWC Logo Cap - TestProduct).  The commented lines are the ones giving me trouble.

WITH SET [ChardonnayChablis] AS
   '{ [Product].[Product].Children  }'
   //'{ [Product].[Product].Children + StrToMember("- TestProduct") }'
   //'{ MemberToStr([Product].[Product].CurrentMember) }'
SELECT
   topcount([ChardonnayChablis],5,[Measures].[Order Count]) ON Rows,
   [Measures].[Order Count] ON Columns
FROM [Adventure Works]

Sincerely,

-Lawrence

connect to web service?

Is it possible to connect to a web service from Analysis Services to populate a cube? Can someone point me to sample code or documentation?

Can not connect to SQL Server 2005 Analysis server / browse tutorial cubes

When I try to :

browse the cube or its dimensions from tutorial (lesson 2 from AS 2005 Tutorials )

connect to AS via SQL Server Management Studio.

see or set the remote options of olap servicen in SQL Server Surface Area configuration

And the error is :

Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. (System)

I removed and reinstalled SQL Server . Installation was all rigtht with no errors.

I got the hotfix from : http://support.microsoft.com/kb/912017/EN-US/ and installed the patch.

But it did not help.

Both the olap and browser service run .

I do appreciate any practical advice that can help me solv this issue.

 

XMLA To Create Dimension Attribute

I am looking for an XMLA (or ASSL) script to add an attribute to a dimension.

Can anyone help ?

Unable to add database to Web project - SQL Express eror message

Searched messages with no real help. Even re-installed VS 2005 and SQL Express (from the CD), and reset settings.

1) Created new website.

2) Tried to "add new item" -> SQL Database.

Message: Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?linkId=49251

Am running SQL Server 2005 and SQLExpress in named instances on localhost.  If I use Server Explorer within Visual Studio 2005 I can create a database for the SQL Express Server, and see its tables, so I know SQL Express is available to Visual Studio, but I still get the message that SQL Express is not installed.

In Visual Studio 2005 -> Help -> About -> Installed products it shows

SQL Server Analysis Services

SQL Server Integration Services

SQL Server Reporting Services - but no direct mention of SQL Express or database engine, if that makes a difference

Any help appreciated.  Thank you.

3/6/2006 SOLVED

Fixed the problem by reinstalling SQLExpress via the VS 2005 disk rather than the Windows->AddPrograms feature that I had used earlier.

Member Name/Value doesn't show anything when the language is set from all to any language

SQL Server 2000

                              I went through the following article

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmemberprops_8ier.asp

To achieve multilingual support but when i changed the Language of member property

from All to any particular language ex.Spanish,I am not able to see that member name/value when the dimension is browsed,Any ideas

Thx and regards

Pankaj

 

OLAP User Access

Having created the OLAP cube and dimensions and deployed the OLAP structures using Business Intelligence Development Studio we are looking for ways to facilitate the OLAP Analysis for users. We have used Oracle Discoverer Plus and Oracle Portal and looking for something that offers similar features.

Our objective is to create a similar portal as in Oracle and allow OLAP stuctures to users so that they can perform drill down, slice and dice on the defined OLAP reports.

Thanks.

Good Books for building weighting table in dimension to resolve many to many relationship

Hi All,

I want to make store proceduce to change data from relational schema to data warehouse as weighting table model in OLAP. That's mean I will reschema and define some goups as if OLAP does.

Do you know any good book, or tool or anything that can help to build algorithm effectively for group dims

Thanks

Nhan.

 

Need general advice on financal reporting (P&L statement)

Hi!

We are implementing a cube that should support our P&L statement.

We have come accross the following issues so far, any input on these would be appreciated.

1) Our P&L statement combines different types of values: Monetary values, percentages, key figures (like average production / day) and other non aggregatable measures such as headcounts. These are intermixed in our reports like:
Total income x$

Income from A customers y$

Number of A customers 32

Income from B customer z$

Any thoughts on how to handle this?

2) We need to sum random accounts and subsums, some of these sums are pure aggregations and others are math like income account X minus income account Y.These sums do not roll up nicely into their parents. Any best practices / experiences on doing this?

3) Any general advice on handling the dimension(s?) that would be put on rows in such a scenario? As i mentioned, we will be showing combinations of sums, costcenters, statistical accounts (ie. key figures), and not the basic accounts (although we will want to drill down into accounts from the report).

 

Any help / suggestions / book references / web references GREATLY appreciated!

- Peter

Incorrect Aggregations

I have a cube displaying incorrect aggregations. 

Can anyone tell me why my aggregations are incorrect? 

Can anyone tell me what I should be looking for?

Here is what it looks like:

 

Assets

   Current Assets

      Cash

         Cash Account 1                   100

         Cash Account 2                   200

         Cash Account 3                   300

      Total                                       700

      Accounts Receivable

          A/R Account 1                    100

          A/R Account 2                        0

      Total                                       700

      Inventory          

          Inventory Account                   0   

      Total                                       700

   Total                                          700

   Non Current Assets 

   Total                                          700

Total                                             700

 

The Total Assets is displaying as the Total at each level of the Dimension but I have no idea why.  Totals for other account types (Liabilities, Revenue, Expense, etc., do the same thing, but display a different amount.

 

Any suggestions would be greatly appreciated.

 

Wendell G

 

          

 

 

Rows to repeat at top

suggestion for msft developers. 

I just learned the <SimplePageHeaders> flag in the config file (to manage the export to excel) and find this functionality extremely useful.  fine job.

Suggestion on new functionality.  Something where I can specify the number of rows to repeat at the top of the page, also in excel, to further improve the integration with excel (would like the column headers to repeat on each page).  Freezing them at the top also would be awesome. 

2005 is good progress

Reporting Services 2000 Accessing SSAS 2005

has anyone tried using Reporting Services 2000 to access a SSAS 2005 database? I'm finding RS 2005 pretty much unusable as far as accessing analysis services goes, crashes, errors, unacceptable performance, etc...

 

I'm sure I'll return to RS 2005 after service pack 1, but at this point, I just need something that works.

Division by NULL

In Analisys Server 2005 the division by NULL is infinity !!!!!!!!!!!

Why this occurrs ?? In 2000 this division is NULL too... Anyone can help ??

Supporting Multiple Character Sets

 

Hi

     Currentely i am using analysis Services with SQL SERVER 2000

OS:- Windows 2000 Server

Datasource:-Oracle

                  We need to show the data in the cubes for multiple countries.

But we are not able to show the data for diferent languges at the same time becoz

in windows 2000 default language on the server can be set to only one language

and the data in the cube can be shown in same language

Ex Let say we wud liket see the data in the cube for Russia and turkey on the same server.If we set the default language of the server as "Rusia" or "Cyrlic"

We are able to see data for Special Russian chars but then we are not abale to see specilal turkey characters.

                                           Please let me know if anybody has the answer for this.

Pl also let me know if same thing can be achived using sql server 2005

 

Thx and regards

Pankaj

Different aggregation based on dimension attribute

I am very new to Analysis Services, so please forgive the relative ignorance.

I *think* what I want is to aggregate a measure differently based on the "level" of a time dimension.

What I am starting with is a transaction table that has (among other things) an amount, a posting date, and a flag. The flag indicates whether the amount of the transaction applies to the current period/month or to the entire year.

I am envisioning that when a user drills down to a certain period, the amount is a sum of the amounts applied directly to that period plus the average of amounts applied to all periods within the year.

Thanks very much for any help.

Regards,
Daniel

Encrypted Data / Fact Dimension

Hi,

I have a requirement to encrypt a column of data within a SQL Server 2005 table however I then need to provide this value in the drill through data set. How do I tell analysis service's that this is an encrypted column and equal how do I prompt for the user to key/pass through the encryption key so that the data set returned is the non-encrypted value????? Is MDX an option? and how do I ensure the de-encryption API is called????? Is this even supported???

Any help or advice would be very much appreciated.

Sunny

Bug or solve_order??? invisible measures, calculated members and calculated cells.

Hi,

In AS2000 I have a problem. I have a calculated member which is a member of the measures-dimension called [measures].[avg daily hrs]. This is calculated by [measures].[total hrs]/[measures].[total days], The measures [measures].[total hrs] and [measures].[total days] are both made invisible by setting the "visible" property to false. The solve_order of the calculated member is 0. Then I have calculated cells, I do this because I have other measures which need currency conversion. The calculated cells have solve_order -1.

My issue is that when my [measures].[total hrs] and [measures].[total days]  are set invisible the calculated cell returns null, when I set them to visible I get the right result. How can the property visible of the measure influence my return values??? I am lost, help.

SSAS2005 currency conversion for calculated measures based on measure error

Hi,

I have a problem whith the currency conversion (I've use the SSAS2005 currency conversion wizard) when it comes to calculated measures based on measures. I have a calculated measure a)

CREATE MEMBER CURRENTCUBE.Measures.[_Only third party] AS Sum({Descendants([Customer].[CustomerGroup].&[1], [Customer].[CustomerGroup])}, [Measures].[Amount])

and another calculated member b) which uses calculated measure a)

CREATE MEMBER CURRENTCUBE.[Account].[AccountIAS].[511.11 - Sales net of taxes - Group] AS [Account].[AccountIAS].[Type].&[T].&[51].&[511].&[511.13] - ([Account].[AccountIAS].[Type].&[T].&[51].&[511].&[511.13],[Measures].[_Only third party]);

When the currency conversion is performed on a calculated member ([511.11 - Sales net of taxes - Group] ) the values in the reporting currencies don't get right. (accidentally it shows the currency rate of the reporting currency)

For reference I have a calculated member c)

CREATE MEMBER CURRENTCUBE.[Account].[AccountIAS].[511.1 - Sales net of taxes] AS [Account].[AccountIAS].[Type].&[T].&[51].&[511].&[511.13]

which is working fine for the reporting currencies. So - what is the problem with calculated measures based on measures regarding the currency conversion - and is there a way to get around this issue?

Br,

Henrik

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Setup is Split Across Multiple CDs

Setup is Split Across Multiple CDs Lately I've seen a bunch of people hitting installation errors that have to do with the fact th...