Compare database on developer and enduser server

Hello Group,
Sorry my english is very littel.
In my company I have two servers: first developer server (DevSvr)- for
developers and second server (MainSvr)to use for other emplooy - for user of
application.
One of databases was changed on DevSvr (add new tables, sp, indexes etc.).
How compare databeses on DevSvr and MainSvr (db structure) and create script
to alter database on MainSvr.
Thx PawelRtry www.red-gate.com
Lutz|||Before you invest several hundred dollars in a schema comparison tool,
consider the option of using Enterprise Manager to generate scripts for each
DB to seperate folders and using a GPL tool like WinMerge to perform the
file comparisons. You can configure to list only scripts and individual
lines that are different.
http://groups.google.com/group/micr...f9cfe0c46abfa76
Also, if you are checking your scripts into Visual Source Safe, there is a
feature to perform file comparisons between projects.
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:ecxsuFjqFHA.3800@.TK2MSFTNGP11.phx.gbl...
> Hello Group,
> Sorry my english is very littel.
> In my company I have two servers: first developer server (DevSvr)- for
> developers and second server (MainSvr)to use for other emplooy - for user
> of application.
> One of databases was changed on DevSvr (add new tables, sp, indexes etc.).
> How compare databeses on DevSvr and MainSvr (db structure) and create
> script to alter database on MainSvr.
> Thx PawelR
>|||You can use my free tools SQLDBDiff, the first time you lunch the tool it is
in French you can easly switch to English version by clicking :
Taches/Options/Language
Here is the link (My site will be in English soon)
http://www.sqldbtools.com/Downloads.aspx?ProductId=1
I'm waiting for your comments and feedback
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:ecxsuFjqFHA.3800@.TK2MSFTNGP11.phx.gbl...
> Hello Group,
> Sorry my english is very littel.
> In my company I have two servers: first developer server (DevSvr)- for
> developers and second server (MainSvr)to use for other emplooy - for user
> of application.
> One of databases was changed on DevSvr (add new tables, sp, indexes etc.).
> How compare databeses on DevSvr and MainSvr (db structure) and create
> script to alter database on MainSvr.
> Thx PawelR
>

compare 2 strings

i have a Company table . one of the column is EmailAdres. now i want to
compare a string(a emaiadres) agains this column. the problem is that the
string is different every time.
e.g.
search Parameter1.: "test user"<testuser@.yahoo.com>
search Parameter2.: <testuser@.yahoo.com>
search Parameter3.: (testuser@.yahoo.com)
column EmailAdres from DB :testuser@.yahoo.com
is there a way to do this?Try:
WHERE EmailAdres LIKE '%testuser@.yahoo.com%'
Note that this will require a table/index scan.
Hope this helps.
Dan Guzman
SQL Server MVP
"henk" <henk@.discussions.microsoft.com> wrote in message
news:29D53D0A-7A7C-49F2-AF12-7A69C2AE735C@.microsoft.com...
>i have a Company table . one of the column is EmailAdres. now i want to
> compare a string(a emaiadres) agains this column. the problem is that the
> string is different every time.
> e.g.
> search Parameter1.: "test user"<testuser@.yahoo.com>
> search Parameter2.: <testuser@.yahoo.com>
> search Parameter3.: (testuser@.yahoo.com)
> column EmailAdres from DB :testuser@.yahoo.com
> is there a way to do this?|||henk
Have a look at LIKE function in the BOL
"henk" <henk@.discussions.microsoft.com> wrote in message
news:29D53D0A-7A7C-49F2-AF12-7A69C2AE735C@.microsoft.com...
>i have a Company table . one of the column is EmailAdres. now i want to
> compare a string(a emaiadres) agains this column. the problem is that the
> string is different every time.
> e.g.
> search Parameter1.: "test user"<testuser@.yahoo.com>
> search Parameter2.: <testuser@.yahoo.com>
> search Parameter3.: (testuser@.yahoo.com)
> column EmailAdres from DB :testuser@.yahoo.com
> is there a way to do this?|||"henk" <henk@.discussions.microsoft.com> wrote in message
news:29D53D0A-7A7C-49F2-AF12-7A69C2AE735C@.microsoft.com...
>i have a Company table . one of the column is EmailAdres. now i want to
> compare a string(a emaiadres) agains this column. the problem is that the
> string is different every time.
> e.g.
> search Parameter1.: "test user"<testuser@.yahoo.com>
> search Parameter2.: <testuser@.yahoo.com>
> search Parameter3.: (testuser@.yahoo.com)
> column EmailAdres from DB :testuser@.yahoo.com
> is there a way to do this?
You use parameter for example @.email and compare parameter values with
values on column.
Later input values into parameter.|||In addition to what Dan suggested:
The most efficient solution would be a data clean-up. In an efficient
production environment this is done before the data is inserted.
Once you identify all patterns it should be pretty simple to remove unwanted
characters - perhaps even in a computed column if the source data must remai
n
unchanged.
ML
http://milambda.blogspot.com/|||try this.
Select * from tbl1
where @.searchparam like '%' + email_col + '%'
hope this helps.
"henk" wrote:

> i have a Company table . one of the column is EmailAdres. now i want to
> compare a string(a emaiadres) agains this column. the problem is that the
> string is different every time.
> e.g.
> search Parameter1.: "test user"<testuser@.yahoo.com>
> search Parameter2.: <testuser@.yahoo.com>
> search Parameter3.: (testuser@.yahoo.com)
> column EmailAdres from DB :testuser@.yahoo.com
> is there a way to do this?|||thanks to you all for ur fats reply.
i just have a question for dan.
what you exactly mean with "Note that this will require a table/index scan."
lets say my table have about 20,000 records. and i have about 5 email column
s.
does it slow down the process? is it better if i clean up the email like
sugessted in front end and than pass the string to DB.
i still have to use like even if i clean up the search parameter.
what are you suggesting?
thanks in advance.
"Dan Guzman" wrote:

> Try:
> WHERE EmailAdres LIKE '%testuser@.yahoo.com%'
> Note that this will require a table/index scan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "henk" <henk@.discussions.microsoft.com> wrote in message
> news:29D53D0A-7A7C-49F2-AF12-7A69C2AE735C@.microsoft.com...
>
>|||> i still have to use like even if i clean up the search parameter.
So you need to provide 'partial' email search functionality rather than an
exact match? The bottom line is that a LIKE expression with a leading
wildcard will require a scan. 20,000 rows isn't really that big nowadays so
if this is a query that is not run often, I'd just take the performance hit
and be done with it.
You can add a covering index if this query is run often. A scan of a
relatively narrow non-clustered index is less expensive than a table scan
when a relatively small number of rows satisfy the criteria. If you always
search all 5 email columns, a single index with all 5 email columns would be
best.
Without the leading wildcard SQL Server can use indexes to efficiently. An
equality search is the most efficient. You could then normalize your data
without a performance hit. For example:
SELECT *
FROM Company
WHERE EXISTS
(
SELECT *
FROM CompanyEmailAddresses
WHERE CompanyEmailAddresses.CompanyID = Company.CompanyID AND
CompanyEmailAddresses.EmailAdres = @.EmailAdres
)
Hope this helps.
Dan Guzman
SQL Server MVP
"henk" <henk@.discussions.microsoft.com> wrote in message
news:BB8C9D4B-10F4-481F-93AD-B740D77B08C6@.microsoft.com...
> thanks to you all for ur fats reply.
> i just have a question for dan.
> what you exactly mean with "Note that this will require a table/index
> scan."
> lets say my table have about 20,000 records. and i have about 5 email
> columns.
> does it slow down the process? is it better if i clean up the email like
> sugessted in front end and than pass the string to DB.
> i still have to use like even if i clean up the search parameter.
> what are you suggesting?
> thanks in advance.
> "Dan Guzman" wrote:
>

Compare 2 addresses

Hi,
I hae a DB with a table holding companies, each company has the fields :
name, address, city, state, country and zip.
I have several clients connected to my server application (ASP.Net), and
they sometimes add new companies to the companies table. When they add the
company they don't always take the time to split the different fields of the
company's address and sends it (address, city, state, country and zip) in a
single row. The pattern of the row isn't always the same, sometimes only the
city is added to the address, sometimes only the zip, sometimes none.
I'm trying to make the match between the data the clients send and the data
I already have in my DB in order not to store the same company twice in the
DB.
I've tried using Full-Text Search, I've tried several logical algorithms,
but I can't make an automatic solution to this problem.
Please add any idea you may have on how I can make this comparission as
automatic as possible.
ThanksYou're best bet would be to scrub the data when it's entered, i.e.--change
the app to prevent the concatenated addresses. The only alternative I can
think of would be to split the incoming data into tokens like a compiler, an
d
then compare the token pattern against a syntax graph to categorize the data
.
It might be easier to reverse the graph, in other words, work backward: a
zip code usually has a pattern of either ##### or #####-####, unless you're
in Canada. The state or province usually preceeds the zip code, the city
preceeds the state or province, etc.
"YN" wrote:

