Issue XMLA queries via SSIS

Hello,

We are in a similar situation at my client... we wish to issue the CREATE and DELETE XMLA scripts for a specific cube via SSIS, however are unsure as to the control object to use...

More info regarding our processes:

OUR SSIS flow is designed to:

1.) backup an existing cube

2.) create a new cube leaving the original in place for users to use while the new one is building

3.) once the newly built cube has been validated, drop the orignal cube, and rename the newly built cube back to the original cube's name

We used DDL code files (DDL Task Objects within SSIS) which contain the XMLA query code for each CREATE, DELETE, ALTER statements used for each specific task:

The flow:

1.) BACKUP Original_Cube

2.) CREATE New_Cube

3.) PROCESS New Cube

4.) DELETE Original Cube

5.) ALTER New_Cube to Original Cube

In order to execute each DDL Task code set for a specific task, it is necessary to specify a connection to a given catalogue... however, in our case, and in the general case of using CREATE/DELETE/ALTER ddls, a given database & catalogue may or may not always be available...

it would be ideal for there to be a way to issue an XMLA script to CREATE a DUMMY CUBE (outline and model only) for all other DDLs to use as a basis (at the beginning of our SSIS Flow), and then issue another XMLA script to DELETE the DUMMY cube when it is no longer needed (at the end of our SSIS Flow)

Currently, the flow does work fine, however, the DUMMY CUBE must exist on the Server in order to provide a connection for each DDL Task... at this point, the creation of this DUMMY CUBE has to be a manual process, we are looking to automate this....

the "Script Task" object bumps you into a VB window asking you to provide a VB script... is there a way to have VB issue an XMLA command...? Or, is there an easier way around this...?

THANK YOU!

Michael

Hi Michael,

I'm hoping there is an easy answer to this. The "Execute DDL Task" should do all you need to do. Anything you can do with XMLA can be done with that task.

I've got alot of XMLA material here: http://blogs.conchango.com/jamiethomson/archive/tags/XMLA/default.aspx and alot of that talks about issuing XMLA from SSIS. Particularly this one:

Process SSAS dimensions and measure groups individually

