How to process cube using Windows Scripting & by passing parameters dynamically

Question

Post reply

How to process cube using Windows Scripting & by passing parameters dynamically

NIRANJANA1977

SSC Eights!

Points: 854
More actions
April 29, 2010 at 5:17 am

#142793

Hi All
I am in process of shifting from one OLAP tool Safari Olap to SSAS. In previous application there was a front end which was being used by users to provide Specific values pertaining to Cube. They were allowded to select Multipal Values w.r.t. Dimension values and Measures which was being entered using list boxes which was then used as a feed to Safari OLAP tool through batch jobs . Now I need to port same solution with SSAS. But I am not too much awared about passing such type of parameters and process cube every time according to dimension & measures changes .
e.g. if I select Demographic area USA/Canada , Agent Location - USA
in this case cube should get processed with only these two values and user should get data in a cube only for give selected ranges.
What are the ways to overcome this problem ?
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

cees van diermen SSC Eights! Points: 841 More actions · Answer 1

LS,

One way to do this is:

1) Visual Studio: In your cube's datasource view (DSV) use named queries as dataproviders for the cube (measuregroups and dimensions). Where applicable include a where clause in the named queries (which will later on be modified based on user input).

2) Visual Studio: Assuming the user input is temporarily stored in a sql-table, refer to this table in the DSV's named queries (........... where Country in (select Country from CollectedUserInput ) )

3) Visual Studio: Deploy the cube. Now the cube exists in SSAS and can be processed on request.

4) Create a userform for the enduser to enter his selection, which is stored in the sql table 'CollectedUserInput' .

5) Place a button on this form to trigger the processing the cube (using windows commandline utility ascmd).

6) The data provided to the cubeprocessing is limited to the user selection. Accordingly the cube will only have the required facts.

Regards,

Cees

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

Keep in mind that this will produce "the" cube with the selected data filters, not "a" cube - if you get two users running this, one after the other, your single cube will be updated using the first preference and then updated using the second. You may want to add a step in the middle that creates a new cube as the target.

If these filters are frequently applied, you may want to consider building one cube that has perspectives applied to it, so to the end users it looks like you have an Americas cube, an AsiaPac cube etc.

Why were you building specific cubes previously? Was it business requirement or technical limitation of the software - if the latter, you're more unlikely to hit those limitations with SSAS.

Steve.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

forgot to mention, there are (or at least were) tools around that would help you build local cubes, usually with subsets of the primary cubes data. If you insisted on having the smaller, sub-focused cubes, you could consider using one of these. That would let you maintain your primary cube and then basically 'slice' the parts you want away into local subcubes.

Steve.

NIRANJANA1977 SSC Eights! Points: 854 More actions · Answer 4

Hi All

Previously the situation was as follows.

Application was based upon AS400/DB2 and Java based front end. Every time whenever user needed any report he had to create a request thru the front end using selection criteria. Then that request was sent to AS400 system which was then running some scripts and fetches data from Db2. The resulting feed was then pushed to process the cube through the scription. OLAP tool was Safari OLAP. Since it was developed 7-8 years back there could be possibility of systems memory restriction. But now in present scenario there is no such restriction and user want to port new solution on SQL Server + Reporting .

I hope everybody got clear understanding now !

Thanks

cees van diermen SSC Eights! Points: 841 More actions · Answer 5

Hi Steve,

Thanks for your additional remarks. You're right about the required additional step. At one of our Customers we do a simple (XMLA) scripted backup and restore (using a new name) to deal with this.

I don't share your idea about pespectives. As far as I know they are used to control the visibilty of SSAS-objecttypes, but not the data itself. So these can not be used to filter America.

Regards,

Cees

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Yep, you're right, my bad. I guess security could be used but the implementation would be cludgy (ie passing the context on the connection).

Sounds like, from the additional info, the requirement quite possibly was based on technology versus end user specification.

Steve.