> Hi,
> I hae a DB with a table holding companies, each company has the fields :
> name, address, city, state, country and zip.
> I have several clients connected to my server application (ASP.Net), and
> they sometimes add new companies to the companies table. When they add the
> company they don't always take the time to split the different fields of t
he
> company's address and sends it (address, city, state, country and zip) in
a
> single row. The pattern of the row isn't always the same, sometimes only t
he
> city is added to the address, sometimes only the zip, sometimes none.
> I'm trying to make the match between the data the clients send and the dat
a
> I already have in my DB in order not to store the same company twice in th
e
> DB.
> I've tried using Full-Text Search, I've tried several logical algorithms,
> but I can't make an automatic solution to this problem.
> Please add any idea you may have on how I can make this comparission as
> automatic as possible.
> Thanks|||Thanks for your reply Brian,
I can't force the users to split the address to separate fields. I usually
don't have a problem with identifying the country and state, however this is
not the case when dealing cities and addresses.
My current algorithm is (for a1 (=address1), a2 (=address2), c1 (=city1), c2
(=city2)
1. Remove all non relevant chars (non literal or numeric chars) from all.
Remove "non relevant words" like "street","road","st." etc from addresses.
2. Check if c1 is null. If so check if c2 exists in a1 and if so remove it
from the address and set c1 = c2
3. The other way around (c2 and a1)
4. tokenize the results by spaces and try to find matches between them
(since sometimes the structure is streen name followed by building number an
d
sometimes its the other waty around)
Do you perhaps have a better suggestion ?
"Brian Selzer" wrote:
> You're best bet would be to scrub the data when it's entered, i.e.--change
> the app to prevent the concatenated addresses. The only alternative I can
> think of would be to split the incoming data into tokens like a compiler,
and
> then compare the token pattern against a syntax graph to categorize the da
ta.
> It might be easier to reverse the graph, in other words, work backward: a
> zip code usually has a pattern of either ##### or #####-####, unless you'r
e
> in Canada. The state or province usually preceeds the zip code, the city
> preceeds the state or province, etc.
> "YN" wrote:
>|||Thanks for your reply Brian,
I can't force the users to split the address to separate fields. I usually
don't have a problem with identifying the country and state, however this is
not the case when dealing cities and addresses.
My current algorithm is (for a1 (=address1), a2 (=address2), c1 (=city1), c2
(=city2)
1. Remove all non relevant chars (non literal or numeric chars) from all.
Remove "non relevant words" like "street","road","st." etc from addresses.
2. Check if c1 is null. If so check if c2 exists in a1 and if so remove it
from the address and set c1 = c2
3. The other way around (c2 and a1)
4. tokenize the results by spaces and try to find matches between them
(since sometimes the structure is streen name followed by building number an
d
sometimes its the other waty around)
Do you perhaps have a better suggestion ?
"Brian Selzer" wrote:
> You're best bet would be to scrub the data when it's entered, i.e.--change
> the app to prevent the concatenated addresses. The only alternative I can
> think of would be to split the incoming data into tokens like a compiler,
and
> then compare the token pattern against a syntax graph to categorize the da
ta.
> It might be easier to reverse the graph, in other words, work backward: a
> zip code usually has a pattern of either ##### or #####-####, unless you'r
e
> in Canada. The state or province usually preceeds the zip code, the city
> preceeds the state or province, etc.
> "YN" wrote:
>|||Look up the tools that Mellisa Data sells for cleaning up addresses.|||YN
If i understood you may want to write a trigger or better stored procedure
to catch then INSERT a new company and check all data
declare @.address VARCHAR(100)
IF EXISTS (SELECT * FROM Table WHERE address =@.address AND company=@.company
AND .....)
PRINT 'You are trying to insert a duplicate'
"YN" <YN@.discussions.microsoft.com> wrote in message
news:F8C33439-18BB-4183-98DD-BDB2F244BF0B@.microsoft.com...
> Hi,
> I hae a DB with a table holding companies, each company has the fields :
> name, address, city, state, country and zip.
> I have several clients connected to my server application (ASP.Net), and
> they sometimes add new companies to the companies table. When they add the
> company they don't always take the time to split the different fields of
the
> company's address and sends it (address, city, state, country and zip) in
a
> single row. The pattern of the row isn't always the same, sometimes only
the
> city is added to the address, sometimes only the zip, sometimes none.
> I'm trying to make the match between the data the clients send and the
data
> I already have in my DB in order not to store the same company twice in
the
> DB.
> I've tried using Full-Text Search, I've tried several logical algorithms,
> but I can't make an automatic solution to this problem.
> Please add any idea you may have on how I can make this comparission as
> automatic as possible.
> Thanks

Company with Address Question

I have a database that allows a client to have multiple addresses, could be a home, business, vacation, etc... I'm trying to deside if I should put the Company Name and Title fileds in the tblClientAddresses table or keep it with the tblClient table. We do mailings to clients and if the mail is going to a home address the company and title should be excluded. If I put them in the tblClients table I really have no way of knowing if we are mailing to a home or a company. If i put the company and title with the address it makes sence that this would solve the issue.

Looking for any thoughts or suggestions?

Thank you,Standard normalization would say if there are multiple addresses per client, put it in a separate table. The only time I would recommend against this is for performance reasons, but I'm guessing you don't have any performance problems yet.|||The Company Name and Title fields belong in tblClient.

You could do something like this to make them blank if the address is a Home address:

SELECT
 CASE
 WHEN A.type = 'Home' THEN ''
 ELSE C.Name
 END AS Name,
 CASE
 WHEN A.type = 'Home' THEN ''
 ELSE C.Title
 END AS Title,
 A.Address1,
 A.Address2,
 A.City,
 A.State,
 A.ZIP
FROM
 tblClient C
INNER JOIN
 tblClientAddress A ON C.ClientID = A.ClientID

As the OP said, though, sometimes you give up correct normalization for performance -- especially if the data is static and the tables are extremely large.

Terri

Company Logo on the Parameters Bar

Hello all,
would like to know if its possible to have a company logo on the
parameters bar that appears above the report ?
Thx
BofoUnless you can figure out how to get this in the viewer CSS, not on the
built-in toolbar. You would need to build your own prompt area.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Win2003InstallIssues" <bofobofo@.yahoo.com> wrote in message
news:1107010875.797510.255770@.c13g2000cwb.googlegroups.com...
> Hello all,
> would like to know if its possible to have a company logo on the
> parameters bar that appears above the report ?
> Thx
> Bofo
>|||And how can somebody create its own prompt area?
I'm (and some others too) search since many hours for a clue on this
subject. The only things to find are that this should be done to solve
several problems. But not a piece of a hint where the information for this
can be found. - Realy incredible!!!
"Brian Welcker [MS]" wrote:
> Unless you can figure out how to get this in the viewer CSS, not on the
> built-in toolbar. You would need to build your own prompt area.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Win2003InstallIssues" <bofobofo@.yahoo.com> wrote in message
> news:1107010875.797510.255770@.c13g2000cwb.googlegroups.com...
> > Hello all,
> >
> > would like to know if its possible to have a company logo on the
> > parameters bar that appears above the report ?
> >
> > Thx
> > Bofo
> >
>
>|||So how would I be able to add company logo in the breadcrumb area?
"Brian Welcker [MS]" wrote:
> Unless you can figure out how to get this in the viewer CSS, not on the
> built-in toolbar. You would need to build your own prompt area.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Win2003InstallIssues" <bofobofo@.yahoo.com> wrote in message
> news:1107010875.797510.255770@.c13g2000cwb.googlegroups.com...
> > Hello all,
> >
> > would like to know if its possible to have a company logo on the
> > parameters bar that appears above the report ?
> >
> > Thx
> > Bofo
> >
>
>|||Dev Main wrote:
> And how can somebody create its own prompt area?
> I'm (and some others too) search since many hours for a clue on this
> subject. The only things to find are that this should be done to solve
> several problems. But not a piece of a hint where the information for
> this can be found. - Realy incredible!!!
Use a real name, go to www.codeproject.com and search for reporting
services.
I needed 3 hours to develop my own prompt area!
Maybe you have to switch back and create your reports with excel :-/
regards
Frank

Company logo in Report Manager - Top Frame

We are trying to work out if it is possible to access the source code for the
Report Manager interface, so that we can configure the design.
For example, we would like to put the company logo in as a watermark in the
space at the top section of Report Manager (behind 'SQL Server Reporting
Services Home')
We have been able to set the background colour etc using the style sheet,
but would like to know if it is possible to have greater control over the
look and feel at the base level.Yes you can. The images for instance can be changed... I have forgotten the
details, but they are discussed in The Hitchikers Guide to Reporting
Services... The issue is that you must place your images in a RS directory,
and change some RS files. This puts you in a position where you might have
to re-do this work at each Service Pack or upgrade...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:17084FC0-0C20-41B4-A7EC-9CA49D3A0F99@.microsoft.com...
> We are trying to work out if it is possible to access the source code for
> the
> Report Manager interface, so that we can configure the design.
> For example, we would like to put the company logo in as a watermark in
> the
> space at the top section of Report Manager (behind 'SQL Server Reporting
> Services Home')
> We have been able to set the background colour etc using the style sheet,
> but would like to know if it is possible to have greater control over the
> look and feel at the base level.|||Thanks Wayne, much appreciated.
Darren
"Wayne Snyder" wrote:
> Yes you can. The images for instance can be changed... I have forgotten the
> details, but they are discussed in The Hitchikers Guide to Reporting
> Services... The issue is that you must place your images in a RS directory,
> and change some RS files. This puts you in a position where you might have
> to re-do this work at each Service Pack or upgrade...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:17084FC0-0C20-41B4-A7EC-9CA49D3A0F99@.microsoft.com...
> > We are trying to work out if it is possible to access the source code for
> > the
> > Report Manager interface, so that we can configure the design.
> >
> > For example, we would like to put the company logo in as a watermark in
> > the
> > space at the top section of Report Manager (behind 'SQL Server Reporting
> > Services Home')
> >
> > We have been able to set the background colour etc using the style sheet,
> > but would like to know if it is possible to have greater control over the
> > look and feel at the base level.
>
>|||This doesnt work in 2005 Report Manager.
The Report Manager in 2005 is little differern from 2000. Im not able to do
the customization as explined in Hitchikers Guide. any help'
have you tried any customization for Report Manager in 2005.
any feedback appricated..
Thanks
Bava
"Wayne Snyder" wrote:
> Yes you can. The images for instance can be changed... I have forgotten the
> details, but they are discussed in The Hitchikers Guide to Reporting
> Services... The issue is that you must place your images in a RS directory,
> and change some RS files. This puts you in a position where you might have
> to re-do this work at each Service Pack or upgrade...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:17084FC0-0C20-41B4-A7EC-9CA49D3A0F99@.microsoft.com...
> > We are trying to work out if it is possible to access the source code for
> > the
> > Report Manager interface, so that we can configure the design.
> >
> > For example, we would like to put the company logo in as a watermark in
> > the
> > space at the top section of Report Manager (behind 'SQL Server Reporting
> > Services Home')
> >
> > We have been able to set the background colour etc using the style sheet,
> > but would like to know if it is possible to have greater control over the
> > look and feel at the base level.
>
>

Company Logo in Header of Report Builder

Can you put a company logo at the top of a report when using Report Builder? The documentation indicates the "image" can be place in the report layout.

Is there any way around this limitation?

Thank you.Hi,
You can add images to your reports. But I do not know this is what you are asking?
Just add an image to your Report Project. You will see it in the Reports folder where the rdl files (reports) are.
Then just drag and drop it on the layout of the report to where you want it to place.
Eralper

|||

I can add an image to the design area in Report Builder but I am unable to place the image at the top of the report like you can in Report Designer.

Thanks

|||This is correct - static images cannot be added in Report Builder. You would need to import the report into Report Designer. We are working on adding this support in an upcoming service pack.

Company footer message on all reports

I've looked around, but can't find this anywhere. Does anyone know of a way to add a footer to all reports (including new ones being created via report builder)? So that when users create new reports, the global footer is there.

Maybe create a "Company Footer" as a subreport?

Granted you will still have to insert the subreport into each of your report footers, but any changes needing to be made to the footer will only need to be done once.

|||That is an option, but we want all users to be forced to use this.
|||

The best bet would be to create a report template and then have all report developers use the template.

To create a report template:

create a report with all the common elements, store the .rdl file in the Report Designer template directory (in a default installation this would be C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject|||This is an excellent suggestion, and one we will probably use for developers. However, the main area we want to catch is the Report Builder reports. Here we can't possibly train all the users to include a subreport, or something similar. What we need is a way to alter the predefined Report Builder template, to add a company footer. Any ideas?

Company Dimension

We deal with multiple vendors who provide us information via text/xml files. Vendor A may provide financial data, vendor b provides litigation data, vendor c provides ratings data. Our current structure has databases for each vendor with its own company table which basically makes all this data disconnected. Of course each vendor has its own proprietary company id to make records unique.

All of the data is based on companies so the grain of data would be at a company level. I would like to be able to link this information together by creating a dimensional model that has a single company table (DimCompany) and has facts populated based on the type of data we receive. Would this be the right sequence of events?

1. My initial load (historical) would have to look at all these data sources and create one company record in my DimCompany table. This table would then link to all other fact tables to provide a single view of company info. I would imagine this would have to be a fuzzy lookup since one company will be in all sources.

2. On subsequent loads (incremental) I would probably have to do a lookup of companies in the dimension via the proprietary code and add if the company wasn't there.

Any advice on tackling this issue would be greatly appreciated especially if SSIS was used in the process.

Hi jrp210,

Based on my interpretation of your schema, I would design DimCompany as you describe. One row per company, unique IDs, and you may wish to add surrogate keys - they will make your data mart / warehouse easier to scale.

I would also add a DimInformationType for the different types of data you receive.

Another option is to snowflake the InformationType table off of DimCompany. This is common when the referenced lookup table is relatively small and doesn't change often.

There are performance implications but in smaller data warehouses (~1 - 2G) you will probably never notice. And if you do, an index will likely clear up any performance issue.

I don't follow the need for a fuzzy lookup. These are expensive in SSIS and should only be used when necessary.

You are correct: incremental loading does require a lookup (or a merge join) to detect new records.

There's lots of good information online about how to do this. I suggest picking up one of the Kimball books for help in designing data warehouses - I particularly like the Data Warehoue Toolkit which is updated to include information on using SSIS for ETL.

Hope this helps,

Andy

|||

Here are my 2 cents:

Having a conformed 'Company' dimension is the way to 'join' all fact rows. Not sure about using a Fuzzy lookup as it may give you non-expected results; so evaluate yourself the margin of error your company can tolerate. In general you may want to asses the level of cleanness of your data, so you can anticipate the results

About how to organize the fact data; I would say that depends on the way the data is going to be used during analysis and reporting, and on the nature of the data. You may want to spend some time with the end users to get that feedback; draw a data model and review it with them again; this may take a few iterations.

Andy suggestion about Kimball’s book is also a good idea.

Good luck with that

|||

Thanks for the response. I did purchase the book you referenced. It has been very helpful but doesn't delve into the issue of trying to create one dimension table from multiple sources with different primary keys.

I will definitely be creating surrogate keys because more data sources will be introduced over the long haul.

I don't follow the DimInformationType table. Is this more of a helper table to map the surrogate company key to the proprietary key used by the vendor? For example:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

When its time to access vendor B's file, most of the companies will be in the DimCompany table. If not, follow the same procedure as above. If they are in the DimCompany table then I will have to add a row to the vendor b helper table with the surrogate key/vendor b key.

It all stems from the fact that each vendor has its own proprietary (different) key for the same company. I don't know how I would get around not using fuzzy logic or some sort of text mapping. Text mapping could be dangerous as well since the names may be slightly different.

|||

There are 2 other books from Kimball’s group:

The Data Warehouse ETL tool kit

The Microsoft Data Warehouse Toolkit

The first one cover how to conform dimension (coming from different sources).; the second one covers Kimball's warehouse methodology using SQL Server 2005 tools.

|||

jrp210 wrote:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

Hi jrp210,

That's different from what I understood previously. Don't feel bad, this happens to me a lot.

Maybe your schema looks like this:

CompanySK (surrogate key) CompanyName (business key)
1 Microsoft

VendorSK (surrogate key) VendorName (business key)
1 Vendor A
2 Vendor B

VendorCompanySK VendorCompany_CompanySK VendorCompany_VendorSK VendorCompany_ID
1 1 1 123456
2 1 2 789101

This is a snowflake that allows you to utilize Company and Vendor separately in facts, and also use VendorCompany in facts. There are foreign key relationships between DimCompany and DimVendorCompany, and DimVendor and DimVendorCompany.

Hope this helps,

Andy

|||

Andy,

My goal would be to create one company record from multiple sources. I don't necessarily need a vendor or vendorcompany table but will probably need their proprietary company id as an attribute in my company dimension table so that one could link back using their key.

I would assume something like this:

CompanySK
VendorA CompanyId
VendorB CompanyId
VendorC CompanyId
Company Name

For the most part one row should contain an Id for vendor a,b, and c but there are times when that is not the case. Is that why the company name would be the business/natural key?

|||

The first thing you need to do if to define the grain of your dimension; it looks to me like the grain is one row for each company (even when that company exists in several vendor data sources); so if you have more than one 'version' of a company, like in your example of Microsoft company, you would need some kind of auxiliary table to keep that 1:many relationship between the many rows/company in the source and the 1 row per company n your dimension.

BTW, this has nothing to do with SSIS...but I hope it helps

|||

You are right about the SSIS - probably more geared toward dimensional modeling/DW. But, I am using SSIS to do that so this is where I originally posted to.

Yes, grain of the dimension is the company. The really isn't more than one version of the company. It is the same company coming from different source (vendor) systems. The vendor systems have different unique keys in which they tag a company. Because of this there isn't one natural key to use across all. If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

|||

jrp210 wrote:

If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

That is right. You could create a surrogate key in the dimension and then that auxiliary table will keep the relationship between source system keys(many for a company) and the Dimension surrogate key (one per company).

|||

At first it makes sense but to initially load the database how would you keep the company unique in the DimCompany table without a key that would link them together? Or better yet put, which comes first loading the auxillary table or Dimension table?

I was assuming the auxillary table would be loaded first:
VendorId
VendorCompanyCode
CompanyName
etc.

But then I would have to create a unique key that would be used in the DimCompany table. There would have to be another table that then creates this key (identity column) that would be used in the DimCompany table.

Company - Address

I need some help devising my tables.

I have a company table and a address table. I have created a linked table with two foreign keys from the company table and the address table respectively.

This set up allows me to apply more than one address per company which is fine. My problem is that I need a way to prevent a company from entering exactly the same address twice.

In the address table i have the following fields:

AddressKey - PK
CompanyKey - FK
Address 1 - 3
Town
County
Country
PostCode

In the company table I have the following fields.

CompanyKey - PK
FirstName
Secondname

And finally in the link table I have the following:

CompanyKey - FK
AddressKey - FK.

Now, if i entered the following into the address table Assuming that company id of 1 was already entered into the company table.

AddressKey - 1, CompanyKey = 1, Address1 = 11 Address2= Taylor

I need a way of preventing this from happening.

AddressKey - 2, CompanyKey = 1, Address1 = 11 Address2= Taylor

As can be seen the PK - FK values are unique and are correct for referential integrity, but the actual address is the same.

Any help we be mostly appreciated.

Cheers

Wayneyou do not need the linking table at all, because you apparently want to link each address to its company using the CompanyKey as a FK in the address table

the only time you need a linking table is when multiple companies share the same address

and even if this does occur, it probably does not occur all that frequently (compared to the bulk of your data)

therefore if two companies have the same address, put that address into the table twice

now, as to your question...

to prevent the same company from entering the same address twice, declare a unique constraint on ( CompanyKey, Address 1 - 3, Town, County, Country, PostCode )|||r937.

Thanks for the reply.

ok I understand why you would not use the linked table, but In my case the address table is in effect the site address of a particular company, and companies can have multiple site addresses.

If i don't use a linked table then this would not be possible.

I tried using an instead of trigger - would this not be a good idea in this case then?

Cheers

Wayne|||let's make sure we are talking about the same linked table

you can support the one-to-many model of a company having multiple addresses just with Company and Address table

the Address table CompanyKey is the FK that says which company this is an address for

the 3rd table, the link table, is not necessary

unless, like i said earlier, you wnt two different companies to "share" an address!

as far as preventing a compnay from entering the same address twice, no, you would not use a trigger for that, you would use a unique constraint for that|||r937.

Thanks for clearing that up for me. I will give it a stab once i've finished browsing ebay. Ok to give you a shout if I have any problems?

Wayne|||if you were thinking of contacting me personally through a private message or email, i would prefer that you post a followup in this thread

:cool:|||sorry that's what I meant!|||here would be a way to solve your issue by using a composite key(or Unique index)and an intersecting entity
your business rule dictates that one customer can have many addresses and that no duplicate addresses can be used

you could create a customer table

customerid PK
companyname
addressid

then create a address table

Addressid PK
StreetAddress
city
state etc..

now the issue exists that you cannot get 2 addresses out of this erd but you can by modifying the erd with an intersecting entity..

create a customeraddress table

Customerid PK
addressid PK
(any dependent data on a customer at a particular address goes in this table)
the composite key on the two columns generates uniqueness for each of them. (no one customer can have a duplicate address)

The join path would look like this
Customer <----> CustomerAddress <----->Address|||Ruprect, that's beautiful, but that's the many-to-many relationship

i have my doubts about whether that's required

i think only a one-to-many is required

and in any case, if you go right back to the original question, the situation was

AddressKey - 1, Address1 = 11 Address2= Taylor
AddressKey - 2, Address1 = 11 Address2= Taylor

and thus possible to give the "same" address to a company twice, because the "same" address is actually in the table twice! (this is a problem of surrogate keys, not one-to-many versus many-to-many)

your 3-table many-to-many does not prevent that duplication either

here's what i recommend:
create table Companies
( CompanyKey
, FirstName
, Secondname
, primary key (CompanyKey)
)
create table Addresses
( AddressKey
, CompanyKey
, Address1
, Address2
, Address3
, Town
, County
, Country
, PostCode
, primary key (AddressKey)
, foreign key (CompanyKey) references Companies (CompanyKey)
, unique (Address1, Address2, Address3, Town, County, Country, PostCode)|||Sunday Morning.

Just read your replies guys. Ruprect as r937 states, this was my original solution and does not prevent a duplicate address from existing, ok it does allow me to create a unique constrant based on the companykey and addresskey of the respective tables but this was not what I was after. - Thanks for the input though.

r937 - going to that last reply a stab.

Thanks for all your helps guys.

Wayne|||Thats worked fine.

Next question is how do I take that sever error message that is created (Server msg 2627) and produce a user friendly error message stating that they have violated key constraints.

Would I design a trigger for this?|||wait
now hold on justa cotton picken minute rabbit! ( channeling yosemite sam)

you solution limits the address process the actual # of cols (in this case 3) what if someone has 4 addresses are you going to alter the table?

my solution which is typically a many to many solution also eliminates the dupes in the customeraddresstable by creating a composite unique constraint or index on the customerid and addressid columns

here are the customers and address tables with some data
customer c Address a
c1 a
c2 b
c3 c
c4 d
c5 e

here is the customeraddress table

c1 a
c1 b
c1 c
c1 d
c1 e

if i try to add customer c1 with the address a again i will violate the constraint. no dupes and there is no limit to the possible addresses that one customer can have.

i wouldnt like to use it because of the extra joins so the previous solution might have a perf advantage. but my solutin does work.|||in the end guys i cam up with this design|||cool! good for you

Common Image in Reports

I need to display the company logo on 100 odd reports that run from different folders on the report manager. Since the company logo has changed before and we had to change every report, I want the image stored on a server (with SSL on) and want the reports to point to the server. The server is going to be different in development and production environments.

When I try to add the image using "Image Wizard" I get the error "Invalid image.". The wizard wouldn't let me use a function to build the path based on the environment and select the image from the correct server. It doesn't accept anything that doesn't start with "http:" when I select "web" as the image source.

Please Help!!!

Don't serve the image over SSL (which requires https: as the protocol). Why can't the image come from a non secure web address? I assume you are just serving these reports over an intranet?

|||Yes, the reports are rendered on the intranet. Even if I have the image come from a non secure web address, I'd still need to get the image from different web addresses in development and production. Reporting services doesn't let me use a web image, unless I specify a valid web address and to switch between development production I need to have some kind of variable in the address.|||

Hello,

Here's what you can do:

Create your report and add a string parameter (Environment) that you will set for development or production. Set this variable to Internal and give it 2 available values (D and P) with a default of D since this is where you will be testing. Add your image to your report via your web address for your development environment. Click on the image and in the properties window and change the Value property to an expression with something like this:

=Switch(Parameters!Environment.Value = "D", "http://www.DevServer.com/images/DevImage.jpg", Parameters!Environment.Value = "P", "http://www.ProdServer.com/images/ProdImage.jpg")

Now, when you deploy your report to production, go into the properties of your report in report manager and change the value of the Evironment parameter to P.

Jarret

|||Thanks Jarret, I'll try and let you know how it goes!!!

Committing a Stored Procedure

hi there,

I am connecting to a sql server express 2005 database which is located on another developers machine in the company. he has given me the username and password and i can read and write to this db using vs2005 no problems.

i can also added new tables and everything through the management studio, but when i add stored procedures, it does not seem to commit to the database, when i click save it asks for a local destination but i want it to save onto the sql server.

i can add stored procedures through vs 2005 no problems! this does not make sense?

Hi,

yes it sure does. You don′t have to save the procs you have to execute the DDL rather than saving the code. That should work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Can you please explain how to execute the DDL step by step, because I have pressed EXECUTE and it just runs the query and displays the results|||OK, due to he fact that I don′t have your code right here and you didn′t pasted it in your previous post I assume that you don′t have any DDL code right now.

DDL code (especially for procs) begin with

CREATE PROCEDURE (...)

If you don′t have such DDL code, you just have a query. What code are you executing ?|||

Hi Jens,

I sorted it out...I clicked Stored Procedures>New stored procedure and from the Query Menu>Specify values for template> and then fill that form out and then it commits it to the database NO PROBLEMS!

Combo box to exclude blank input

Hi
I have a combo box that displays a list of company names. Some entries do
not have a company name on the form so the textbox remains blank. Therefore
in the combo box there are alot of blanks in the list. How do I get the
combo box to just list out the company names without the blanks?
At the moment I have this:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts;
Thanks in advance
SeanThe following will exclude NULL or blank CompanyName rows:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts
WHERE
Contacts.CompanyName IS NOT NULL AND
Contacts.CompanyName <> '';
Hope this helps.
Dan Guzman
SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:9930ACDB-8E70-45B8-94B1-89A8DF57B60F@.microsoft.com...
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank.
> Therefore
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean|||Isn't there a Company table that, by definition, wouldn't have empty
company names?
If not, then one should be added - basic normalization.
Sean wrote:
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank. Therefor
e
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean