PowerPlay Transformer 1

[monsky]

Hi guys,

I'm still new to PowerPlay Transformer.

My objective is to create a cube coming from a single database that has multiple tables. This table is in a star schema format.

I understand that Transformer doesn't perform joins between tables even if they come from a common database but how can I create a cube using this database. Everytime I test build I always get a warning and when I create the cube it's basically empty. The solution that I did was to create a query in Access that already joins the tables that I needed.

I'd like to avoid this extra step. Please let me know how I can build the cube without resorting the step I mentioned above.

 

[Exie]

As far as I know, you cannot connect Transformer directly to a database. Traditionally you would use Impromptu to prodcue an SQL script (refered to as an [blue]Impromptu Query Definition[/blue] or [blue].IQD[/blue] file).

Do you have the Impromptu application ? If so, I would reccomend using this to generate IQD files containing all the details about the database and table joins.

If not, you will proberbly need to use an intermediate datasource, such as MS Access or CSV files.

 

[monsky]

So it's not possible to create a cube using multiple data sources?

Everytime I attempt to do multiple data source and do a test build I always get:

Warning: (TR2718) Dimension 'Period' has no measures associated with it. During PowerCube generation, no data source will be able to provide data for this dimension.

Warning: (TR2718) Dimension 'Product' has no measures associated with it. During PowerCube generation, no data source will be able to provide data for this dimension.

Check model is finished. 0 error(s), 2 warning(s) found.


I have Impromptu and creating the IQD is similar to what I did with the Access query.

I'm just wondering why such a facility was included if it's not possible to create a cube using this approach.

 

[GJParker]

Transformer can create cubes using multiple data sources in fact it is very good at it.

The warning message is telling you that the period dimension is not related to any measure data and as such the cube will show that.

if you are using multiple data sources they have to have at least on common field i.e.

Data source 1
Customer No
Item No
Invoice No
Date (this could be the invoice date the fields have to have the exact same name case sensitivity included)
Quantity (This would be a measure)

data source 2
Year
Period No
Week No
Date

data source 3
Customer No
Customer Name

data Source 4
Item No
Item Name

This is how transformer handles the associations when building the cube and each data source could come from a different database transformer doesn't care.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[Exie]

Correct Gary,

You cannot have isolated (eg. Un-Related) datasources. The mutliple datasources are great for filling out your dimensions.

By using a 'lookup' table such as gary's Datasource3 and Datasource4 you can reduce the size of each record retrieved in Datasource1.

As gary has pointed out, If datasource1, did not contain a matching reference to Customer No. then cognos would not know what quantities to allocate to each Customer Name.

Good example Gary.

 

[monsky]

I understand that I need to have related fields. Cross check me on this one:

I'm using an Access database containing 4 dimension tables and a single fact table. I have the following data sources already in my model:

Period
-TimeCode
-CurrentYear
-CurrentMonth

Product
-ProductCode
-ProductNumber
-ProductName
-ProductType
-ProductLine

Sales
-ProductCode
-TimeCode
-Quantity
-UnitCost
-UnitPrice
-SaleTotal

Basically, I created two dimensions named Period and Product in the Dimension Map. I dragged the CurrentYear and CurrentMonth into the Period dimension then dragged ProductLine, ProductType, and ProductName into the Product dimension. Finally I dragged the SaleTotal into the Measures window.

From hereon I tried to testbuild and I get the warnings. When I check the scope of the dimensions they are properly highlighted when I choose the measure.

Please let me know what step(s) I missed.

 

[GJParker]

monsky

your dimensions need to contain the related fields as the lowest level, and these need to be set as unique.

In the Product Dimension change the Product Name Level to use Product Code as the source and Product name as the Label.

You will need to add Time Code as the bottom level of your Period dimension.

HTH


Gary Parker
Systems Support Analyst
Manchester, England

 

[monsky]

Yes! That did it! Thanks a lot Gary.