(RESOLVED) Can entity members be passed to Infocube values without using ascii exports and file uplo

Paul Wyatt

Using dataflows, BOARD is able to pass values from one cube to another.  As such, only one datareader to populate the initial cube is required.

 

This functionality allows for dynamic processes to manipulate the contents of infocubes in many useful ways, such as:

- Quantifying values from one hierarchy to another.

- Allow multiple users to simultaneously and independently create scenarios without writing the results to the server. 

 

I would be interested to know of any method that would allow me to pass entity members to an infocube for displaying in a report, similar to the dataflow method.  An example would be where an entity of one hierarchy is required in a report of another hierarchy.

 

I look forward to any response to my query.  If it is not possible,  I will re-post it as an idea.

 

Below is the method I currently use and am looking to replace with a dataflow type method.

 

ASCII txt file process.

 

Entity members can be passed to infocubes (and vice versa) by carrying out the following:

 

- Export a configured dataview with the source entity to txt

- Import txt file values into a target cube

- use target cube within report

 

Working example

 

- Two dimensions, [BRANCH] and [LEASE].  [BRANCH] contains the entity [Brand], which is to appear in a report on the dimension [LEASE]. 

- A functional requirement of the lease report is that it should be ordered by any column selected by the user.

 

Image 1: ENTITY structure

- A LEASE report requires the entity members of [Brand] to be included

- The report to be ordered by any column within it.  Due to the ordering criteria, only one row axis can be used, [LEASE].

- An infocube for [Brand] is used show the [Brand] at [LEASE] level. 

 

Image 2: Lease Report

 

 

- The cube [Brand] is highlighted in red. 

- The blue box shows two other entity members that have been similarly replicated in cubes.

- Additionally, the entity [Brand] has been used for selection purposes and can be seen on the left of the image.

 

Image 3: The Procedure that transfers the entity members to the cube:

 

Previous Member

Ok, so the goal is to show the associated Brand name w/o putting the entity on the row, so you need a text cube dim by Lease with the corresponding Brand name in it. Your current solution uses a DR, and you'd like to know if its possible to achieve a similar result with data flows.

 

When board aggregates text cubes, only 1 value from the text cube is displayed, rather than concatenating the text parts together. Therefore, I think it could get really hairy if you had multiple Brands to a Lease. However, assuming there is only ever 1 Brand associated with any Lease (as seems to be based on your solution), you could use data flows to copy a text cube dim by Brand containing the entity item names into one dim by Brand and Lease where a valid intersections exists, and then show that on your layout in lieu of the DR solution you created. If you need to get the entity name into a text cube in a way other than DR, you can use the below as a starting point, as long as you turn off High Performance Mode:

 

 

All that said, it would be much nicer if you could just use a block in the layout set to entity and have the names show up, particularly if there are multiple Brands to a Lease   

Paul Wyatt

Thanks very much for your response J C.  Took me a while to get around to your response due to workload.

 

Your response appears to suggest that I should use the target cube, with the two associated dimensions, to feed itself.  If this is correct, this did not work for me as the result was that I had the same PROPERTY and NAME for each and every lease. 

 

I'm sure that I have missed something in your reply, if you have successfully completed this.  Would it be possible for you to upload a test extract using [Shft][Ctrl][Alt]+[T] so that I could look at your suggested layout and procedure?

 

Here is what I did, 

 

Step 1 - Create Cube

Step 2a - Create procedure to populate target cube

 

Step 2b - Dataflow

Step 2c - Dataflow Setup

Kind regards

Previous Member

Hi Paul,

 

I don't have a db sample as i just created the layout / df in an existing db; however, I think you need 3 cubes to pull this off: 

Cube A - text - dim by Entity A (Property/Brand? Not sure what the difference is here but you want whatever you are going to display on the report from this tree)

Cube B - text - dim by Entity B (Lease)

Cube C - text - dim by Entity A, Entity B

 

df1 - Cube A populated via df as i showed above, which populates the entity item names in the text cube

df2 - Cube B populated via df as i showed above, which populates the entity item names in the text cube

df3 - Cube C populated via df with ~4-5 blocks:

block a: Cube A

block b: Cube B

block c/d: cubes, and or formulas that tell you where a valid intersection is

block e: Cube C

formula - Cube C = if block(c/d) = 1(is a valid intersection, etc), "Brand "&a (or similar concatenation, etc) This will put the appropriate Brand name in the cube where a valid intersection between Brand and Lease exists.Since text cubes don't aggregate but show the last value, you should be able to show this cube aggregated on your report (with Lease on the Row) and see the Brand displayed, even though the cube is dimensioned by Brand and Lease.

 

That's what i had in mind anyways. Basically, what i was showing above was only the first step, or how to get an entity name into a cube. From there you can just use normal df logic...

 

HTH

Paul Wyatt

Thanks J C. I'll check this out tomorrow if I get the chance. I appreciate the time you Tom to detail it down. I'll let you know how I get on. 

Paul Wyatt

J C, Many thanks.  I got it.  I would never have thought to turn off 'High Performance' mode to create the intermediate, or bridging, cube because it works when turned o when transferring the temporary values held in the bridging cube to the target cube.

 

This issue has been vexing me for ages. Really, thank you very much.