Could anyone please advise how to modify the location of
the datafile and logfile of the model database?
I need to change them from drive C to drive D.
Thank you.Hi Nathalie,
See the following knowledge base article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Nathalie" <nathalbr@.cellcom.co.il> wrote in message
news:7e5701c35a7f$5742ae00$a001280a@.phx.gbl...
> Could anyone please advise how to modify the location of
> the datafile and logfile of the model database?
> I need to change them from drive C to drive D.
> Thank you.sql
Showing posts with label model. Show all posts
Showing posts with label model. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Modelling Tool Help
Hello,
For management that doesn't have the software I do, I've been using a PDF postscript utility to print the model and give it to them. However, I have to fit the model for the specific page (which is really small, and you have to zoom in), or you have to view it on multiple pages. Well multiple pages can be a pain, because the table you are looking at is on page 1, but the relationships are on page 12, 22, or 50, for example. So I was looking for a tool that is cheap enough to implement that we could look at the entire model on one "surface" (not necessary to print), that the model could be viewed in.
Thanks,
BrianHow about visio?|||Yes, we have that too, and I did find the Meta Information Model Bridge that converts to different formats, but we are looking for a general viewer without the data modelling need. It may just have to be Visio.
Thanks,
Briansql
Modelling a Time dimension with date & time & time zone info
Hello,
Suppose you have original table having columns:
GMTDate (KeyColumn)
GMTMinute (calculated column from GMTDate or populated by algorithm)
GMTHour (calculated column from GMTDate or populated by algorithm)
GMTDay (...)
GMTMonth (...)
GMTYear (...)
That original table is probably populated by some time generation code you have. So we can extend that code to generate more columns or create calculated columns on DSV:
GMTPlus1Minute
GMTPlus1Hour
GMTPlus1Day
GMTPlus1Month
GMTPlus1Year
We can create this way other sets of columns. Note that we do not need columns like GMTPlus1Minute, GMTPlus2Minute ,... We can have just Minute column. But i suppose later we can not have shared Minute attribute.
When we create time dimension we would have key attribute bound to GMTDate. The fact tables would also contain GMTDate and we will use those in the relationships. Other columns would be used for attributes used in different hierarchies.
USA Time Hierarchy
"Year" level <- GMTPlus5Year attribute
"Month" level <- GMTPlus5Month attribute
"Day" level <- GMTPlus5Day attribute
"Hour" level <- GMTPlus5Hour attribute
"Minute" level <- GMTPlus5Minute attribute
It really depends what user interface you have and how efficient it can hide different things (is it easier to hide dimensions than hierarchies or it does not matter).
|||
How would you model a time dimension such that data in transactional source can be viewed from different time zones?
Basically I need not only the date but also the time (minute granularity is enough).
for instance, if we have a datetime like
1st jan 2005 at 01:45 AM in GMT stored in the db,
a user from US would see it as 31 Dec 2004 at 08:45 PM in GMT-5 time zone and someone in Asia would see the same transaction at 1st Jan 2005 6:45 AM in GMT+5 timezone.
This way the aggregates would change according to where the data is viewed from, but that is what my users want...
I considered having several time dimensions (one per time zone), but I already have time playing 8 different roles in my cube, so 8*24 time dimensions seems a bit akward...
Thanks
Were you able to find out anything on this?
|||Not sure if this is even more awkward.Suppose you have original table having columns:
GMTDate (KeyColumn)
GMTMinute (calculated column from GMTDate or populated by algorithm)
GMTHour (calculated column from GMTDate or populated by algorithm)
GMTDay (...)
GMTMonth (...)
GMTYear (...)
That original table is probably populated by some time generation code you have. So we can extend that code to generate more columns or create calculated columns on DSV:
GMTPlus1Minute
GMTPlus1Hour
GMTPlus1Day
GMTPlus1Month
GMTPlus1Year
We can create this way other sets of columns. Note that we do not need columns like GMTPlus1Minute, GMTPlus2Minute ,... We can have just Minute column. But i suppose later we can not have shared Minute attribute.
When we create time dimension we would have key attribute bound to GMTDate. The fact tables would also contain GMTDate and we will use those in the relationships. Other columns would be used for attributes used in different hierarchies.
USA Time Hierarchy
"Year" level <- GMTPlus5Year attribute
"Month" level <- GMTPlus5Month attribute
"Day" level <- GMTPlus5Day attribute
"Hour" level <- GMTPlus5Hour attribute
"Minute" level <- GMTPlus5Minute attribute
It really depends what user interface you have and how efficient it can hide different things (is it easier to hide dimensions than hierarchies or it does not matter).
|||
You might be interested to read this entry on my blog, where I discuss this problem:
http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!367.entry
|||I think the adding additional columns to you time table and treating them as translations of the time member names would a solution worth looking at. This gives you the benifit of also being able to give formats that are cultur specific. On the downside, your timezone is based on your UI culture which doesn't work for timezones within the same country.
For populating the localized time columns you could write some managed code that uses .Net to convert to the date/time to the appropriate timezone and then format in the desired culture.
Modelling a Time dimension with date & time & time zone info
Hello,
Suppose you have original table having columns:
GMTDate (KeyColumn)
GMTMinute (calculated column from GMTDate or populated by algorithm)
GMTHour (calculated column from GMTDate or populated by algorithm)
GMTDay (...)
GMTMonth (...)
GMTYear (...)
That original table is probably populated by some time generation code you have. So we can extend that code to generate more columns or create calculated columns on DSV:
GMTPlus1Minute
GMTPlus1Hour
GMTPlus1Day
GMTPlus1Month
GMTPlus1Year
We can create this way other sets of columns. Note that we do not need columns like GMTPlus1Minute, GMTPlus2Minute ,... We can have just Minute column. But i suppose later we can not have shared Minute attribute.
When we create time dimension we would have key attribute bound to GMTDate. The fact tables would also contain GMTDate and we will use those in the relationships. Other columns would be used for attributes used in different hierarchies.
USA Time Hierarchy
"Year" level <- GMTPlus5Year attribute
"Month" level <- GMTPlus5Month attribute
"Day" level <- GMTPlus5Day attribute
"Hour" level <- GMTPlus5Hour attribute
"Minute" level <- GMTPlus5Minute attribute
It really depends what user interface you have and how efficient it can hide different things (is it easier to hide dimensions than hierarchies or it does not matter).
|||
How would you model a time dimension such that data in transactional source can be viewed from different time zones?
Basically I need not only the date but also the time (minute granularity is enough).
for instance, if we have a datetime like
1st jan 2005 at 01:45 AM in GMT stored in the db,
a user from US would see it as 31 Dec 2004 at 08:45 PM in GMT-5 time zone and someone in Asia would see the same transaction at 1st Jan 2005 6:45 AM in GMT+5 timezone.
This way the aggregates would change according to where the data is viewed from, but that is what my users want...
I considered having several time dimensions (one per time zone), but I already have time playing 8 different roles in my cube, so 8*24 time dimensions seems a bit akward...
Thanks
Were you able to find out anything on this?
|||Not sure if this is even more awkward.Suppose you have original table having columns:
GMTDate (KeyColumn)
GMTMinute (calculated column from GMTDate or populated by algorithm)
GMTHour (calculated column from GMTDate or populated by algorithm)
GMTDay (...)
GMTMonth (...)
GMTYear (...)
That original table is probably populated by some time generation code you have. So we can extend that code to generate more columns or create calculated columns on DSV:
GMTPlus1Minute
GMTPlus1Hour
GMTPlus1Day
GMTPlus1Month
GMTPlus1Year
We can create this way other sets of columns. Note that we do not need columns like GMTPlus1Minute, GMTPlus2Minute ,... We can have just Minute column. But i suppose later we can not have shared Minute attribute.
When we create time dimension we would have key attribute bound to GMTDate. The fact tables would also contain GMTDate and we will use those in the relationships. Other columns would be used for attributes used in different hierarchies.
USA Time Hierarchy
"Year" level <- GMTPlus5Year attribute
"Month" level <- GMTPlus5Month attribute
"Day" level <- GMTPlus5Day attribute
"Hour" level <- GMTPlus5Hour attribute
"Minute" level <- GMTPlus5Minute attribute
It really depends what user interface you have and how efficient it can hide different things (is it easier to hide dimensions than hierarchies or it does not matter).
|||
You might be interested to read this entry on my blog, where I discuss this problem:
http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!367.entry
|||I think the adding additional columns to you time table and treating them as translations of the time member names would a solution worth looking at. This gives you the benifit of also being able to give formats that are cultur specific. On the downside, your timezone is based on your UI culture which doesn't work for timezones within the same country.
For populating the localized time columns you could write some managed code that uses .Net to convert to the date/time to the appropriate timezone and then format in the desired culture.
sqlModeling a Matrix
What is the best way to model a matrix in terms of table design? I want to
model a sociogram, which is basically a network diagram with weights
assigned to the edges. Once, established I would want to use it to do matrix
computations (matrix algebra). So, the simple version would be something
like the following:
0 1 1
1 0 1
1 1 0
The rows and columns will constitute the same set of data. In other words,
the matrix will reflect the relationship between like entities from the same
set. Relationships between persons of a given set of people, for example.
Just wondering if there is a clever way of modeling this, or if I should
just use a table with three columns: entityA, entityB, edgeValue...
Thanks
BKGet a copy of SQL FOR SMARTIES; there is a whole chapter on matrix math
in SQL.
CREATE TABLE Martix
(i INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n1>>,
j INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n2>>,
k INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n3>>,
element_value FLOAT NOT NULL,
PRIMARY KEY (i, j, k));
Then you talk about graphs in SQL with a sociogram -- which is it?|||(grabs SQL FOR SMARTIES off of his bookshelf...and there it is on p303! ..
should have checked there first)
Excellent, thanks! I thought that might be the best way to do, but just
wanted to validate my gut feeling...
The answer to your question is: both. Really its just a matrix
representation of a sociogram, so the edges will represent the relationships
between the nodes (and the elements of the matrix). The row and column
vectors will be the same and will represent the "people".
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115680013.666216.159290@.f14g2000cwb.googlegroups.com...
> Get a copy of SQL FOR SMARTIES; there is a whole chapter on matrix math
> in SQL.
> CREATE TABLE Martix
> (i INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n1>>,
> j INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n2>>,
> k INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n3>>,
> element_value FLOAT NOT NULL,
> PRIMARY KEY (i, j, k));
> Then you talk about graphs in SQL with a sociogram -- which is it?
>|||BK,
See http://groups.google.co.uk/groups?q=BBEB95_9DBB7E for an example.
Steve Kass
Drew University
BK wrote:
>What is the best way to model a matrix in terms of table design? I want to
>model a sociogram, which is basically a network diagram with weights
>assigned to the edges. Once, established I would want to use it to do matri
x
>computations (matrix algebra). So, the simple version would be something
>like the following:
>0 1 1
>1 0 1
>1 1 0
>The rows and columns will constitute the same set of data. In other words,
>the matrix will reflect the relationship between like entities from the sam
e
>set. Relationships between persons of a given set of people, for example.
>Just wondering if there is a clever way of modeling this, or if I should
>just use a table with three columns: entityA, entityB, edgeValue...
>Thanks
>BK
>
>
model a sociogram, which is basically a network diagram with weights
assigned to the edges. Once, established I would want to use it to do matrix
computations (matrix algebra). So, the simple version would be something
like the following:
0 1 1
1 0 1
1 1 0
The rows and columns will constitute the same set of data. In other words,
the matrix will reflect the relationship between like entities from the same
set. Relationships between persons of a given set of people, for example.
Just wondering if there is a clever way of modeling this, or if I should
just use a table with three columns: entityA, entityB, edgeValue...
Thanks
BKGet a copy of SQL FOR SMARTIES; there is a whole chapter on matrix math
in SQL.
CREATE TABLE Martix
(i INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n1>>,
j INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n2>>,
k INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n3>>,
element_value FLOAT NOT NULL,
PRIMARY KEY (i, j, k));
Then you talk about graphs in SQL with a sociogram -- which is it?|||(grabs SQL FOR SMARTIES off of his bookshelf...and there it is on p303! ..
should have checked there first)
Excellent, thanks! I thought that might be the best way to do, but just
wanted to validate my gut feeling...
The answer to your question is: both. Really its just a matrix
representation of a sociogram, so the edges will represent the relationships
between the nodes (and the elements of the matrix). The row and column
vectors will be the same and will represent the "people".
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115680013.666216.159290@.f14g2000cwb.googlegroups.com...
> Get a copy of SQL FOR SMARTIES; there is a whole chapter on matrix math
> in SQL.
> CREATE TABLE Martix
> (i INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n1>>,
> j INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n2>>,
> k INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n3>>,
> element_value FLOAT NOT NULL,
> PRIMARY KEY (i, j, k));
> Then you talk about graphs in SQL with a sociogram -- which is it?
>|||BK,
See http://groups.google.co.uk/groups?q=BBEB95_9DBB7E for an example.
Steve Kass
Drew University
BK wrote:
>What is the best way to model a matrix in terms of table design? I want to
>model a sociogram, which is basically a network diagram with weights
>assigned to the edges. Once, established I would want to use it to do matri
x
>computations (matrix algebra). So, the simple version would be something
>like the following:
>0 1 1
>1 0 1
>1 1 0
>The rows and columns will constitute the same set of data. In other words,
>the matrix will reflect the relationship between like entities from the sam
e
>set. Relationships between persons of a given set of people, for example.
>Just wondering if there is a clever way of modeling this, or if I should
>just use a table with three columns: entityA, entityB, edgeValue...
>Thanks
>BK
>
>
model report - role attributes unavailable
Hi guys!
In my report model project Im trying to use the attributes of an entity.
This entity has (a number of) subroles coupled to it, which all are available
in the ReportBuilder-app once I drag any entity ground-level attribute on to
the canvas.
Unfortunately Im not able to put out the attributes in the subroles even
though they are visible among the ReportBuilder entity-props so they are not
hidden or anything.
I can drag these role-attribs out, but the mousepointer changes to that
forbidden-sign-thingie when I drag them out. For some reaseon RB does not
want to cooperate here, and Im not able to figure out why.
Anybody recognize this and care to comment?
DavidI found the reason; the cardinality was set to optional-many in both
directions of the role.
changing it to be optional-many/optional-one resolved this.
Hope this helps someone else!
David
"David Sundström" <DavidSundstrm@.discussions.microsoft.com> wrote in message
news:83DE441D-E013-4280-9427-4A7557BB1FB1@.microsoft.com...
> Hi guys!
> In my report model project Im trying to use the attributes of an entity.
> This entity has (a number of) subroles coupled to it, which all are
> available
> in the ReportBuilder-app once I drag any entity ground-level attribute on
> to
> the canvas.
> Unfortunately Im not able to put out the attributes in the subroles even
> though they are visible among the ReportBuilder entity-props so they are
> not
> hidden or anything.
> I can drag these role-attribs out, but the mousepointer changes to that
> forbidden-sign-thingie when I drag them out. For some reaseon RB does not
> want to cooperate here, and Im not able to figure out why.
> Anybody recognize this and care to comment?
> David
In my report model project Im trying to use the attributes of an entity.
This entity has (a number of) subroles coupled to it, which all are available
in the ReportBuilder-app once I drag any entity ground-level attribute on to
the canvas.
Unfortunately Im not able to put out the attributes in the subroles even
though they are visible among the ReportBuilder entity-props so they are not
hidden or anything.
I can drag these role-attribs out, but the mousepointer changes to that
forbidden-sign-thingie when I drag them out. For some reaseon RB does not
want to cooperate here, and Im not able to figure out why.
Anybody recognize this and care to comment?
DavidI found the reason; the cardinality was set to optional-many in both
directions of the role.
changing it to be optional-many/optional-one resolved this.
Hope this helps someone else!
David
"David Sundström" <DavidSundstrm@.discussions.microsoft.com> wrote in message
news:83DE441D-E013-4280-9427-4A7557BB1FB1@.microsoft.com...
> Hi guys!
> In my report model project Im trying to use the attributes of an entity.
> This entity has (a number of) subroles coupled to it, which all are
> available
> in the ReportBuilder-app once I drag any entity ground-level attribute on
> to
> the canvas.
> Unfortunately Im not able to put out the attributes in the subroles even
> though they are visible among the ReportBuilder entity-props so they are
> not
> hidden or anything.
> I can drag these role-attribs out, but the mousepointer changes to that
> forbidden-sign-thingie when I drag them out. For some reaseon RB does not
> want to cooperate here, and Im not able to figure out why.
> Anybody recognize this and care to comment?
> David
Model Question
Does anyone know if a change is made to a model, will reports created based
on it be affected? Or, once created, are these reports independent?
ThanksYes,
--
Regards,
Raja
â'Smile is a curve that sets everything straight"
"TomT" wrote:
> Does anyone know if a change is made to a model, will reports created based
> on it be affected? Or, once created, are these reports independent?
> Thanks|||Raja, thanks for your reply. Do you mean yes, they will be affected, or yes
they are independent?
"Raja" wrote:
> Yes,
> --
> Regards,
> Raja
> â'Smile is a curve that sets everything straight"
>
> "TomT" wrote:
> > Does anyone know if a change is made to a model, will reports created based
> > on it be affected? Or, once created, are these reports independent?
> >
> > Thanks|||Hello Tom,
I think Raja means that the report will be affected.
You could refer the article:
Creating a Report Model Project
http://msdn2.microsoft.com/en-us/ms156285.aspx
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Ok, thanks very much.
"Wei Lu [MSFT]" wrote:
> Hello Tom,
> I think Raja means that the report will be affected.
> You could refer the article:
> Creating a Report Model Project
> http://msdn2.microsoft.com/en-us/ms156285.aspx
> Thanks!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
on it be affected? Or, once created, are these reports independent?
ThanksYes,
--
Regards,
Raja
â'Smile is a curve that sets everything straight"
"TomT" wrote:
> Does anyone know if a change is made to a model, will reports created based
> on it be affected? Or, once created, are these reports independent?
> Thanks|||Raja, thanks for your reply. Do you mean yes, they will be affected, or yes
they are independent?
"Raja" wrote:
> Yes,
> --
> Regards,
> Raja
> â'Smile is a curve that sets everything straight"
>
> "TomT" wrote:
> > Does anyone know if a change is made to a model, will reports created based
> > on it be affected? Or, once created, are these reports independent?
> >
> > Thanks|||Hello Tom,
I think Raja means that the report will be affected.
You could refer the article:
Creating a Report Model Project
http://msdn2.microsoft.com/en-us/ms156285.aspx
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Ok, thanks very much.
"Wei Lu [MSFT]" wrote:
> Hello Tom,
> I think Raja means that the report will be affected.
> You could refer the article:
> Creating a Report Model Project
> http://msdn2.microsoft.com/en-us/ms156285.aspx
> Thanks!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
Model Query Designer
I added the RS extension to RSReportServer.config. Then, I restarted
the report server. Now it works!Could you please explain how and where exactly in the config file you added
it?
Thanks,
Nancy
<fmatamoros@.yahoo.com> wrote in message
news:1179967924.559739.15150@.d30g2000prg.googlegroups.com...
>I added the RS extension to RSReportServer.config. Then, I restarted
> the report server. Now it works!
>sql
the report server. Now it works!Could you please explain how and where exactly in the config file you added
it?
Thanks,
Nancy
<fmatamoros@.yahoo.com> wrote in message
news:1179967924.559739.15150@.d30g2000prg.googlegroups.com...
>I added the RS extension to RSReportServer.config. Then, I restarted
> the report server. Now it works!
>sql
Model Information
Is it possible to get hold of the fit parameters out of the prediction models in order to use them without going back to the database.
E.g. With the linear fit model, y = ax + b, is it possible to get hold of a and b?
This way I could use the fit equation and parameters directly within my C# code, rather than making many costly connections back to the database.You can get all of the parameters from the model's content rowset. You can see the rowset by using the generic viewer (there's a viewer drop-down above the mining viewers) or by executing the query SELECT * FROM [My Model].CONTENT.
At www.sqlserverdatamining.com there is an additional plug-in viewer that makes it easier to view the content rowset as well.
You can query the content rowset through code using standard connection and command objects.|||Thanks of replying, but this still doesn't seem to give me the actual parameters of the equation. I can see them in the 'mining legend' window, though I can only get this to come up for the linear fit model.
Also it seems to be giving me strange fit parameters. I created a table of data with x = 1 - 20 and y = 2x. When I performed a linear fit on this, the equation I was given in 'Mining Legend' was Y = 21.000+1.999*(X-10.500). This seems a bit odd, especially when I pasted the data into excel and got the expected answer of a zero intercept and a gradient of 2.|||
Greetings, MagorGirl:
what you are seeing in the Mining Legend is the algebraic form of the linear fit you are looking for.
Because 1.99999*(-10.5) is -21 [up to roundoff], once you expand the parentheses you indeed get Y = 0.000 + 1.999*X
Regression formulas are represented in such form in order to highlight the mean values of the each regressor and of the regression target.
In this case 10.5 is obviously the mean of x and 21 is the mean of y.
So by looking at this format you can take in all the essential stats in one glance.
If you use a regression formula in this format for numeric prediction, the numeric resultsare going to be indentical of course to when you use the equivalent linear fit in the form of a*x+b
|||Thanks AlexeiThere's still a problem with the accuracy of this fit though, the intercept does not cancel out fully unless you use quite a lot of rounding. Is there a tendency in the model not to overfit? Does the SQL regression require more data than the excel regression?
I was also looking at the neural networks model and trying to see what equation it produces.
Is there a table anywhere that contains the explanations for the different valuetypes? I've found out that in the linear regression output, valuetype of 3 = intercept and 7 is gradient (Thanks to Jamie for pointing out the code for the improved viewer). I've yet to find a table explaining all the values and the general content of the node_distribution table. Is there any more technical documentation out there, there doesn't seem to be much in books online.
Thanks|||> Is there a tendency in the model not to overfit?
That's exactly right. There is a mechanism of Bayesian priors to reduce the risk of overfitting. The more data you have the less noticeable the influence of the priors is going to be.
I am not sure where the valuetype descriptions are published...|||
I know this is not the level of detail you're looking for but you can at least see the symbolic names for the value types (and other constants used in content) in "\Program Files\Microsoft SQL Server\90\SDK\Include\oledbdm.h" - e.g.:
#define DMMVALUETYPE_COEFFICIENT ( 7 )
We hope to have more technical information available in web updates to Books Online.
|||The GenericContentTreeViewer downloadable from sqlserverdatamining.com decodes all the content node types and value types for you to make it easier to understand. I recommend giving it a try.|||Ok, so I've installed the generic viewer and I can see that some values_types are continuous, some coefficients etc. I can see how the linear fit equation can be built up from this.I would still like to create an equation from the Neural Network model. I used the same data set as for my linear fit, y = 2x. I told the model to have zero hidden nodes. I basically get two value_types back, which are both continuos. X = 5.5 and Y = 11. Obviously this shows that y = 2x, but how do I construct an equation from these?
Thanks for all the help so far
Model Information
Is it possible to get hold of the fit parameters out of the prediction models in order to use them without going back to the database.
E.g. With the linear fit model, y = ax + b, is it possible to get hold of a and b?
This way I could use the fit equation and parameters directly within my C# code, rather than making many costly connections back to the database.You can get all of the parameters from the model's content rowset. You can see the rowset by using the generic viewer (there's a viewer drop-down above the mining viewers) or by executing the query SELECT * FROM [My Model].CONTENT.
At www.sqlserverdatamining.com there is an additional plug-in viewer that makes it easier to view the content rowset as well.
You can query the content rowset through code using standard connection and command objects.|||Thanks of replying, but this still doesn't seem to give me the actual parameters of the equation. I can see them in the 'mining legend' window, though I can only get this to come up for the linear fit model.
Also it seems to be giving me strange fit parameters. I created a table of data with x = 1 - 20 and y = 2x. When I performed a linear fit on this, the equation I was given in 'Mining Legend' was Y = 21.000+1.999*(X-10.500). This seems a bit odd, especially when I pasted the data into excel and got the expected answer of a zero intercept and a gradient of 2.|||
Greetings, MagorGirl:
what you are seeing in the Mining Legend is the algebraic form of the linear fit you are looking for.
Because 1.99999*(-10.5) is -21 [up to roundoff], once you expand the parentheses you indeed get Y = 0.000 + 1.999*X
Regression formulas are represented in such form in order to highlight the mean values of the each regressor and of the regression target.
In this case 10.5 is obviously the mean of x and 21 is the mean of y.
So by looking at this format you can take in all the essential stats in one glance.
If you use a regression formula in this format for numeric prediction, the numeric resultsare going to be indentical of course to when you use the equivalent linear fit in the form of a*x+b|||Thanks Alexei
There's still a problem with the accuracy of this fit though, the intercept does not cancel out fully unless you use quite a lot of rounding. Is there a tendency in the model not to overfit? Does the SQL regression require more data than the excel regression?
I was also looking at the neural networks model and trying to see what equation it produces.
Is there a table anywhere that contains the explanations for the different valuetypes? I've found out that in the linear regression output, valuetype of 3 = intercept and 7 is gradient (Thanks to Jamie for pointing out the code for the improved viewer). I've yet to find a table explaining all the values and the general content of the node_distribution table. Is there any more technical documentation out there, there doesn't seem to be much in books online.
Thanks|||> Is there a tendency in the model not to overfit?
That's exactly right. There is a mechanism of Bayesian priors to reduce the risk of overfitting. The more data you have the less noticeable the influence of the priors is going to be.
I am not sure where the valuetype descriptions are published...
|||
I know this is not the level of detail you're looking for but you can at least see the symbolic names for the value types (and other constants used in content) in "\Program Files\Microsoft SQL Server\90\SDK\Include\oledbdm.h" - e.g.:
#define DMMVALUETYPE_COEFFICIENT ( 7 )
We hope to have more technical information available in web updates to Books Online.
|||The GenericContentTreeViewer downloadable from sqlserverdatamining.com decodes all the content node types and value types for you to make it easier to understand. I recommend giving it a try.|||Ok, so I've installed the generic viewer and I can see that some values_types are continuous, some coefficients etc. I can see how the linear fit equation can be built up from this.I would still like to create an equation from the Neural Network model. I used the same data set as for my linear fit, y = 2x. I told the model to have zero hidden nodes. I basically get two value_types back, which are both continuos. X = 5.5 and Y = 11. Obviously this shows that y = 2x, but how do I construct an equation from these?
Thanks for all the help so far
Model generation (for Report Builder)
I have had to add Calculated columns to some tables in my Data Source View in
order to correctly link some tables but this kills the model generation.
For example, although the job table has a CompanyID column, the job_task
table does not - but I need to be able to lookup a status in CompanyReason
which is indexed on CompanyID and PauseReason (this column is in job_task).
So I added 'theCompanyID' as a Calculated columns to job_task with the
following code:
SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
j.job_id = jt.job_id
Makes sense? But when I create a model with it - this is the error message:
--
An error occurred while executing a command.
Message: Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.
Line 5: Incorrect syntax near ')'.
Command:
SELECT
COUNT([notes]),
COUNT(DISTINCT [notes]),
COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
j.job_id = jt.job_id)),
COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j
on j.job_id = jt.job_id)),
COUNT('task_status'),
COUNT(DISTINCT 'task_status')
FROM [dbo].[job_task] t
--
Of course, the error is correct - what I need to know is how to either
bypass the column uniqueness test FOR THIS ONE column or another method to
create the link in the Data Source View.
Thank you,
ScottI would use SSIS to get this set up. I have not done such a thing, but since
SSIS can provide report datasources that's the route I would look at.
"ScottB" <ScottB@.discussions.microsoft.com> wrote in message
news:95A89A5B-F36B-4056-96BF-A748B613CA25@.microsoft.com...
>I have had to add Calculated columns to some tables in my Data Source View
>in
> order to correctly link some tables but this kills the model generation.
> For example, although the job table has a CompanyID column, the job_task
> table does not - but I need to be able to lookup a status in CompanyReason
> which is indexed on CompanyID and PauseReason (this column is in
> job_task).
> So I added 'theCompanyID' as a Calculated columns to job_task with the
> following code:
> SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
> j.job_id = jt.job_id
>
> Makes sense? But when I create a model with it - this is the error
> message:
> --
> An error occurred while executing a command.
> Message: Cannot perform an aggregate function on an expression containing
> an
> aggregate or a subquery.
> Line 5: Incorrect syntax near ')'.
> Command:
> SELECT
> COUNT([notes]),
> COUNT(DISTINCT [notes]),
> COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
> j.job_id = jt.job_id)),
> COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job
> j
> on j.job_id = jt.job_id)),
> COUNT('task_status'),
> COUNT(DISTINCT 'task_status')
> FROM [dbo].[job_task] t
> --
> Of course, the error is correct - what I need to know is how to either
> bypass the column uniqueness test FOR THIS ONE column or another method to
> create the link in the Data Source View.
> Thank you,
> Scott|||Hi,
I am new to SSIS. I have been searching for some examples to
use Expression Builder in SSIS to use derived columns
transformation...I basically want to add a new column to my data source
on the run and add this column as the 2nd column before loading into
destination. How can I achieve this? Plz help. Is there a link where I
can get examples online'
order to correctly link some tables but this kills the model generation.
For example, although the job table has a CompanyID column, the job_task
table does not - but I need to be able to lookup a status in CompanyReason
which is indexed on CompanyID and PauseReason (this column is in job_task).
So I added 'theCompanyID' as a Calculated columns to job_task with the
following code:
SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
j.job_id = jt.job_id
Makes sense? But when I create a model with it - this is the error message:
--
An error occurred while executing a command.
Message: Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.
Line 5: Incorrect syntax near ')'.
Command:
SELECT
COUNT([notes]),
COUNT(DISTINCT [notes]),
COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
j.job_id = jt.job_id)),
COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j
on j.job_id = jt.job_id)),
COUNT('task_status'),
COUNT(DISTINCT 'task_status')
FROM [dbo].[job_task] t
--
Of course, the error is correct - what I need to know is how to either
bypass the column uniqueness test FOR THIS ONE column or another method to
create the link in the Data Source View.
Thank you,
ScottI would use SSIS to get this set up. I have not done such a thing, but since
SSIS can provide report datasources that's the route I would look at.
"ScottB" <ScottB@.discussions.microsoft.com> wrote in message
news:95A89A5B-F36B-4056-96BF-A748B613CA25@.microsoft.com...
>I have had to add Calculated columns to some tables in my Data Source View
>in
> order to correctly link some tables but this kills the model generation.
> For example, although the job table has a CompanyID column, the job_task
> table does not - but I need to be able to lookup a status in CompanyReason
> which is indexed on CompanyID and PauseReason (this column is in
> job_task).
> So I added 'theCompanyID' as a Calculated columns to job_task with the
> following code:
> SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
> j.job_id = jt.job_id
>
> Makes sense? But when I create a model with it - this is the error
> message:
> --
> An error occurred while executing a command.
> Message: Cannot perform an aggregate function on an expression containing
> an
> aggregate or a subquery.
> Line 5: Incorrect syntax near ')'.
> Command:
> SELECT
> COUNT([notes]),
> COUNT(DISTINCT [notes]),
> COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
> j.job_id = jt.job_id)),
> COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job
> j
> on j.job_id = jt.job_id)),
> COUNT('task_status'),
> COUNT(DISTINCT 'task_status')
> FROM [dbo].[job_task] t
> --
> Of course, the error is correct - what I need to know is how to either
> bypass the column uniqueness test FOR THIS ONE column or another method to
> create the link in the Data Source View.
> Thank you,
> Scott|||Hi,
I am new to SSIS. I have been searching for some examples to
use Expression Builder in SSIS to use derived columns
transformation...I basically want to add a new column to my data source
on the run and add this column as the 2nd column before loading into
destination. How can I achieve this? Plz help. Is there a link where I
can get examples online'
Model for Report Builder
I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?
I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?
|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||I'm not aware of any existing issues like this. Here's a few things you could double check:
- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false
- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany
- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.
Model for Report Builder
I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?
I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?
|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||I'm not aware of any existing issues like this. Here's a few things you could double check:
- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false
- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany
- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.
sqlModel for Report Builder
I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?
I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?
|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||I'm not aware of any existing issues like this. Here's a few things you could double check:
- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false
- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany
- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.
Model Designer - ODBC Data Source
I want to create a Model against an ODBC data source.
This is not possible in the June CTP.
Will this facility be available in a future release?
Thanks,
JoeI've raised this as a call with Microsoft through our Partner Program.
Will post the result here when I get an answer.|||As promised here is Microsoft's reply:
Please find the answers to your queries below:
Query:
=======
You want to be able to create an Ad-hoc Report Model using the Model Designer in Visual Studio 2005.
You want to be able to use an ODBC data source for this model - currently the only option is to use the “SQL Client Data Provider”.
So you need to know whether this will be addressed in the final release of SQL Server 2005 (or any CTPs of SQL Server 2005 before then).
Solution:
==========
As of now only SQL and Analysis Services are directly supported as a data source for Report Builder.
However you can connect to the ODBC data source using SQL or Analysis Services. We have the following two ways to get this working:
Option I:
-
UDM (Unified Dimensional Model) is essentially a way that SQL Analysis Services 2005 can combine multiple sources of data into one model. Let’s say your “Sales” information is one an Oracle data store, your “HR” info is in SQL, and your “Inventory” is in DB2 – You would build a UDM which includes all three of these data sources, and then you could report against the UDM as if ALL of the information lived inside Analysis Services.
So, by using a UDM to point to the ODBC data source, you enable Report Builder to get at the data too, since Report Builder CAN report against Analysis Services.
Option II:
--
You could try this but the performance expected is not very good with this option:
1. create linked servers in order to reference the ODBC (or other) data sources
2. create a layer of views in a SQL Server database accessing the other database through the linked server mechanism. You could use just simple views to expose the contents of the underlying tables with 'select *'. For example:
create view MyODBCTableView as select * from LinkedServerName..SchemaOwner.TableX
3. build SQL Server views on top of these views as desired
4. build the Report Model against these SQL Views
5. use Report Builder to query the OBDC data source through this 'proxy'
The following link is useful for more information and resources on SQL Server 2005:
http://www.microsoft.com/uk/partner/sol_and_products/servers/sql/
This is a reply from MSAS newsgroup :
AS2005 is not going to support ODBC data source. However, you can try to
create a data source in SSIS project. Then, add the data source into new AS
project. You should be able to create DSV, cube and dimension, etc.
However, since this feature is not supported. Believe that this area has not
been tested enough and this may break engine or not working probably. It is
"AS IS" if you really want to use it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Ken Kwok
SQL Server Analysis Services
Model definitions
I've inherited a database from a vendor. This database has primary keys defined but not any foreign keys to other tables. The vendor establishes all their relationships in their code.
I've created a simple model off of the base tables, but can only report on one table at a time which makes perfect sense. What is the best way to create a more usable model?
Create views with foreign keys defined and then use the views as my source for the dsv? or can I define relationships within the model itself? I have not been able to figure out how to add a foreign key in the model Can this be done?
1. Load your model into Model Designer.
2. Edit DSV - manually add foreign keys your tables
3. Regen the model - roles should automatically appear.
model db should I make it read-only
What kind of troubles would I get into if I made the
model database read-only? By saying that this database
is only used for retrieving information and is never
gathering or modifying information with update, insert,
or delete operations, making it read-only will eliminate
a lot of overhead and increase its performance is this a
true statement?
Thanks for your input.The proof of the pudding is in the eating. Why not just try it out? After I
set the model database to read only, I get the following error message when
I try to create another database:
Server: Msg 3906, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database 'myTest' because the database is
read-only.
The CREATE DATABASE process is allocating 0.63 MB on disk 'myTest'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'myTest_log'.
And the new database is not created.
Also, even if there is no problem with creting a new database, since the
model database is so tiny and is not used often at all, performance isn't
really an issue.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:22d501c3a92d$5b1ee640$a601280a@.phx.gbl...
> What kind of troubles would I get into if I made the
> model database read-only? By saying that this database
> is only used for retrieving information and is never
> gathering or modifying information with update, insert,
> or delete operations, making it read-only will eliminate
> a lot of overhead and increase its performance is this a
> true statement?
> Thanks for your input.|||Rachan,
The model database is only used by SQL Server as basis to
create new user databases.
It is actually not used for anything else.
Let's say you are going to build 10 different databases
that will have same security, sizing, etc., you can
configure the model database once and all of your
databases will be created with the same configuration.
This will save you lots of time.
Generally, this database is not updated or no one really
uses it. So I do not think you are going to gain any
performance increase by making it read only.
HTH
DeeJay
Please Reply to NewsGroup Only.
>--Original Message--
>What kind of troubles would I get into if I made the
>model database read-only? By saying that this database
>is only used for retrieving information and is never
>gathering or modifying information with update, insert,
>or delete operations, making it read-only will eliminate
>a lot of overhead and increase its performance is this a
>true statement?
>Thanks for your input.
>.
>|||DeeJay,
Thanks for you input. The reason that I want to make the
database read-only is because the database is located on
client PC and they nerver going to use it. I just don't
know what kind of overhead the server is making when it
deal with database if any at all. Can you explain what
kind of overhead the server use with dealing with
database? I could not find any info on the web regarding
this.
Thanks,
Rachan
>--Original Message--
>Rachan,
>The model database is only used by SQL Server as basis
to
>create new user databases.
>It is actually not used for anything else.
>Let's say you are going to build 10 different databases
>that will have same security, sizing, etc., you can
>configure the model database once and all of your
>databases will be created with the same configuration.
>This will save you lots of time.
>Generally, this database is not updated or no one really
>uses it. So I do not think you are going to gain any
>performance increase by making it read only.
>HTH
>DeeJay
>Please Reply to NewsGroup Only.
>>--Original Message--
>>What kind of troubles would I get into if I made the
>>model database read-only? By saying that this database
>>is only used for retrieving information and is never
>>gathering or modifying information with update, insert,
>>or delete operations, making it read-only will
eliminate
>>a lot of overhead and increase its performance is this
a
>>true statement?
>>Thanks for your input.
>>.
>.
>|||Linchi,
Thanks for your input. The model database that we use
here is ship to client PC. Since they will not be using
it (create new database..etc) I through that I might be
able to save some overhead of the server maintain of the
database. Do you know what kind of overhead the server
use when dealing with database?
Thanks,
Rachan
>--Original Message--
>The proof of the pudding is in the eating. Why not just
try it out? After I
>set the model database to read only, I get the following
error message when
>I try to create another database:
>Server: Msg 3906, Level 16, State 1, Line 1
>Could not run BEGIN TRANSACTION in database 'myTest'
because the database is
>read-only.
>The CREATE DATABASE process is allocating 0.63 MB on
disk 'myTest'.
>The CREATE DATABASE process is allocating 0.49 MB on
disk 'myTest_log'.
>And the new database is not created.
>Also, even if there is no problem with creting a new
database, since the
>model database is so tiny and is not used often at all,
performance isn't
>really an issue.
>--
>Linchi Shea
>linchi_shea@.NOSPAMml.com
>
>"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
>news:22d501c3a92d$5b1ee640$a601280a@.phx.gbl...
>> What kind of troubles would I get into if I made the
>> model database read-only? By saying that this database
>> is only used for retrieving information and is never
>> gathering or modifying information with update, insert,
>> or delete operations, making it read-only will
eliminate
>> a lot of overhead and increase its performance is this
a
>> true statement?
>> Thanks for your input.
>
>.
>
model database read-only? By saying that this database
is only used for retrieving information and is never
gathering or modifying information with update, insert,
or delete operations, making it read-only will eliminate
a lot of overhead and increase its performance is this a
true statement?
Thanks for your input.The proof of the pudding is in the eating. Why not just try it out? After I
set the model database to read only, I get the following error message when
I try to create another database:
Server: Msg 3906, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database 'myTest' because the database is
read-only.
The CREATE DATABASE process is allocating 0.63 MB on disk 'myTest'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'myTest_log'.
And the new database is not created.
Also, even if there is no problem with creting a new database, since the
model database is so tiny and is not used often at all, performance isn't
really an issue.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
news:22d501c3a92d$5b1ee640$a601280a@.phx.gbl...
> What kind of troubles would I get into if I made the
> model database read-only? By saying that this database
> is only used for retrieving information and is never
> gathering or modifying information with update, insert,
> or delete operations, making it read-only will eliminate
> a lot of overhead and increase its performance is this a
> true statement?
> Thanks for your input.|||Rachan,
The model database is only used by SQL Server as basis to
create new user databases.
It is actually not used for anything else.
Let's say you are going to build 10 different databases
that will have same security, sizing, etc., you can
configure the model database once and all of your
databases will be created with the same configuration.
This will save you lots of time.
Generally, this database is not updated or no one really
uses it. So I do not think you are going to gain any
performance increase by making it read only.
HTH
DeeJay
Please Reply to NewsGroup Only.
>--Original Message--
>What kind of troubles would I get into if I made the
>model database read-only? By saying that this database
>is only used for retrieving information and is never
>gathering or modifying information with update, insert,
>or delete operations, making it read-only will eliminate
>a lot of overhead and increase its performance is this a
>true statement?
>Thanks for your input.
>.
>|||DeeJay,
Thanks for you input. The reason that I want to make the
database read-only is because the database is located on
client PC and they nerver going to use it. I just don't
know what kind of overhead the server is making when it
deal with database if any at all. Can you explain what
kind of overhead the server use with dealing with
database? I could not find any info on the web regarding
this.
Thanks,
Rachan
>--Original Message--
>Rachan,
>The model database is only used by SQL Server as basis
to
>create new user databases.
>It is actually not used for anything else.
>Let's say you are going to build 10 different databases
>that will have same security, sizing, etc., you can
>configure the model database once and all of your
>databases will be created with the same configuration.
>This will save you lots of time.
>Generally, this database is not updated or no one really
>uses it. So I do not think you are going to gain any
>performance increase by making it read only.
>HTH
>DeeJay
>Please Reply to NewsGroup Only.
>>--Original Message--
>>What kind of troubles would I get into if I made the
>>model database read-only? By saying that this database
>>is only used for retrieving information and is never
>>gathering or modifying information with update, insert,
>>or delete operations, making it read-only will
eliminate
>>a lot of overhead and increase its performance is this
a
>>true statement?
>>Thanks for your input.
>>.
>.
>|||Linchi,
Thanks for your input. The model database that we use
here is ship to client PC. Since they will not be using
it (create new database..etc) I through that I might be
able to save some overhead of the server maintain of the
database. Do you know what kind of overhead the server
use when dealing with database?
Thanks,
Rachan
>--Original Message--
>The proof of the pudding is in the eating. Why not just
try it out? After I
>set the model database to read only, I get the following
error message when
>I try to create another database:
>Server: Msg 3906, Level 16, State 1, Line 1
>Could not run BEGIN TRANSACTION in database 'myTest'
because the database is
>read-only.
>The CREATE DATABASE process is allocating 0.63 MB on
disk 'myTest'.
>The CREATE DATABASE process is allocating 0.49 MB on
disk 'myTest_log'.
>And the new database is not created.
>Also, even if there is no problem with creting a new
database, since the
>model database is so tiny and is not used often at all,
performance isn't
>really an issue.
>--
>Linchi Shea
>linchi_shea@.NOSPAMml.com
>
>"Rachan Terrell" <web24by7@.hotmail.com> wrote in message
>news:22d501c3a92d$5b1ee640$a601280a@.phx.gbl...
>> What kind of troubles would I get into if I made the
>> model database read-only? By saying that this database
>> is only used for retrieving information and is never
>> gathering or modifying information with update, insert,
>> or delete operations, making it read-only will
eliminate
>> a lot of overhead and increase its performance is this
a
>> true statement?
>> Thanks for your input.
>
>.
>
Model DB problem..
attempted to move my Model and msdb database files to a different drive on the server. I did the following:
* Restarted SQL Server 2K with the -T3608 startup parameter
* successfully detached msdb and model DBs
* moved the msdbdata.mdf, msdblog.ldf, model.mdf and modellog.ldf
files to their new homes
* successfully reattached model and msdb
When I take away the -T3808 startup parameter and restart SQL Server, I do not see the model database in the db list in enterprise manager eventhough my 'attach' seemed to work. Now I get the following error when i try and query against the Model database using query analyzer:
"Could not locate entry in sysdatabases for database 'model'. No entry found with that name. Make sure that the name is entered correctly"
Also, when I look in the sysaltfiles table in Master, I see no reference whatsoever to the Model DB
What did I do wrong?attempted to move my Model and msdb database files to a different drive on the server. I did the following:
* Restarted SQL Server 2K with the -T3608 startup parameter
* successfully detached msdb and model DBs
* moved the msdbdata.mdf, msdblog.ldf, model.mdf and modellog.ldf
files to their new homes
* successfully reattached model and msdb
What did I do wrong?
Ouch! I HATE it when that happens. Here's a link, you've probably already read it (here (http://support.microsoft.com/default.aspx?scid=kb;en-us;224071)).
I would (in more or less this order):
1. Look carefully at the SQL Error log
2. Restart with -T3608 and attempt to re-attach model
3. Restore model from a backup (you did have a backup, right?)
I seem to recall from a distant memory that when you moved msdb and model, there was a particular sequence (one HAD to be done before the other). [Edit: here it is, smack in the middle of the link above].
Note If you are using this procedure together with moving the msdb
and model databases, the order of reattachment must be model first
and then msdb. If msdb is reattached first, it must be detached and
not reattached until after model has been attached.
I can't tell for certain from the steps you outline above which you did first.
I have used this procedure to move model, master, msdb and tempdb on several different occasions with no trouble at all (sorry, that doesn't help you, but I did want to verify that the steps outlined above DO work). As I recall, I usually make it a point to move msdb and model in separate steps (restarting SQL Server in between moving these two dbs).
Best of luck,
hmscottsql
* Restarted SQL Server 2K with the -T3608 startup parameter
* successfully detached msdb and model DBs
* moved the msdbdata.mdf, msdblog.ldf, model.mdf and modellog.ldf
files to their new homes
* successfully reattached model and msdb
When I take away the -T3808 startup parameter and restart SQL Server, I do not see the model database in the db list in enterprise manager eventhough my 'attach' seemed to work. Now I get the following error when i try and query against the Model database using query analyzer:
"Could not locate entry in sysdatabases for database 'model'. No entry found with that name. Make sure that the name is entered correctly"
Also, when I look in the sysaltfiles table in Master, I see no reference whatsoever to the Model DB
What did I do wrong?attempted to move my Model and msdb database files to a different drive on the server. I did the following:
* Restarted SQL Server 2K with the -T3608 startup parameter
* successfully detached msdb and model DBs
* moved the msdbdata.mdf, msdblog.ldf, model.mdf and modellog.ldf
files to their new homes
* successfully reattached model and msdb
What did I do wrong?
Ouch! I HATE it when that happens. Here's a link, you've probably already read it (here (http://support.microsoft.com/default.aspx?scid=kb;en-us;224071)).
I would (in more or less this order):
1. Look carefully at the SQL Error log
2. Restart with -T3608 and attempt to re-attach model
3. Restore model from a backup (you did have a backup, right?)
I seem to recall from a distant memory that when you moved msdb and model, there was a particular sequence (one HAD to be done before the other). [Edit: here it is, smack in the middle of the link above].
Note If you are using this procedure together with moving the msdb
and model databases, the order of reattachment must be model first
and then msdb. If msdb is reattached first, it must be detached and
not reattached until after model has been attached.
I can't tell for certain from the steps you outline above which you did first.
I have used this procedure to move model, master, msdb and tempdb on several different occasions with no trouble at all (sorry, that doesn't help you, but I did want to verify that the steps outlined above DO work). As I recall, I usually make it a point to move msdb and model in separate steps (restarting SQL Server in between moving these two dbs).
Best of luck,
hmscottsql
model db owner
I have a request to add a user to the model db dbo group.
Can you provide pros/cons to this request
thanks again
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>I have a request to add a user to the model db dbo group.
> Can you provide pros/cons to this request
> thanks again
Pro:
That user will be created in any new databases you create.
Con:
That user will be created in any new databases you create.
|||What they're asking is for someone to have dbo access to every database ever
created in that server. I suppose if the user is the CEO that might be
justified but that's a lot of privileged access to give to anyone.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>I have a request to add a user to the model db dbo group.
> Can you provide pros/cons to this request
> thanks again
|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ACF4C5D7-F192-4B68-B05D-B1BF5D951204@.microsoft.com...
> What they're asking is for someone to have dbo access to every database
> ever created in that server. I suppose if the user is the CEO that might
> be justified but that's a lot of privileged access to give to anyone.
>
Oops, missed the dbo part. That's probably not a good idea.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Can you provide pros/cons to this request
thanks again
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>I have a request to add a user to the model db dbo group.
> Can you provide pros/cons to this request
> thanks again
Pro:
That user will be created in any new databases you create.
Con:
That user will be created in any new databases you create.
|||What they're asking is for someone to have dbo access to every database ever
created in that server. I suppose if the user is the CEO that might be
justified but that's a lot of privileged access to give to anyone.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>I have a request to add a user to the model db dbo group.
> Can you provide pros/cons to this request
> thanks again
|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ACF4C5D7-F192-4B68-B05D-B1BF5D951204@.microsoft.com...
> What they're asking is for someone to have dbo access to every database
> ever created in that server. I suppose if the user is the CEO that might
> be justified but that's a lot of privileged access to give to anyone.
>
Oops, missed the dbo part. That's probably not a good idea.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
model db owner
I have a request to add a user to the model db dbo group.
Can you provide pros/cons to this request
thanks again"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>I have a request to add a user to the model db dbo group.
> Can you provide pros/cons to this request
> thanks again
Pro:
That user will be created in any new databases you create.
Con:
That user will be created in any new databases you create.|||What they're asking is for someone to have dbo access to every database ever
created in that server. I suppose if the user is the CEO that might be
justified but that's a lot of privileged access to give to anyone.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>I have a request to add a user to the model db dbo group.
> Can you provide pros/cons to this request
> thanks again|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ACF4C5D7-F192-4B68-B05D-B1BF5D951204@.microsoft.com...
> What they're asking is for someone to have dbo access to every database
> ever created in that server. I suppose if the user is the CEO that might
> be justified but that's a lot of privileged access to give to anyone.
>
Oops, missed the dbo part. That's probably not a good idea.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Can you provide pros/cons to this request
thanks again"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>I have a request to add a user to the model db dbo group.
> Can you provide pros/cons to this request
> thanks again
Pro:
That user will be created in any new databases you create.
Con:
That user will be created in any new databases you create.|||What they're asking is for someone to have dbo access to every database ever
created in that server. I suppose if the user is the CEO that might be
justified but that's a lot of privileged access to give to anyone.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>I have a request to add a user to the model db dbo group.
> Can you provide pros/cons to this request
> thanks again|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ACF4C5D7-F192-4B68-B05D-B1BF5D951204@.microsoft.com...
> What they're asking is for someone to have dbo access to every database
> ever created in that server. I suppose if the user is the CEO that might
> be justified but that's a lot of privileged access to give to anyone.
>
Oops, missed the dbo part. That's probably not a good idea.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:DA0A3CE6-26BD-41C9-91D3-1257A1C8B513@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Subscribe to:
Posts (Atom)