data secruity for Report Builder Models

Does anyone know how to implement data secruity based on user id (forms
authenication) for the report builder models.
Basically, I have created an report model. And i want certain people to have
access to certain data. The users are organized by the office where they
work. Is there a way to do, this without creating an different model for each
group of users?read this:
http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:A0BE0C59-10DA-4C44-9120-CCC13E5EF84E@.microsoft.com...
> Does anyone know how to implement data secruity based on user id (forms
> authenication) for the report builder models.
> Basically, I have created an report model. And i want certain people to
> have
> access to certain data. The users are organized by the office where they
> work. Is there a way to do, this without creating an different model for
> each
> group of users?|||When I try to add a new expression or filter, i get the following error
message
Access to the path 'C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\config' is denied. (Microsoft Visual Studio)
"Jeje" wrote:
> read this:
> http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:A0BE0C59-10DA-4C44-9120-CCC13E5EF84E@.microsoft.com...
> > Does anyone know how to implement data secruity based on user id (forms
> > authenication) for the report builder models.
> >
> > Basically, I have created an report model. And i want certain people to
> > have
> > access to certain data. The users are organized by the office where they
> > work. Is there a way to do, this without creating an different model for
> > each
> > group of users?
>|||so, i wanted to do something like this as a filter or expression. But i don't
know how to get this into the expression. Anything you can do would help
SELECT *
FROM tblBureauCode INNER JOIN
tblPerson ON tblBureauCode.Bureau_CodeID =tblPerson.Bureau_CodeID INNER JOIN
tblDataAccess ON tblBureauCode.Bureau_Code =tblDataAccess.Bureaus
WHERE (tblDataAccess.Login = user!userid)
"Jeje" wrote:
> read this:
> http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:A0BE0C59-10DA-4C44-9120-CCC13E5EF84E@.microsoft.com...
> > Does anyone know how to implement data secruity based on user id (forms
> > authenication) for the report builder models.
> >
> > Basically, I have created an report model. And i want certain people to
> > have
> > access to certain data. The users are organized by the office where they
> > work. Is there a way to do, this without creating an different model for
> > each
> > group of users?
>

Data Modelling Question with Cascade Deletes

Hi, I have this scenario that keeps coming up on different models and
I'm yet to find a solution.
In this model I have Customers, Customer Recognitions, Sectors and
Divisions.
Sectors and Divisions are related and reference data.
Customers become recognized in sectors, and then within divisions in
that sector.
The problems I have are these:
1) I need to be able to delete data at all levels & can create all but
1 of the cascade rules - because of circular references. In this case
when I delete a Sector I would want to Cascade and delete all related
CustomerSectors
2) Its feels to me like an unnecessary relationships exists in the
database because of the circular design - but I cant decide how and
exactly to remove a relationship.
Can anyone advise?.
Thanks.
CREATE TABLE [dbo].[CustomerSectorDivision] (
[CSD_ID] [smallint] NOT NULL ,
[D_ID] [smallint] NOT NULL ,
[CS_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customer] (
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerRecognition] (
[CR_ID] [smallint] NOT NULL ,
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerSector] (
[CS_ID] [smallint] NOT NULL ,
[CR_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Division] (
[D_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sector] (
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [PK_CustomerSectorDivision] PRIMARY KEY CLUSTERED
(
[CSD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[C_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [PK_CustomerRecognition] PRIMARY KEY CLUSTERED
(
[CR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [PK_CustomerSector] PRIMARY KEY CLUSTERED
(
[CS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [PK_Division] PRIMARY KEY CLUSTERED
(
[D_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sector] ADD
CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
(
[S_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [FK_CustomerSectorDivision_CustomerSecto
r] FOREIGN KEY
(
[CS_ID]
) REFERENCES [dbo].[CustomerSector] (
[CS_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSectorDivision_Division] FOREIGN KEY
(
[D_ID]
) REFERENCES [dbo].[Division] (
[D_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [FK_CustomerRecognition_Customer] FOREIGN KEY
(
[C_ID]
) REFERENCES [dbo].[Customer] (
[C_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [FK_CustomerSector_CustomerRecognition] FOREIGN KEY
(
[CR_ID]
) REFERENCES [dbo].[CustomerRecognition] (
[CR_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSector_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
)
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [FK_Division_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
) ON DELETE CASCADE
GOIf customers are in sectors and sectors are in divisions, then the
CustomerSectorDivision table breaks normal form.
Please elaborate on the actual (real-life, not current database model)
relationships between the entities.
ML
http://milambda.blogspot.com/|||Remove the FK from CustomerSector to Sector.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1138707797.465718.285110@.g47g2000cwa.googlegroups.com...
Hi, I have this scenario that keeps coming up on different models and
I'm yet to find a solution.
In this model I have Customers, Customer Recognitions, Sectors and
Divisions.
Sectors and Divisions are related and reference data.
Customers become recognized in sectors, and then within divisions in
that sector.
The problems I have are these:
1) I need to be able to delete data at all levels & can create all but
1 of the cascade rules - because of circular references. In this case
when I delete a Sector I would want to Cascade and delete all related
CustomerSectors
2) Its feels to me like an unnecessary relationships exists in the
database because of the circular design - but I cant decide how and
exactly to remove a relationship.
Can anyone advise?.
Thanks.
CREATE TABLE [dbo].[CustomerSectorDivision] (
[CSD_ID] [smallint] NOT NULL ,
[D_ID] [smallint] NOT NULL ,
[CS_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customer] (
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerRecognition] (
[CR_ID] [smallint] NOT NULL ,
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerSector] (
[CS_ID] [smallint] NOT NULL ,
[CR_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Division] (
[D_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sector] (
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [PK_CustomerSectorDivision] PRIMARY KEY CLUSTERED
(
[CSD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[C_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [PK_CustomerRecognition] PRIMARY KEY CLUSTERED
(
[CR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [PK_CustomerSector] PRIMARY KEY CLUSTERED
(
[CS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [PK_Division] PRIMARY KEY CLUSTERED
(
[D_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sector] ADD
CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
(
[S_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [FK_CustomerSectorDivision_CustomerSecto
r] FOREIGN KEY
(
[CS_ID]
) REFERENCES [dbo].[CustomerSector] (
[CS_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSectorDivision_Division] FOREIGN KEY
(
[D_ID]
) REFERENCES [dbo].[Division] (
[D_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [FK_CustomerRecognition_Customer] FOREIGN KEY
(
[C_ID]
) REFERENCES [dbo].[Customer] (
[C_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [FK_CustomerSector_CustomerRecognition] FOREIGN KEY
(
[CR_ID]
) REFERENCES [dbo].[CustomerRecognition] (
[CR_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSector_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
)
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [FK_Division_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
) ON DELETE CASCADE
GO|||Because there are two paths to CustomerSectorDivision, this cascading delete
scenario isn't supported. I prefer to avoid all cascading referential
actions because they cede control over the order in which locks are
obtained, which can make it more difficult to minimize deadlocks.
As far as I can tell, there aren't any unnecessary relationships in this
model. Cascading referential actions in SQL Server require that the
relationships form a tree, but networks can and do occur in perfectly valid
models.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1138707797.465718.285110@.g47g2000cwa.googlegroups.com...
> Hi, I have this scenario that keeps coming up on different models and
> I'm yet to find a solution.
> In this model I have Customers, Customer Recognitions, Sectors and
> Divisions.
> Sectors and Divisions are related and reference data.
> Customers become recognized in sectors, and then within divisions in
> that sector.
> The problems I have are these:
> 1) I need to be able to delete data at all levels & can create all but
> 1 of the cascade rules - because of circular references. In this case
> when I delete a Sector I would want to Cascade and delete all related
> CustomerSectors
> 2) Its feels to me like an unnecessary relationships exists in the
> database because of the circular design - but I cant decide how and
> exactly to remove a relationship.
> Can anyone advise?.
> Thanks.
> CREATE TABLE [dbo].[CustomerSectorDivision] (
> [CSD_ID] [smallint] NOT NULL ,
> [D_ID] [smallint] NOT NULL ,
> [CS_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Customer] (
> [C_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CustomerRecognition] (
> [CR_ID] [smallint] NOT NULL ,
> [C_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CustomerSector] (
> [CS_ID] [smallint] NOT NULL ,
> [CR_ID] [smallint] NOT NULL ,
> [S_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Division] (
> [D_ID] [smallint] NOT NULL ,
> [S_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Sector] (
> [S_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerSectorDivision] ADD
> CONSTRAINT [PK_CustomerSectorDivision] PRIMARY KEY CLUSTERED
> (
> [CSD_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Customer] ADD
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [C_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerRecognition] ADD
> CONSTRAINT [PK_CustomerRecognition] PRIMARY KEY CLUSTERED
> (
> [CR_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerSector] ADD
> CONSTRAINT [PK_CustomerSector] PRIMARY KEY CLUSTERED
> (
> [CS_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Division] ADD
> CONSTRAINT [PK_Division] PRIMARY KEY CLUSTERED
> (
> [D_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Sector] ADD
> CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
> (
> [S_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerSectorDivision] ADD
> CONSTRAINT [FK_CustomerSectorDivision_CustomerSecto
r] FOREIGN KEY
> (
> [CS_ID]
> ) REFERENCES [dbo].[CustomerSector] (
> [CS_ID]
> ) ON DELETE CASCADE ,
> CONSTRAINT [FK_CustomerSectorDivision_Division] FOREIGN KEY
> (
> [D_ID]
> ) REFERENCES [dbo].[Division] (
> [D_ID]
> ) ON DELETE CASCADE
> GO
> ALTER TABLE [dbo].[CustomerRecognition] ADD
> CONSTRAINT [FK_CustomerRecognition_Customer] FOREIGN KEY
> (
> [C_ID]
> ) REFERENCES [dbo].[Customer] (
> [C_ID]
> ) ON DELETE CASCADE
> GO
> ALTER TABLE [dbo].[CustomerSector] ADD
> CONSTRAINT [FK_CustomerSector_CustomerRecognition] FOREIGN KEY
> (
> [CR_ID]
> ) REFERENCES [dbo].[CustomerRecognition] (
> [CR_ID]
> ) ON DELETE CASCADE ,
> CONSTRAINT [FK_CustomerSector_Sector] FOREIGN KEY
> (
> [S_ID]
> ) REFERENCES [dbo].[Sector] (
> [S_ID]
> )
> GO
> ALTER TABLE [dbo].[Division] ADD
> CONSTRAINT [FK_Division_Sector] FOREIGN KEY
> (
> [S_ID]
> ) REFERENCES [dbo].[Sector] (
> [S_ID]
> ) ON DELETE CASCADE
> GO
>|||It appears to me that divisions are in sectors. I see nothing wrong with
the model.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:C08CF6D6-993B-4C78-9AD0-C6EC4CE9BCBA@.microsoft.com...
> If customers are in sectors and sectors are in divisions, then the
> CustomerSectorDivision table breaks normal form.
> Please elaborate on the actual (real-life, not current database model)
> relationships between the entities.
>
> ML
> --
> http://milambda.blogspot.com/|||I don't see what the CustomerSectorDivision is there for. The relationship i
s
already defined thorugh the CustomerSector and Division tables, isn't it?
ML
http://milambda.blogspot.com/|||Sectors are areas of industry, Divisions are just sub-sectors within
e.g Finance has several divisions Basic Accounts, Taxation, Assets &
Liabilities. Customers s recognition first in a sector and then for
divisions within the sector.
A customer cannot have recognition for a division without the
corresponsing sector.|||Not necessarily. Since there's a 1:0..n relationship between Sector and
Division, it follows that there could also be a 1:0..n relationship between
CustomerSector and Division. CustomerSectorDivision provides a place to
store information that is related only to the juxtaposition of a
CustomerSector and a Division.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:DEB5E66A-CE4D-43A3-AA63-7A2CB1A15CF1@.microsoft.com...
>I don't see what the CustomerSectorDivision is there for. The relationship
>is
> already defined thorugh the CustomerSector and Division tables, isn't it?
>
> ML
> --
> http://milambda.blogspot.com/|||I see. Now I understand. However, I'd try to further isolate individual
relationships.
Entities:
Sectors
Divisions
Customers
Relationships:
Sector <-- Customer (actual relationship, but I wouldn't implement it
directly in the physical model)
Sector <-- Division
Sector <-- Division <-- Customer (actual relationship, but I wouldn't
implement it directly)
Tables:
Sectors (SectorId : ...)
Divisions (DivisionId : ...)
Customers (CustomerId : ...)
SectorDivisions (SectorDivisionId : SectorId : DivisionId)
DivisionCustomers (SectorDivisionId : CustomerId)
Views:
SectorDivisionCustomers (SectorId : DivisionId : CustomerId)
SectorCustomers (SectorId : CustomerId)
I believe this model supports cascading foreign keys better. But one
question remains - can a Customer belong to a Sector without belonging to a
Division?
ML
http://milambda.blogspot.com/|||The relationship between a Customer and a Sector is implied by the foreign
key constraint between CustomerSector and CustomerRecognition and the
foreign key constraint between CustomerRecognition and Customer.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:002BDCD5-0F8E-4BBE-A4AC-1F996289717F@.microsoft.com...
> That is if the direct relationship between a Customer and a Sector is
> valid.
>
> ML
> --
> http://milambda.blogspot.com/

Data mining prediction

Hi again....

I have tree models trained and now how do I perform predictions? I've read that i can use ADO in my vb language so could anyone give me some code lines about prediction using ADO and Vb language? and how to view the prediction in my vb application?

Thank's a lot

Once you have a trained model, you can do perform predictions by querying the model using the DMX language (which is similar to SQL). Take a look at the source code for the DM Sample which uses ADO/VB to send your queries to AS 2000 and displays the result in a hierarchical grid control: http://www.sqlserverdatamining.com/DMCommunity/Downloads/Links_LinkRedirector.aspx?id=85

data mining and SS05

I've seen a couple of article on creating and scoring some mining models -- very cool stuff. Is the only way I can do this from an OLAP cube? Can I create, train, and score a model just from relational data?

Thanks!

You can create models directly from cubes using the wizard. Scoring models can be done using an MDX input to a prediction join statement e.g.

SELECT Predict(MyColumn) FROM MyModel
PREDICTION JOIN
<MDX Statement>
ON <bindings>

|||

The data mining functionality in SQL Server 2005 Analysis Services is not restricted to source data in OLAP cubes - you can create, train and query (score) mining models based directly on data in any relational data source. You can find more information, including tutorials at http://www.sqlserverdatamining.com.