Steps for creating star schema

[saumil287]

hi All,
I have a requirement where I had imported 8 Excel sheets which is not having any relationships.
Currently the organization is viewing different reports from each excel sheet
I have a requirement to create a single report containing data from 8 excel sheets
So what are the steps should i followto create a star schema which can display a business required report.
Thanks in advance

[blom0344]

A true starschema would rely heavily on establishing relationships. Without relationships no starschema. But you can still create query subject based on the Excel data and publish a package containing all of these. This will allow creating a report containing all isolated data in different report pages, lists , graphs etc..

[saumil287]

Hi blom,
Thanks for reply to my question. Since after a long time I am able to get this page, before I was not able to see the messsage u had replied because of some server error, The page was not displaying. I want to know the step by step process after importing the excel sheets till the star schema is created. I created a 4 reports with different data, but my organization needs only one report which contains all the data in one croostab report.
Its urgent, Your guidance will be appreciated
thanks and regards
saumil

[blom0344]

You already stated that the data in the Excel sheets is non-related. You CANNOT create a starschema (a fact joined to multiple dimensional subjects) without relationships. You also cannot generate 1 crosstab with all data if you have a collection of non-related spreadsheets

[saumil287]

Hi blom,
You are right, Currently the excel files are not related. Each excel files are for different business units. The organization requires only a single crosstab report instead of 4 different reports, So can u guide me that what steps should I follow to create a star schema after I create a links between this sheets in framework manager.
I dont know what are the process/steps that needs to be followed to create a star schema after importing the xcel files.
I am a fresher in my organization and never did the etl process in framework manager. So your guidence will be much appreciated.
Thanks and regards
saumil

[blom0344]

There is no ETL involved when using Framework manager. A framework literally only consists of metadata. You say the excel sheets are NOT related (diverse units) If the structure is identical you could perhaps define a set (union) against them. If not then how would you generate a crosstab from such non-related/ diverse data

[saumil287]

Hi blom
Some of the structures are similar but not all excel files have similar ie num of columns,orders and data types.
I want to know what others follow the steps after importing the excel files till the creation of star schema in general.
Thanks

[blom0344]

Hmm, I have the feeling I am not getting through to you  :o
Did you read the earlier replies?

[MFGF]

Hi,

Just to be clear on this, are you saying the Excel spreadsheets do not contain data that can be used to link them together?  If this is the case, you cannot create relationships between them because there are no values which link them.  This is what Blom is trying to point out.

If there are common values included, you simply select these in Framework Manager and create a relationship based on them.

Regards,

MF.

[JeroenWork]

And now for the silly answer: first combine the data in excel, then import into Cognos.

[MFGF]

Not silly at all :) The simple answers are often the best!

[blom0344]

And now for the silly answer: first combine the data in excel, then import into Cognos.

Excel is a spreadsheet containing cells. Range A1:A100 make contain some set of identifiers, whereas A101:A200 may contain values , attributes etc. The Excel file will only be useful when It represents a table or a set of related tables. That is the problem: is (and how) data related..

[saumil287]

Hi
Data in excel sheets are not related ie u cannot create inner join because u dont get the matching data.
I want to know in general whether as per the standard for framework manager, It is mandatory that a star schema must be created at the end of data modelling or is it ok if the report requirements are met without creating star schema.
thanks

[Lynn]

Forget the technology and jargon for a moment....

I don't think it is clear how the data in these different excel files would be combined to produce a single report.

It might be easier for people to help if you could explain how data that can't be joined or unioned is supposed to yield a single useful report.

[cognostechie]

STAR SCHEMA is NOT a mandatory requirement. It is perfectly fine to create reports without creating Star Schema in Framework Manager or even in the ETL process. Star Schema is definately a better solution for reporting but that does not mean you have to have it. Most of the companies I have worked for don't have Star Schema.

Inner joins are also not a mandatory requirement. Outer joins are also fine for reporting. That being said, you still need the data in Excel files to be related to each other or thru each other if you have need one report having data from all 8 Excel files. if the data is not related, you cannnot join that in Framework Manager.

[bvk.cognoise]

Hi  saumil287,

all masters are giving best guidance to you.

if  you still want to create a star schema grouping.

you have to do one thing may be its not correct process.


>before importing excel sheets into FM create a common column  in all excel sheets.

for eg : create a column name it like 'KEY' (or) copy one common column into all excel sheets               

>then import all excel sheets into FM then create relation ship between fact table and dimension table.

you can use common key for relation which we created before.you can create starschema.

>while publishing package you can hide this column.


but its not correct process every time.when your having large number of excel sheets its not possible.

hop it helps you.if its not correct answer let us wait for correct answer.


thanks
bvk