How to change the datasource view of a mining model.

Hi,

I created on model in Business Intelligence Development studio.I wants to change the datasourceview(dsv) assigned for that view.for e.g

My mining Model is StudModel.dmm.

The dsv for that model is StudDSV.dsv.

I wants to change the dsv for that model to marksDSV.dsv.

How to do that in Business Intelligence Development studio.

Thanks,

Karthik.

The BI Dev Studio does not provide you with much support for this task.

You can change the DSV manually. It may be very easy or more difficult, depending on how similar the new DSV is compared to the original DSV.

Here is something you can do, manually:

- close the Mining structure designer window

- open the DMM file in BI Dev Studio (right click and select the View Code option)

- look for the <DataSourceViewID> element in the XML. Replace the content of the element with the id of your new data source view

- close and save the file

- double click on the DMM file to load it in the designer and validate the change

Now, if your new data source view contains different tables, or if those tables have differrent columns, more work needs to be done.

Note that the changes will only be visible after you re-deploy and process fully your mining structure

Hope this helps

sql

how to change column names in the grid view programatically?

I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer. If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005?

Here's my post to the SQL devs to give you an idea what I'm trying to do.

I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3...Week26. Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption".
fldfieldno Fldcaption
---- -----
11 9/07/2007
12 9/14/2007
13 9/21/2007

So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptions
where fldfieldno = 11

That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.

My current query looks like this:
SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,

So can I do something like the following?
SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...

Hi tomhirt,

So can I do something like the following? SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...

You cannot do that . Column alias can only be constant string.

Note what you need to modify is only gridview column header texts. So,you can create another query clause againstlkpdatecaptionsand assign the resultset to be your gridview column header texts.

Hope my suggestion helps

|||

ok, I think I understand the concept, but I could use some examples or pointers.

|||

It's very easy to use the approach i suggested above.

What you all need to do is to: build a connection to yourlkpdatecaptionsdatabase and fetch the data (use the smae parameters as you did when you get the first table). As to modify the datagridview column headers, see msdn documnet:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.datacontrolfield.aspx

|||

I know it was a long weekend and I'm brain dead this morning, but I don't see any type of example where a data connection is looped to assign values to column header text.

|||

sqlcommand cmd=new sqlcommand("Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where XXXXXX")// this will retrun a collection of weeks.using(sqlconnection con=new sqlconnection("your con-string")){ cmd.connection=con; con.Open(); sqldatareader rder; rder=cmd.ExecuteReader();int tmp=0;while(rder.Read()) { yourdatagrid.columns[tmp].HeaderText=rder[tmp].ToString(); tmp++; } }

Hope my suggsestion helps|||

Perfect!!! That's what I was missing. Thanks!!!!

How to call a Store proc inside a view

Can anyone tell me how to call a sp inside a view.I dont think you can do that
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:05347F6A-25D7-44B0-BA89-F22495D1C417@.microsoft.com...
> Can anyone tell me how to call a sp inside a view.
>
>|||You will need to set-up a linked server (LOCALHOST), and then...
CREATE VIEW vw_Test
AS
SELECT *
FROM OPENQUERY(LOCALHOST, 'SET FMTONLY OFF; EXEC pubs..Your_SP')
However, the SP is actually executed twice, so there is a performance
cost. If the SP performs updates/inserts, these are also performed
twice which is probably not what you want.|||You are right.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Pradeep Kutty" wrote:

> I dont think you can do that
> "Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
> news:05347F6A-25D7-44B0-BA89-F22495D1C417@.microsoft.com...
>
>|||However, if your SP doesn't create temp tables, you can do this (only
called once):
CREATE VIEW vw_Test
AS
SELECT *
FROM OPENQUERY(LOCALHOST, 'EXEC pubs..Your_SP')|||Not easily. You are asking the wrong question really. Call your view
from a proc if you need to, not the other way around. Maybe you could
explain just why you want to do this.
David Portas
SQL Server MVP
--|||I have 10 tables with common fields(splitted 1 into 10 due to easy access of
data). I want to make a view of it by combining all the tables using union
all.
From that view I will make a self join and do my operations. I am ok with
all these tasks. But my worry is if a new table is being added tomorrow I
have to hard code the 11th table in my view. I also have a log of how many
tables are added in a common table. So I want the view to autmatically add
the new table to the view with the help of the log table , which I will be
able to do it in an SP.
"David Portas" wrote:

> Not easily. You are asking the wrong question really. Call your view
> from a proc if you need to, not the other way around. Maybe you could
> explain just why you want to do this.
> --
> David Portas
> SQL Server MVP
> --
>|||Surely, you would know when the table is being added. You could do the
ALTER VIEW at that time and nothing else is required.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:19BD2C7D-D08C-4A5B-ABBE-472651BBE8B2@.microsoft.com...
I have 10 tables with common fields(splitted 1 into 10 due to easy access of
data). I want to make a view of it by combining all the tables using union
all.
From that view I will make a self join and do my operations. I am ok with
all these tasks. But my worry is if a new table is being added tomorrow I
have to hard code the 11th table in my view. I also have a log of how many
tables are added in a common table. So I want the view to autmatically add
the new table to the view with the help of the log table , which I will be
able to do it in an SP.
"David Portas" wrote:

> Not easily. You are asking the wrong question really. Call your view
> from a proc if you need to, not the other way around. Maybe you could
> explain just why you want to do this.
> --
> David Portas
> SQL Server MVP
> --
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:543962

> I have 10 tables with common fields(splitted 1 into 10 due to easy access
of
> data).
Your problem makes one thing clear: splitting tables with common
columns makes data HARDER to access not easier. By doing this you force
youself either to make lots of messy UNIONs or (worse) to use dynamic
SQL everywhere.
Splitting data across multiple identical tables is commonly understood
to be a big design error. In SQL2000 it makes sense in certain fairly
limited circumstances in support of partitioned views. If you are stuck
with this design for now then look up Partitioned Views in Books
Online. Basically you can create the view once and then reference it
everywhere as if it were a single table. Don't reference the base
tables.
David Portas
SQL Server MVP
--|||loopback query (though not really recommended due to perf penalty) would
allow you to..
e.g.
exec sp_serveroption 'srv','data access','true'
go
if object_id('_v','v') is not null
drop view _v
go
create view _v
as
select *
from openquery(srv,'set fmtonly off; exec sp_lock')x
go
select * from _v
go
-oj
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:05347F6A-25D7-44B0-BA89-F22495D1C417@.microsoft.com...
> Can anyone tell me how to call a sp inside a view.
>
>

how to calculate percentage

b/w two colums?
i have two numberic columns
i want percentage of column2/column1
They are on separate table.. l am intended to creat view with my results.
thanksjust do select table1.column1/table2.column2 from table1,table2

are the tables related in anyways? are they just numeric values that have nothing to do with each other? i need more information to help|||yes they are related and numberic
it gives me 0{zero] through in the results

i did table1.column1/table2.column2 as Percentage from table1,table2
do i have to create column? why outputing 0's only?|||Probably one or both columns are declared as integer. Try:

select convert(numeric(38, 19), table1.column1)/convert(numeric(38, 19), table2.column2) from table1,table2|||error
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.table1' does not match with a table name or alias name used in the query.

this column name has space and im using it like

select convert(numeric(38, 19), table1.[column1 name])/convert(numeric(38, 19), table2.column2)|||i just ran it and it worked fine

select convert(numeric(38,19), table1.[a c])/convert(numeric(38,19), table2.[b d])
from table1, table2

i guess double check that you are using the right DB, and you've typed everything correctly. Otherwise can you post up your query for me to look at?|||thks
it's working|||do you know how to format this to 2 decimal places?
like 20 instead of 0.02|||Umm...I'll just take a guess here...multiply by 100?

I assume you meant that 0.02 should display as 2, not 20...

How to calculate category value - perhaps use subqueries?

Hello all - I am trying to create a view that will calculate a category fiel
d
value based on several conditions regarding another field.
i.e. having a table PayCodes (employeeID, paycode)
employeeID paycode
--
1 01
1 02
1 02S
1 03S
1 71
2 01
2 02S
2 71
3 02
3 03H
4 01
4 02
I need to create a view that will output employeeID and overtimeType where
overtimeType = 1 if an employeeID has 02S or 02H
overtimeType = 2 if an employeeID has 03S or 03H
overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
So given the above table, the view should return:
employeeID overtimeType
--
1 3
2 1
3 2
4 0
Any ideas?
Thanks in advance!!
Hellman.On Tue, 4 Oct 2005 10:59:03 -0700, Hellman wrote:
(snip)
Hi Hellman,
I just posted a reply to your question in the .mseq group.
Please post your questions to one group only. And if you really feel
that a question fits two groups, use the crossposting ability of your
software to post one message to both groups at once, so that others will
see if there's already a reply in the other group, and we have all
reactions in one thread.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to calculate category value - perhaps use subqueries?

Hello all - I am trying to create a view that will calculate a category field
value based on several conditions regarding another field.
i.e. having a table PayCodes (employeeID, paycode)
employeeID paycode
1 01
1 02
1 02S
1 03S
1 71
2 01
2 02S
2 71
3 02
3 03H
4 01
4 02
I need to create a view that will output employeeID and overtimeType where
overtimeType = 1 if an employeeID has 02S or 02H
overtimeType = 2 if an employeeID has 03S or 03H
overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
So given the above table, the view should return:
employeeID overtimeType
1 3
2 1
3 2
4 0
Any ideas?
Thanks in advance!!
Hellman.
On Tue, 4 Oct 2005 10:36:02 -0700, Hellman wrote:

>Hello all - I am trying to create a view that will calculate a category field
>value based on several conditions regarding another field.
>i.e. having a table PayCodes (employeeID, paycode)
>employeeID paycode
>--
> 1 01
> 1 02
> 1 02S
> 1 03S
> 1 71
> 2 01
> 2 02S
> 2 71
> 3 02
> 3 03H
> 4 01
> 4 02
>I need to create a view that will output employeeID and overtimeType where
> overtimeType = 1 if an employeeID has 02S or 02H
> overtimeType = 2 if an employeeID has 03S or 03H
> overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
> overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
>So given the above table, the view should return:
>employeeID overtimeType
>--
> 1 3
> 2 1
> 3 2
> 4 0
>Any ideas?
>Thanks in advance!!
>Hellman.
>
Hi Hellman,
I believe that the following will work:
SELECT employeeID,
SUM(overtimeType) AS overtimeType
FROM (SELECT DISTINCT
employeeID,
CASE
WHEN paycode LIKE '02[HS]' THEN 1
WHEN paycode LIKE '03[HS]' THEN 2
ELSE 0
END AS overtimeType
FROM PayCodes) AS d
GROUP BY employeeID
(untested - see www.aspfaq.com/5006 if you prefer tested results)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)