MSSQL Connection

[former_member402770]

Hi,

1) I have been provided with an SQL Query like

Select

From

Where

2) I also could able to see the above tables present in the SQL Query with an universe connection(MSSQL) based schema user.

Now, i am struck with what universe design i can approach for eg: derived table with provided sql query or to design with table and joins of the SQL Query or to write an custom sql in the report level. Pls. provide some steps for the right approach.

Thanks,

Dinya,

[Former Member · Accepted solution]

If the tables are already in your universe and so are the objects, then you can just build a Webi query with them. A derived table is not a best practice solution.

If the tables are not there:

Insert the tables into your schema, join them on the keys in your join condition.

Create the objects that you need to use in your query.

The key to your date calculations is how your fiscal period is stored. What data type is it on SQL Server? If it is datetime then you can simply use the datepart function to create further objects within the same class as your fiscal period object.

[former_member402770 · Accepted solution]

Hi,

  Can we use the below sql in derived tables. Pls put your thoughts on top of this, willit cause any performance in webi, If yes any chance of alteration in the query

sELECT KYD.month, YFD.act, KYD.ugt, KYD.no, KYD.desc, KYD.hoj, KYD.Unit, HAH.sTotal, KYD.vTotal AS EAC, KYD.sdfo, tblJobs.sdfe, ent.wefr, ent.sadity, ins.wer FROM (((KYD INNER JOIN HAH ON (KYD.ewf = HAH.ewf) AND (KYD.xc = HAH.xc)) ) INNER JOIN ubf ON KYD.sd = sd.fg INNER JOIN ins on (KYD.Instance = ins.Instance) INNER JOIN ent on (ent.Entity = ins.Entity) WHERE ((HAH.Action='sdf') AND ((KYD.Show)<>0));

Any thoughts on above for fine-tuning

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

How much time this query taking if you are running at database?Similar you can compare the refresh time at webi level.

Amit

[former_member402770 · Accepted solution]

Amit,

Thanks i just checked not taking too much time..Sorry for late reply, i was checking report refresh time post development.

Thanks,

Dinya

[amitrathi239]

Hi,

If you have already the SQL then easy step is to create derived table.create objects and use in the webi.

Amit

[former_member402770]

Hi Amit,

Thanks, i have created derived table with the already available sql below:

Select FiscalMonth, projtype

From , FiscalPeriod INNER JOIN Proj ON (FiscalPeriod .FiscalMonth = Proj .projtype)

Where (FiscalPeriod .FiscalMonth = '11/1/2012' AND projtype<>0);

Below is the output of the above query:

I am trying to get separate out the Month and Year and form as Prompt Filter in universe to display 011.2012.

One more questions can i use the inner join on from clause and where condition in an derived table because i would like to use the say for eg: FiscalMonth   and projtype!=0 as webi prompts.

Appreciate your help may be with some screenshots

Thanks,

Dinya.

[amitrathi239]

Hi,

I am not good in the writing SQL..If you can write SQL and  spilt the records then you can use in the derived table.Yes you can defined the prompts in the derived table.

If not then might be use different tables and joins.in your earlier thread i have explained with steps to combined the month and year.

Amit