(http://blogs.conchango.com/jamiethomson/archive/2006/07/18/SSIS_2F00_SSAS_3A00_-Process-SSAS-dimensions-and-measure-groups-individually.aspx)

-Jamie

Issue with Unknown Member - Measures are 'off'

I have a problem with a cube where the some of the measures seems to be ‘off’ when compared against the underlying table. Digging into that I noticed that the measures do not reflect the amounts/numbers tie to the unknown member in of my dimensions (date dimension specifically).

e.g. at the leaf level it looks something like:

01/01/2007 100

02/01/2007 100

Unknown 50

Grand Total 200

If I browsed it at a higher level, let’s say month, then it looks like:

Jan 2007 200

Unknown (yes, empty)

Grand Total 200

The odd thing is that my date dimension is playing 3 roles, and this only happens in 2 of the roles.

I am expecting lot of rows to be tie to the unknown member as the source has referential integrity issues.

Any advice is appreciated

Have you checked your attribute relationships? Incorrectly set attribute relationships can cause this kind of problem.

Chris

|||

Christopher Webb wrote:

Have you checked your attribute relationships? Incorrectly set attribute relationships can cause this kind of problem.

Chris

Thanks Chris for your reply. I will be double cheking that; but I still don't understand why this happens only with 2 of the 3 roles that dimension is playing. Are not the attribute relationships defined in the underlying dimension only? Should not all the roles behave the same?|||

Yes, all the role-playing dimensions should behave the same - but sometimes the behaviour is quite hard to spot, so it could be that all three are producing incorrect values but it's only obvious on two of them.

Chris

Issue connecting from C#, even though SSPI, it does ANONYMOUS LOGON

We are using Integrated Security=SSPI, and I can connect to SSAS 2005 just fine. I can also connect to the cube through OWC...

but when I try to connect in C# (.net 1.1), I get the message: the peer prematurely closed the connection. I did plenty of searches on this message, and I should have all the required files to connect - it just doesn't connect.

So I did a trace, and when I connect through OWC, it shows an Audit Log with my username. When I try to connect w/ .net, it shows ANONYMOUS LOGON

Here is my connection string that is being used: (xxx's are filled w/ the right values)
Provider=MSOLAP.3;Cache Authentication=True;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxx;Data Source=xxxx;

Any ideas?

I realized that this might be happening because the virtual directory was set to use anonymous access. So, I took that off of the directory and made sure 'integrated windows authentication' was checked. I did an iisreset and tried out the changes, but it still was using ANONYMOUS LOGON.

I then tried to make a normal windows app because before, I was using a web app. This connected with my name just fine!

So it seems like the problem is with the iis virtual directory setup still wanting to use the anonymous account. I even set deny permission to the web project directory for iusr, iwam, network service.

I'd be glad to receive some feedback.

|||Its the double hop scenario. The user comes in with credentials and they get used to access the web site/service. But when the web site/service goes to the db, the ASP.Net (local) user is used for authentication.

That is why it works when one runs it outside the web site/service but not within. If it is IIS 6 you can try the application pooling in InetMgr by specifying a user that has access and the have the site use that pool...your mileage may vary.

Otherwise you will need to connect not using the impersonation route but by passing in a DB user name and password.

Another option is Active Directory and having the machine accepted while using Kerberos Authentication...but again, your mileage may vary.|||

There is a need to implement the specific user's crudentials when accessing SSAS. It sounds like one option you gave would still be using a single logon account.

If we go the route of active directory, is there a way to pass the current user's logon info but not have it be visible to developers? the connection open method takes a (plain text?) un/pw if SSPI isn't used.

|||You may want to look into Protected Configuration as provided by ASP.Net 2. That would encrypt the information into the web.config. See this Security Practices: ASP.NET 2.0 Security Practices at a Glance to see if that answers your question, search for (encryption). Otherwise one of the other notes may be beneficial.|||Were you able to get a resolution to this issue? I have a .NET application that I am trying to pass role-based security into SSAS 2005. This is a double-hop scenario and we have implemented Active Directory to try to restrict access to dimension data. However, it is not passing through the appropriate security restriction to the dimension data.

Issue connecting from C#, even though SSPI, it does ANONYMOUS LOGON

We are using Integrated Security=SSPI, and I can connect to SSAS 2005 just fine. I can also connect to the cube through OWC...

but when I try to connect in C# (.net 1.1), I get the message: the peer prematurely closed the connection. I did plenty of searches on this message, and I should have all the required files to connect - it just doesn't connect.

So I did a trace, and when I connect through OWC, it shows an Audit Log with my username. When I try to connect w/ .net, it shows ANONYMOUS LOGON

Here is my connection string that is being used: (xxx's are filled w/ the right values)
Provider=MSOLAP.3;Cache Authentication=True;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxx;Data Source=xxxx;

Any ideas?

I realized that this might be happening because the virtual directory was set to use anonymous access. So, I took that off of the directory and made sure 'integrated windows authentication' was checked. I did an iisreset and tried out the changes, but it still was using ANONYMOUS LOGON.

I then tried to make a normal windows app because before, I was using a web app. This connected with my name just fine!

So it seems like the problem is with the iis virtual directory setup still wanting to use the anonymous account. I even set deny permission to the web project directory for iusr, iwam, network service.

I'd be glad to receive some feedback.

|||Its the double hop scenario. The user comes in with credentials and they get used to access the web site/service. But when the web site/service goes to the db, the ASP.Net (local) user is used for authentication.

That is why it works when one runs it outside the web site/service but not within. If it is IIS 6 you can try the application pooling in InetMgr by specifying a user that has access and the have the site use that pool...your mileage may vary.

Otherwise you will need to connect not using the impersonation route but by passing in a DB user name and password.

Another option is Active Directory and having the machine accepted while using Kerberos Authentication...but again, your mileage may vary.|||

There is a need to implement the specific user's crudentials when accessing SSAS. It sounds like one option you gave would still be using a single logon account.

If we go the route of active directory, is there a way to pass the current user's logon info but not have it be visible to developers? the connection open method takes a (plain text?) un/pw if SSPI isn't used.

|||You may want to look into Protected Configuration as provided by ASP.Net 2. That would encrypt the information into the web.config. See this Security Practices: ASP.NET 2.0 Security Practices at a Glance to see if that answers your question, search for (encryption). Otherwise one of the other notes may be beneficial.|||Were you able to get a resolution to this issue? I have a .NET application that I am trying to pass role-based security into SSAS 2005. This is a double-hop scenario and we have implemented Active Directory to try to restrict access to dimension data. However, it is not passing through the appropriate security restriction to the dimension data.