Saturday, April 2, 2022

MDX Query in SSIS

MDX Query in SSIS

Is it possible to run an MDX query against a cube and then insert the value into a sql 2K database?  It was possible to do it in DTS 2000 using an OLE DB connection and then a Data Transformation task.  We've been trying a similar configuration in SSIS but it seems to only take SQL queries.  How might we go about setting up a MDX query?

Writeback with currency conversion ?

Does anyone know what support is provided in SSAS 2005 on currency conversion based cube updates ?

I have put together a simple example on the AdventureWorksDW DB.

In my DSV, I have DimTime, DimCurrency and DimCustomer as dimension tables plus FactCurrencyRate and FactInternetSales as fact tables.

I then create a simple sample cube based on these 5 tables.

I now have my [CurrencyTest] cube made of 2 measure groups : "Fact Currency Rate" and "Fact Internet Sales".

I have setup the [Dim Currency] dimension to be typed as CURRENCY, the measure group "Fact Currency Rate" as Exchange Rate and added Currency Conversion BI to the cube (on-to-many relationship).

It all works fine as far as reporting is concerned. Changing currency works fine.

Now, I write-enable the Fact  Internet Sales measure group.

Updating the cube with no restriction on the "Reporting Currency" dimension works fine.

Now, when I try to perform an UPDATE CUBE mdx statement on a specific currency (on the [Reporting Currency] dimension ), I don't get any error. Though, the value is not modified at all (even afetr commit)

Maybe I should rather use a many-to-many type relationship with the curreny dimension...

Any suggestion on this ?

thx & rgds,

Francois

How do you support the Oracle CLOB Data Type?

I am using VS2005 Analysis Services to create a cube from an Oracle data source.

My problem is that 3 of the tables use a CLOB field.
So my "Data Source View" can only link to 25 of the 28 tables.

Is there a workaround so that I can include the 3 CLOB tables?

AS 2005 Using multiple Roles for a user

Hello,

I am setting up roles in the cube to restrict what the users can see. What I understand is that one user can in the AD belong to several groups. These groups are supposed to have a corresponding role in the cube.

the ideal situation would be that a combination of roles would mean an intersection of the dimension data. I have heard that in other tools you work as follows: Rmove all rights and then add on....

My situation in SSAS is as follows (one user belong to all three groups):

  Role                       AD group          dimension              Criteria

Market_North ; Market_North  ;  dim_Market  Only see North and members

Market_East ;  Market_East ;  dim_Market  only see East and members

Organization_Merchandize ;  Organization_Merchandize ;  dim_Org   only see Merchandize and members

 

The behaviour is that I see nothing or I see too much when my user belong to all AD groups/Roles (one ADgroup correspond to one Role). This is the fact when I view both dimensions. If I browse only dim_Market I see only North and East. If I browse only Organization I see nothing...

I thought that in order to ensure not to see too much I would deselect all members in  dim_Market in Role Organization_Merchandize   and vice versa but this does not work out.

- How do I go about this, is it at all possible?? I know that in 2000 you may only have one role defining what you may see otherwise you will see more than allowed.  My tests so far (in AS2005) indicate that you may only have one role...

 

Sincerely,

CreatedTimestamp doesn't have a value

Hi,

I'm playing around with AS2005 for the first time in a long time and this is just a quickie question.

Here's the XML for my data source:

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" xsi:type="RelationalDataSource" dwd:design-time-name="c35e52ca-345e-44ae-9629-38f2dea36ea9" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ID>Adventure Works DW</ID>

<Name>Adventure Works DW</Name>

<CreatedTimestamp>0001-01-01T00:00:00Z</CreatedTimestamp>

<LastSchemaUpdate>0001-01-01T00:00:00Z</LastSchemaUpdate>

<ConnectionString>Provider=SQLNCLI.1;Data Source=cgojthomson;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW</ConnectionString>

<ConnectionStringSecurity>Unchanged</ConnectionStringSecurity>

<ImpersonationInfo>

<ImpersonationMode>ImpersonateCurrentUser</ImpersonationMode>

<ImpersonationInfoSecurity>Unchanged</ImpersonationInfoSecurity>

</ImpersonationInfo>

<Timeout>PT0S</Timeout>

</DataSource>

Is there a reason that CreatedTimestamp and LastSchemaUpdate don't have the proper values in them? Do these nodes have any use within AS?

Probably lots more of these to come so bear with me :)

-Jamie

 

 

Time Analysis Dimension (Shell Dimension) Calculations not working in AS2005

Hello,

 

In AS 2000, we had created a "shell dimension" to calculate different time periods (YTD, MTD, previous MTD, Trailing 28 days). This dimension basically had several calculated members which did these time calculations. We would reference these members by [Period Calculations].[YTD] for example.

 

In AS 2005, is there an equvilent way to do this without breaking existing cubes and reports (which have mdx referencing this shell dimension)? 

Problem connecting to SSAS

I have installed the September CTP and am having problems connecting to Analysis Services.  The error message I'm getting is "No connection could be made because the target machine actively refused it (System)".  The log-in is set identically to what's set in Reporting Services, SQL Server 2005, etc.  Has anybody else come across this error?


Reply:
I have exactly the same problem and can't find what the problem it.  I can't connect using SQL Server Management Studio at all.  I tried turning off my virus scan and firewalls but nothing helped.

------------------------------------
Reply:
I have the same problem.

------------------------------------
Reply:
only i try to connect to RTM version of the Reporting Services.

anybody found a solution yet? i see a lot of posts with this problem.

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

Two ways to view member values

Hi,

I'm working on a SSAS 2005 project where I migrate an EXCEL pivot table based on a flat file (coming from an Oracle source). The previous implementation had most of the members defined twice, first by description and code and second by code and description, where code is the key of each customer. In the flat file, for each level with the need for "dual view support", there are 2 columns, having the same data but in different format.

So, for example - just for illustration - someone could set the rows to be

Customer1 (1234)
Customer2 (4553)
Customer3 (3573)
etc

where, someone else

(1234) Customer1
(4553) Customer2
(3573) Customer3

As you might have guessed, this freaked me out, because the flat file has lots of redundant data and now it is about 120MB fat!

What I'm asking is, how can I implement such a feature on my cube? Is there a designated SSAS 2005 way to do it elegantly or do I have to define on my hierarchies each level that requires "dual view support" twice?

SSAS 2005 Dimension Browser Error (Input String not in Correct Format)

Hello everyone,

Another developer I'm working with is having trouble browsing dimensions (Double click the dimension in the solution exploerer, click on the browser tab).  The "browsing" area turns grey with an error message of

TITLE: Microsoft Visual Studio
------------------------------
 
Input string was not in a correct format.
 
------------------------------
BUTTONS:
 
OK
------------------------------

I believe we have narrowed it down to this particular user's machine.  I ensured they had the same permissions (we are both set up as an admin in SSAS), opened their solution on my machine (this worked), and opened the solution on my machine using "run as..." with his username (this also worked).

We have also tried this with different solutions and have the same results.  This problem seems very interesting since it is possible to use the cube browser to view the dimension hierarchies.

Has anyone else come across a similar problem?  Does anyone have any suggestions to check for to try to address the problem?

Thanks in advance for any help.

Regards,

Dan

Sql Express and One Care

For those of you that are using One Care and Sql Express, there is a problem.  You will receive an error message in the Windows log that reads:

FCB: : Removealternatestreams: Operating system error 6 (The handle is invalid) occurred while creating or opening file 'C;\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mater.mdf"

This message is for: Master, Model, Temp, etc.....

OneCare blocks access to alternate NTFS streams.

 

Thanks for email Ogre...........

Cartridges

Hi,

is there any information about cardridges (the XSL files which "teach" SSAS how to access different databases than SQL Server)? The only information I found was that there is no information...

Thanks,

Drillthrough Security

Hi,

 

I have an AS2000 cube that pulls back "sensitive" information via its drillthrough.

 

I have 2 set of users:  Group 1 and Group 2.

 

I need Group 1 to be able to see everything when using the drillthrough, and I need group 2 to see everthing but 1 column.  This 1 column holds the sensitive data.  It is viable that the data is just masked, rather than being compltely removed.

 

For performance issues, it's important that I stick with 1 cube - I DONT WANT 2 CUBES...

 

Can anyone please tell me how I can achieve this please?

 

Thanks in advance.

 

Jon Derbyshire

MSOLAPADMINLib2.dll in 2005 ?

Hi,

i have a reference to MSOLAPADMINLib2.dll.
This dll is not supportet by AS2005.

What can i do to access the AS2005 through my application to build the cube ?

Updated Version of SqlClrProject

Back in December (26:th to be exact) I released a new version of the SqlClrProject and wrote about it here [0].

One drawback of the deployment functionality was that you could only deploy locally as the deployment process used the CREATE ASSEMBLY based on the location of the assembly; i.e. CREATE ASSEMBLY myasm FROM 'path_to_dll'.

Well, this is now fixed, as I have chenged the code so it now uses the binary representation of the assembly. You who know your CREATE ASSEMBLY stuff may think "what about dependent assemblies?" as dependent assemblies are not automatically deployed when using the binary representation. Well, the code handles that as well, it uses reflection to see what the dependent assemblies are and retrieves the binary representation of those as well during deployment.

