Building multiple cubes dynamically

[CognosPaul]

I have a client who is a service provider. Due to the complexity of the reporting requirements and budgeting constraints, we've determined that powercubes are the way to go.

At the moment when a user logs in the database is automatically sectioned for only his data. With the powercubes, I'd like each user to have his own cube, with the data source connection automatically pointing to the correct cube based on an attribute from the authentication system. So far so good.

At this point the hold up is how can I automatically and dynamically build each cube? In theory it should run over and over with the company hierarchy (the only dimension with data that's not shared between the users) filtered based on a list. So if the client has users 1 - 3, the model should be run 3 times with the cubes saved as Cube1.mdc, Cube2.mdc, Cube3.mdc.

[MFGF]

I guess you would need to find a way of iterating through the data and building a batch file with the relevant commands to build the cubes. It's not something I have ever attempted or even considered before. I imagine you will be breaking new ground with this, Paul. If anyone can do it, it would be a very smart lad who used to sport a fabulous red mohican :)

MF.

[tjohnson3050]

I have never tried this, but if I remember right, when you setup partitioning, separate MDC files get created for each partition, then a reference MDC file gets created that points to the rest.  You could try to setup partitions for each user then test connecting directly to the underlying MDC files.

[CognosPaul]

I was considering using partitioning for that, but even if it works I'd lose the time based partitioning. I'm also not entirely sure I'd be able to control the file names.

According to the user guide, it looks like you can supply prompts through the command line. So what I'm thinking is writing a script that opens a connection to the db to get a list of active user sites, and looping through the results triggering the cube build with new parameters.

[CognosPaul]

I think I'm on the right track. I can set up an xml file to pass the correct prompt parameters for each run. Unfortunately it looks like I have to define the cubes in the model in advance.  In cogtr.xml there is a command section called "Publish" which looks something like:

Code Select Expand

<Section Name="Commands">
<Command Name="Publish">
<Parameters>
<Parameter Name="CubeName" Value="Company_2"/>
<Parameter Name="CognosConnectionDataSourceName" Value="Company_2"/>
<Parameter Name="DataSourceWindowsLocation" Value="c:\transformer\temp\Company_2.mdc"/>
<Parameter Name="DataSourceUnixLinuxLocation" Value=""/>
<Parameter Name="DataSourceNameSpace" Value=""/>
<Parameter Name="ReadCacheSize" Value="0"/>
<Parameter Name="DataSourceSignon" Value="FALSE"/>
<Parameter Name="DataSourceDescription" Value=""/>
<Parameter Name="DataSourceToolTip" Value=""/>
<Parameter Name="DataSourceUpdate" Value="FALSE"/>
<Parameter Name="PackageName" Value="Company_2"/>
<Parameter Name="Packagelocation" Value=""/>
<Parameter Name="PackageDescription" Value=""/>
<Parameter Name="PackageToolTip" Value=""/>
<Parameter Name="PackageUpdate" Value="FALSE"/>
<Parameter Name="PackageAllowNullSuppression" Value="TRUE"/>
<Parameter Name="PackageAllowMultiEdgeSuppression" Value="TRUE"/>
<Parameter Name="PackageAllowAccessToSuppressionOptions" Value="TRUE"/>
</Parameters>
</Command>

While this is useful for dynamically creating the data sources (I can then reference that data source dynamically with macro functions inside a framework model), it's not so useful in that it fails of the cube isn't already defined in the model. So when company_3 signs up this won't work.

Does anyone know if there's some sort of "Insert" or "Define cube" command? If the cube can be defined programmatically at runtime, this will make adding new customers significantly easier.

[cognostechie]

Not sure if I understand the requirements correctly but if this is something like a company haveing 10 sales agents and each sales agent should see only his data , not the other agent's data.  If it is the same scenario, then Transformer has a good feature that automatically builds cubes for each agent seperately. In the attached example, it is building seperate cubes for each Product Category. 

Is this something you are looking for?

[CognosPaul]

The cube contains two dimensions that would need to be changed. Regional hierarchy and employee hierarchy, would that filter both?

I suppose at the very worst I could try making them alternate drilldowns against the customer key. Still experimenting...

[cognostechie]

Every employee belongs to a region, right ?. You can create a column in your data source query subjects  that will  have  region + employee  . Create a dimension and a level out of that and use that to create diff cubes.

[CognosPaul]

I will get this working, or tear Cognos apart trying.

So the cube groups work well. Adding the corp id as a dimension, works well as the measure, employee and corp hierarchy all have corp id in it. So everything is in the scope of everything else.

Now the next issue, I have 5 cubes (1, 2, 3, 4, 5). Each has a data source connection set up in the administration page. How do I set up a dynamic reference to them? Each user has an LDAP attribute returning their corpid, so it should be a simple matter of adding #$account.parameters.corpID# somewhere.  I've done this before with a relational database, it was just a matter of replacing the Content Manager Data Source field with a macro.

The location in the data source doesn't support macro functions. Opening the model.xml and replacing the reference with the macro doesn't seem to work either.

[tjohnson3050]

How about using security on the data source connections?  Allow only members of group Corp1 to see the connection to the cube for Corp1.

[bdbits]

I could be way off the mark here, but in case it helps... We have some cubes here where access to the data is defined by an organizational hierarchy. To do this we use custom cube views to filter the data at runtime. This is easier to explain with an example (fictional and not from my workplace).


Dept       Division    Section
HQ          Finance
HQ          Mergers
Widgets  Acct         AP
Widgets  Acct         GL
Widgets  HR
Retail      Sales     


In Transformer go to Diagram > Categories and the Custom Views tab. Right-click and create a custom view. Call it whatever you like. Assign Security will let you decide which custom view(s) will be in place at runtime for the user or groups. Click OK. Make sure the custom view is highlighted, then click the relevant dim below. Now below the list of dims click the first button for "Customize". On the right, find the category you want to restrict them to. Right click it - there are multiple options you can choose from string with Exclude, we most often use Apex which means they only 'see' this category and the descendants.

So for example, somebody with the Widgits/Acct/AP does not see anything from HQ or Retailing, nor do they see Widgets/Acct/GL or Widgets/HR. In fact if they open say Analysis Studio under Organization they will only see Widgets, which expands to Acct, which expands to AP. Neither can they get summary information for Widgets that includes GL or HR information (though other options would let that happen if desired).

This is all set up in a single cube, which is generated each night, and requires a single data source connection.

If this helps, great, if not just ignore me.  8)

[CognosPaul]

Trying that now. This leaves a few issues though:

I have more than one dimension that needs to be filtered. Regional hierarchy and employee hierarchy. By doing this, I'll need to add Corp ID to the top of each one to ensure they're only showing the correct regions and employees. This leaves a single member level at the top.

This also means significantly more work when adding a new customer. My ideal solution would be to simply add the user to the LDAP with the Corp attribute set correctly. If I use views the chance of user error increases substantially. If the client creates a custom view correctly, but forgets to add it to the cube, or if he creates the custom view but adds the wrong group. Exposing customer data to the wrong customer is a bad thing, and I'd like to make that as difficult for the client to as possible.

[prikala]

Why not create a script (using your favourite scripting language) that generates a MDL-script that implements the required custom views?

It might be quite simple if your organizational data is in a database that your scripting language can access.

Nigthly build script might then consist of
- copy the original cube model (without security views) to a temporary name
- run mdl script generation (=code to add views for every corp id, old or new)
- run generated mdl against temporary cube model
- generate cube from temporary model (now modified by generated mdl)