So if you have installed the original SqlClrProject which was available from here [1], there is now an update here [2]. Download the update, un-zip and follow the readme.txt. The original download has now been updated as well, so if you haven't retrived it yet, go and get it [3].

Have Fun!!

Niels

[0]: http://staff.develop.com/nielsb/PermaLink,guid,200487e5-f2e2-449c-8a3f-ee28e6aa8735.aspx

[1]: http://staff.develop.com/nielsb/code/DeployPropAndAddIn.zip

[2]: http://staff.develop.com/nielsb/code/yukondeployupdate.zip

[3]: http://staff.develop.com/nielsb/code/DeployPropAndAddIn.zip

Deployment overwrites partitions

When I deploy to Analysis Services 2005 from BI Studio, it prompts me that 'Deployment will overwrite existing objects' and when they are deployed, it appears that certain partitions are lost.

How can I deploy while retaining existing data? 

Error in the encryption Library ("Full detail")

Hi Guys,

Here is the scenario,

Setup of Analysis Service:
Data Dir: I:\OLAP_DATA\

Data Storage of Cube Partition: Drives (C:\OLAP_DATA\,D:\OLAP_DATA\,E:\OLAP_DATA\,I:\OLAP_DATA\)

I created a backup of our OLAP Database with the configurations above. Deleted the OLAP Database and Restored the backup. I've changed the configuration during the restoration.

Below is the Configuration for the Restoration database

Setup of Analysis Service:
Data Dir: F:\OLAP_DATA\

Data storage of Cube Partition: Drive (F:\OLAP_Data, (default location))

I have a process that runs daily. After the restoration, the first process is working fine but the succeeding process failed. What I did was restore the backup and reprocess until the point of failure. The succeeding days process fails again. I investigated the Data Source property of the OLAP Database and found that the connection string was empty. I tried supplying the connection string and was fine and leave the other properties to its default value but when I click the OK. Error shows up.

Below is the Error:

"Error in the encryption library: Failed to encrypt sensitive data. Possibly the encryptioin key is inaccessible because of improper service account change.(Microsoft.Analysis)"

Need help or insight regarding this issue.

Regard,
L@rry

 

Add an adhoc record to the mdx result

I have the following mdx query

with
      member [Measures].[ParameterCaption] AS '[Company].[Branch].Currentmember.Member_Caption'
      member [Measures].[ParameterValue] AS '[Company].[Branch].Currentmember.Uniquename'
select 
    {[Measures].[ParameterCaption], [Measures].[ParameterValue]} on columns,
    {[Company].[Branch].ALLMEMBERS} on rows
from [Profit And Loss]

I would like to add one more ad hoc row to the result set, which is an adhoc member identifying All Branches. I don't want this member to appeared in the olap cube, but would like the user to view it in the result set. What should I do to archive this?

Thanks.

Encryption error processing cube with Visual Studio 2005 Analysis Services

Hi!

I am trying to process a cube in analysis services of visual studio
2005, with sql server 2005. The error is:

"Errors in the encryption library: Failed to encrypt sensitive data.
Possibly the encryption key is inaccessible because of improper service
account change."

Please, if anyone know how to solve this eror, please let me know...

Cristovao


Reply:
We seem to have this problem as well. Any answers to this one? Our datawarehouse was well and running for 4 months now until this weekend when we got this problem. Havent solved this yet.

------------------------------------
Reply:

Hi Cristavo

I also experienced the same error when I tried to configure the datasource property of my OLAP database. In the Data Source Properties dialog box, I've set the connection string and leave the security settings to its default value "ImpersonateServiceAccount" and the errors shows up when clicking the OK.

Larry 


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

Error in the encryption library

 

Hi guys,

Im configuring the datasource property of my OLAP database using Management studio. I've set the connection string. and leave the other properties to its default values.

Below is the error message I got when I click Ok.

Error in the encryption library: Failed to encrypt sensitive data. Possibly the encryptioin key is inaccessible because of improper service account change.(Microsoft.Analysis)

Right now I can't process cubes because I can not configure the datasource property of my OLAP Database.

Please advise on what to do.

Regards,

Larry

 

 

Errors in the encryption library When Processing Cube

 

Hi!

I am trying to process a cube in analysis services of visual studio
2005, with sql server 2005. The error is:

"Errors in the encryption library: Failed to encrypt sensitive data.
Possibly the encryption key is inaccessible because of improper service
account change."

Please, if anyone know how to solve this erorr, please let me know...

Thanks,

Joseph

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...