SQL Server Data Warehousing Forum

Rapidly changing dimension attrubutes..

Fri, 27 Nov 2009 11:07:13 Z

Hi Everyone,
The scenario I have is,..
I have 4 dimensions and one of them is Property..Property has some fixed attributes...which don't change for each transaction.....and
a list of more than 300 attributes, which are actually part of the fact table….(basically ..these values changes for each transaction level record in fact but belongs to the property dimension).
that lot contains more than a 100 date attributes,.. around a 50 monetary values, more than 10 comments…n some flags.

I don't want to connect the date attributes to time dimension..Bcoz,..there is no analysis...is required on them..
But,..my question is,.....How do I represent(or) store them????

I can not make a junk dimension for all these attributes..bcoz, theere are no fixed range of values...for these attributes...

I tried,. a method...grouping all these attributes under a single name...'Property Chracterstics' and a Propety ID put into a Dimension...and storing the value of 'Property characterstics' into a fact...
But,..Here..it tries to aggregate all the dates....n flags...across the dimensions!!..

not sure .. How I explained it….but,…please let me know if it needs more clarity..

Microsoft SQL Server Reporting instead of using SAP Business Warehouse

Mon, 09 Nov 2009 10:35:16 Z

Hy...

has anyone experiences in using Microsoft SQL Server instead of SAP Business Warhouse.

For the moment i did not find any Information if this is usefull.

OK, SQL Server is used as an Database for SAP BW and R/3 / ECC.
But for me this is not integrated enough. I wanted to connect SAP ECC directly to Microsoft SQL Server for BI (Business Intelligence) Reporting.

Thanks for your opinions and experiences, best regards

Robert

Business Intelligence SQL Server 2005 with Windows Vista Bussiness

Fri, 27 Nov 2009 02:07:32 Z

I´m developing an integration service project. In the Toolbox, the entire Dataflow items are disabilited. I´m using Windows Vista Bussiness, SQL Server BI 2005. I tried to upgrade to 2008 but I´ve had a lot of problems, even installing the 2005. During it, I read the advise ´This program has incompatibility problems´

Definitly must be another solution than a OS change.

General Question about transfering data to DWH using SSIS

Tue, 24 Nov 2009 08:43:15 Z

Hello All,

I have a situation in which I have to transfer some data on weekly basis and some data on monthly basis. I do receive data in two formats i.e. excel and text files. When client send us the data they append a data into the same file which they have already sent us last week/month.

So we do have the same data with some changes and the new data appended in the end of each file. But the business requirement is to keep all the dataand transfer all the data regardless of no change in the previous data. for example first week or month i have received the file containing the following data

column A     Column B    Column C    Column D
11                   AA              BB             £20
22                   cc               dd             £30
33                   ee               ff              £40

Now second  week or month, I have received the same file with the following data

column A     Column B    Column C    Column D
11                   AA              BB             £20
22                   cc               dd             £30
33                   ee               ff              £40
44                   gg               hh            £50

Now one row has been added and the first three rows are same. Some times they can remove all the three rows from above file and send a new row. Some times they can make the changes in the previous record and append the new rows as well. to maintain the history management want to tranfer everything from source file regardless it has already been transfered or not.

Now from reporting point of view what would be the best approach. I have added three rows in the destination tables

RowStartDate
RowEndDate
WeekNo

In RowStartDate i am adding the date when package has run (i.e. current date)
In RowEndDate i am adding one month / or 7 days to the rowstartdate to calculate the Rowenddate
In WeekNo, I am extracting the week no from RowStartDate.

Is it a correct approach? or there are some better ways to handle this requirement? Any suggestions please.

Thanks

Accessing ODBC Data From SQL Stored Procedure

Mon, 23 Nov 2009 15:47:55 Z

SQL 2005.

I am looking to develop a stored procedure to query external data via an ODBC (not OLEDB) connection (Merant Progress).

Ideally I had wanted to setup a linked database to do this but without an OLEDB driver for the external data source this does not appear possible.

So....

1. Is it possible to setup a linked database without using an OLEDB driver?

2. If not, then is there a manner to query/access data through an ODBC connection within SQL?

TIA Gary

Gary in Massena. WinForms Client Development. SQL Server Management and Development.

Days into Months

Mon, 23 Nov 2009 23:50:49 Z

Hi

Am not an expert SQL user and I am trying to format days into calendar months.

For example the fromdate is: 05/04/2009 and the endDate is 23/03/2010

I have a field for each calendar month in my table from Apr - Mar...for the above I want the code to calculate 25 days go into April 31 into May 30 into June.....etc....and....23 into March

i can do a datediff to get the total number of days but this dosent help with inserting the number of days for each month pertaining to that record

can anyone help?

Thanks

Table partitions with varchar column

Fri, 20 Nov 2009 06:57:56 Z

Hi All,

Can anyone tell me if it is possible to have table partitions built on a varchar key(the data is alphanumeric) without any range ?

Also, can a partitions be created without specifying the range on a numerical key ?

Suppose my key has the data like 1,2,3,4,5,6. I want to create separate partitions for keys 2 alone and 3 alone. Also,the rest of the keys 1,4,5,6 must be in another partition.

Regards,

Kailash

reporting data warehouse management server is currently unavailble

Thu, 19 Nov 2009 22:39:53 Z

help, what is that mean?

please advise,

thanks

SQL taking forever to execute

Thu, 12 Nov 2009 23:20:36 Z

I'm using AdventureWorksDW

I've written a query to extract average sales by product and sales territory country.
I didn't get any syntax errors, but it's taking forever to execute both in Management Studio and reporting Services.

Can anyone tell me if anything is wrong with the SQL?

MODERATOR EDIT - please use the insert code block button when pasting SQL Code. It is the last button on the right of the toolbar when composing a post.

SELECT DimProductCategory.EnglishProductCategoryName, 
       DimSalesTerritory.SalesTerritoryCountry, 
       SUM(FactInternetSales.SalesAmount)/COUNT(DISTINCT FactInternetSales.SalesOrderNumber) AS AverageSales

  FROM DimProduct 
  
 INNER JOIN DimProductSubcategory
    ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey 
    
 INNER JOIN FactInternetSales
    ON DimProduct.ProductKey = FactInternetSales.ProductKey 
    
 CROSS JOIN DimSalesTerritory

 CROSS JOIN DimTime

 CROSS JOIN DimProductCategory

 GROUP BY DimProductCategory.EnglishProductCategoryName, 
          DimSalesTerritory.SalesTerritoryCountry

Create global cube error

Thu, 12 Nov 2009 01:46:31 Z

I have a LastNonEmpty measure in the cube, when I run this query, I got error.

CREATE GLOBAL CUBE [Inventory Visibility] STORAGE 'C:\Users\v-polian\Documents\Hardware\work\2968377984\Inventory Visibility.cub' FROM [Inventory Visibility]

(

MEASURE [Inventory Visibility].[Optiant Reorder Point],

DIMENSION [Inventory Visibility].[Date].[APO Date Hierarchy]

(

LEVEL [(All)],

LEVEL [APO Year Nbr],

LEVEL [APO Month Name YY],

LEVEL [APO Week Start Date],

LEVEL [Calendar Date]

DIMENSION [Inventory Visibility].[Date].[Fiscal Date Hierarchy]

(

LEVEL [(All)],

LEVEL [Fiscal Year Nbr],

LEVEL [Fiscal Quarter Nbr YY],

LEVEL [Fiscal Month Name YY],

LEVEL [Fiscal Week End Date],

LEVEL [Calendar Date]

)

The error message is

Executing the query ...

Server: The operation has been cancelled.

Internal error: The operation terminated unsuccessfully.

Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

Errors in the OLAP storage engine: An error occurred while processing the 'Inventory Visibility' partition of the 'APO Planning Fact' measure group for the 'Inventory Visibility' cube from the Inventory Visibility database.

Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

Execution complete

Determine the partition number

Tue, 27 Oct 2009 10:40:58 Z

Hi All,

I have created 6 partitions based on a key. My keys are from 100,101.. 105 and a separate partition is created for each key.

Can anyone tell me how to determine which partition(partition number) is created for each of the keys.

Following is the result

partition_id object_id index_id partition_number hobt_id rows

72057594068860928 1474104292 1 1 72057594068860928 0

72057594069123072 1474104292 1 2 72057594069123072 10575

72057594069319680 1474104292 1 3 72057594069319680 0

72057594069385216 1474104292 1 4 72057594069385216 5290

72057594069450752 1474104292 1 5 72057594069450752 0

72057594070237184 1474104292 1 6 72057594070237184 0

Regards,

Kailash

What is the real difference of dimensional data warehouse and the normalized OLTP database?

Mon, 09 Nov 2009 09:04:34 Z

Hi everyone,

This problem haunts me for a long time.
I know what a dimensional data warehouse is and its schema.
However, I am puzzled why the dimensional data warehouse is query faster than the normalized database.
The dimensional data warehouse is split into many dimensional tables which should be joined by the JOIN operator...

So, is anyone can tell the real difference and give some basic example?

Really thanks!

Bitmap indexing in SQL Server 2008

Thu, 22 Oct 2009 12:44:54 Z

Hi All,

Is it possible to do bitmap indexing in SQL server 2008 ? Please let me know if there are any useful links.

Regards,
Kailash

Need an advise on designing BI infrastructure

Thu, 05 Nov 2009 17:41:49 Z

Our corporate currently has a medium sized (approx 500 GB) data warehouse in SQL SERVER 2005 and all SQL Server’s components database server, SSIS and SSAS are deployed to the single physical server. Considering the current performance and future growth management, we are in the process of re-architecting this BI infrastructure using SQL SERVER technologies.

Could you please share your ideas/experiences/expertise on selecting the following design choices for designing a BI infrastructure for an optimal performance:

1. System Configuration : Monolithic (run all SQL Servers components in single physical server) OR Distributed (run all SQL Servers components to different physical servers)

2. Data Storage: SAN or DAS

Thanks in Advance.

Recalculate Aggregates

Tue, 03 Nov 2009 04:42:26 Z

Hi All,

I have a situation where I am suppose to calculate the Standard deviation on a fact table.

The question is :

How is it best to calculate this for any subsequent incremental loads?
Does it mean that I have to recompute the standard deviation evert time after inserting the data in the fact?

Sridhar

Sridhar

sql server2000 LAN connectivity problem

Tue, 06 Oct 2009 13:44:32 Z

hi
when I try to connect sql server2000 by a client on LAN I got an error "Access denied or sql server does not exist" how can I solve this

Newbie: I came up with a couple of Dim and Fact tables... what's next? stuck

Mon, 12 Oct 2009 18:14:50 Z

All,

I'm new at Data Warehousing and been reading quite a bit about it, since I figured it's the way to go for my current project.
My data deals with hospitals and patients, so I want to be able to create reports on patient visits, admissions, discharges, etc by region, hospital, year, quarter.

I came up with a Dimension Table for Dates (where I have Year, Quarter, Month, Date, DayOfWeek, etc) and a second Dimension Table for Hospitals (where I have Region, State, City).

My Fact tables deal with the actual Patient Visits -- I have a table for Admissions, one for Discharges. Each row in these tables will include a DateId and a HospitalId that point to the above mentioned dim tables.

Now I want to work on reports for this data. My coworker will be using Reporting Services to write the reports and he wants me to write Stored Procedures that he'd run on the reports to get the data. I want to be able to report by Date (Year, Quarter, Month, Year to Date, Last Year, etc) and by Hospitals (in a region, city, state).

This is where I'm stuck... I'm not sure how to build the stored procs based on what parameters, and I can't find an example on the net that would help me. Obviously the parameters are the Date Range and the Hospital / City / State but I'm not sure how to tie it all together.

Any leads will be appreciated.
Thanks!

Creating Unique Key Identification

Fri, 23 Oct 2009 03:06:55 Z

Hi Guys,

I am working on a health service project were I need a data validation technic to identify patient and the history of that patient.

Scenario:

If a patient went to A hospital for heart treatment, hospital will give him a date for operation. During course of time if meet with accident and broke his leg he will be admitted to different hospital. Now issue is how I can validate data because as you can imagine there would possibility of same name, DOB, Add etc also keep in mind all this hospitals use different databases. I need to create a unique key to identify that patient and maintain the history of that patient.

How to solve this issue?

On top of my head we got 430 separate databases and patient can move anywhere from Hospital A to B OR C and than A.

Thanks and Regards,

D

Partition Functions

Fri, 23 Oct 2009 19:25:59 Z

I am creating scripts that will create partitioned tables. I would like to include an IF NOT EXISTS wrapper around my code.

How can I determine if a partition function already exsists, for example:

     IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'MyPartitionFnName'
          CREATE PARTITION FUNCTION MyPartitionFnName (datetime)....

I would also like to do the same thing for the SCHEME:

     IF NOT EXISTS (SELECT * FROM sys.scheme WHERE schemeName = 'MySchemeName')
          CREATE PARTITION SCHEME MySchemeName

Can this be done?

Thanks in advance.

Using Change Tracking For Datawarehousing

Fri, 02 Oct 2009 13:10:49 Z

Hi all,

We would like to use Change Tracking (Standard Edition available only !) to isolate records that have changed during day time to do nightly loads in our DWH (for example, we would like to get all customer changes done today (e.g. all insert/update/delete done on the customer table on the 1st of october 2009 from 8h00 AM till 18h00 PM).

Since there is no time information recorded with Change Tracking, how can we filter changes done each day on a given table ?
Can someone provide a clear example how to recover those changes ?

Can someone also explained how the columns SYS_CHANGE_VERSION and SYS_CHANGE_CREATION_VERSION are intended to work together ?
Indeed, I remarked some weird cases while testing Change Tracking with transaction:

1) Change Tracking recorded only 1 change for an insert, update and then delete of a record done in the same transaction: DELETE was recorded, INSERT and UPDATE weren't.
2) Change Tracking recorded only 1 change for an insert, update of a record done in the same transaction: INSERT was recorded, UPDATE wasn't.

With case 1) we would end with an error during our DWH load because we will try to delete a nonexistent record (since Change Tracking did not kept the INSERT information, we don't know the record has been created before being deleted).

Thanks.

Talend certification program

Thu, 22 Oct 2009 13:31:08 Z

Hello,

I have been using the Talend software for a few months now and am very happy. I have seen the page on the Talend certification and the exam on the website ( http://www.talend.com/partners/index.php ).

I wonder what it takes to take the exam, if you need to know well Talend. Also is the exam available for everyone and is it free or paying?

Maybe someone would have an idea? Thank you.

one size fits all architecture for star's etl

Mon, 14 Sep 2009 13:41:09 Z

is there a whitepaper somewhere that describes a flexible architecture for a star's etl? I have many goals I'd
like to accomplish with the architecture of my etl but dont necessarily see anybody discussing them on a macro level.
Here are some of my goals:

1) I may wish to update previously recorded facts with dimensions like "close date".

2) I may wish to delete instances of some dimensions that are no longer in use

3) I may be stuck to MOLAP for a while, but dont want to introduce obstacles to ROLAP and HOLAP.

4) Types I and II SCDs are something I want to account for

5) Because various measure groups share conformed dimensions, I'd like some kind of precedence between dim
recording process(es) and all fact processing that depends on those conformed dims

6) I'd like sufficient decoupling to target/run one object's ETL if necessary

7) I'm sometimes forced to deal with "deltas" (change between yesterday and today) in the absence of transactions
that record changes in the OLTP, maybe that issue will dictate how I architect things

8) Maybe I should consider generating all/some of the ETL automatically, perhaps based on metatdata about my realtionships

9) Wherever Incremental Processing Options make sense, I want to use them, but maybe with an option to do full
now and then

10) I want to append time dims periodically as I approach boundary conditions. For instance, I presently limit
    my time dim entries to no later than one year from today. So I'd want to insert new rows there maybe once a
    month. I do this to limit the perfomance issues I believe are present in "In Flight Transaction" scenarios
    when using MDX. Unfortunately I use the max time dim for accounts whose close date is really null, so I'd
    probably want to update "open" facts across the board with a new close date any time this boundary changes.

11) Reusability (for instance between new fact realtionships and old fact relationship expectations/changes)

12) Flexibility

13) Server and DB flexibility

14) maybe restartability

15) I think I'd like a layered/precedence approach that looks somethingh like this:

a) somehow record anything of a Type II nature in a sub package, perhaps one subpackage per Type II dim.
        b) record existing fact dim "expectations", perhaps one sub package per measure group, keep a key to be
           used for identifying "change" scenarios. Remember close dates.
        c) slide any "Today" tables to "Yesterday" for delta scenarios, maybe one sub package per scenario.
        d) record new facts and their dim expectations with new pks that start after highest existing one, maybe
           new sub package for every measure group. Remember close dates.
        e) use all previous dim related data to record and process dim changes, but wait till all facts are done
           running
        f) resolve fks on pre recorded and new facts and process. Dont use Identity on Fact pks as they've already
           been assigned in what looks like it will be a staging table

16) notifications, good and bad

17) anything that might support rerunning X number of days of ETL to bring us forward from a known problem
point to current on one or more objects

Does anybody have ideas/strategies/experiences/articles they'd like to share?

Need your advices for a SQL Server / SSIS/RS/AS 2005 migration to 2008 (installation methods)

Tue, 20 Oct 2009 16:28:11 Z

Hello

I have a big migration to do on a datawarehouse server, and your feedback is welcomed.

Here's the situation : I have a server (let's call it SVR1) hosting SQL Server 2005, and SSRS/IS/AS 2005. I need to upgrade everything to 2008.

on the current 2005 version are allready many reports, ssis packages, etc, and they need to stay available while we prepare the new 2008 reports/packages. That's why we plan to use a second temporary server (SVR2) to create the new 2008 environment, create the new SSIS packages, reports, etc, and once everything is ready, migrate everything.

The question is : what's the best to do : install a names instance of SQL Server 2008 on the SVR1 server, install SSIS/AS/RS 2008 on SVR2, pointing on the named instance on SVR1 ? or install the complet 2008 environment (including the SQL Server 2008 instance) on SVR2, and once everything is ready, migrate everything on SVR1 ?

Suggestions are welcomed.

Thank you

Olivier

Microsoft Fast Track Program...

Sun, 18 Oct 2009 19:50:35 Z

Was curious if anyone used the Fast Track program to help establish/validate your DW program. I've read some pretty useful information about it and was wondering if anyone had experience with the program.

Thanks
Roz

Difference in exits and inlist

Sat, 17 Oct 2009 10:33:55 Z

Hi,

Tell me the difference between using exists and using in list in queries. Which one will work faster?

Should I rebuild this fact table every day?

Tue, 06 Oct 2009 09:40:53 Z

We have a problem with one of our fact tables. One of the sources of data for this fact table allows retrospective data entry (e.g. this sale was completed 2 months ago) and modification of historical data (e.g. this code "ABC" last week was really an "XYZ"). The biggest problem is that our source database doesn't have any auditing so potentially anything can change any time without us knowing.

When we designed this fact table, we weren't aware users were making retrospective edits. We're simply just adding whatever activity has happened on the day to the fact table. It was a poor assumption on our part. As a result, we're missing a significant amount of fact data and some of it is incorrect.

So the question is, should I just rebuild the whole table every night at the end of the ETL? Or should I put in some change detection logic? For reference, this fact table is just over 4 million records and should grow roughly 20-30k records each day. Is this a "fully rebuildable" size?

Another thing is that there are other columns in this fact table that aren't subject to this problem. Should I just rebuild these as well or split it into seperate tables?

Any opinions or guidance much appreciated.

ETL Metadata Warehouse Design

Fri, 09 Oct 2009 17:07:54 Z

Design Question:

I am looking to design a warehouse that essentially captures the ETL metadata associated with a large scale conversion process. The purpose of this warehouse will be to provide an ongoing audit and reporting capability for ETL activity on the whole. By ETL activity I mean extract to cleansed processes including but not limited to crud activities as well as the application of complex business logic to various datapoints. On the whole I believe the most important points of interest are not so much the data within the tables but end to end record counts, and some data level aggregates like sum amounts for customer etc..

I am struggling with conceptualizing the dimensions by which I would base this on. As a starting point I believe breaking down all processes that manipulate the data, mock time elements,and the discrete data points are a good start. Then looking at deltas in record counts as per these.

Has anyone implemented something similar? I really haven't found much information out there regarding warehousing etl metadata and audit data. Any advice would be greatly appreciated

“Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.” -Terry Pratchett

Add Oracle 10g as Linked Server

Thu, 08 Oct 2009 07:19:35 Z

Hi,
I want to add an oracle server as linked server in SQL Server 2005.

I have followed all the steps given in link : http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

Install Oracle Database 10g Client Release 2
1. Install using the InstantClient option
Install Oracle 10g Release 2 ODAC (64-bit, 32-bit)
1. Select the Oracle Data Access Components option (not .NET!)
Edit TNSNAMES.ora
<install directory>Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:OracleProduct10.2.0ClientnetworkADMINTNSNAMES.ora)
There is a very specific format to the network configuration file. Here is an example to get you started (just copy & paste multiple entries if necessary):
```
DMDEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = FRIENDLYNAME)
    )
  )
```
There are a couple of things you need to change:
- HOST = SERVERNAME. The value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
- SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.
REBOOT!
Configure provider in SQL Server

OraOLEDB.Oracle Provider Menu Item

Databases→DBName→Server Objects→Linked Servers→Providers→OraOLEDB.Oracle→Properties→Enable “Allow inprocess”
Create a linked server to the Oracle Database
General
1. Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
2. Provider: Oracle Provider for OLE DB
3. Product Name: “Oracle” is fine here
4. Data Source: This should match the HOST you defined in TNSNAMES.ora
  
  Create a Linked Server
Security
1. Select Be made using this security context and supply the remote login and password

BUT I am still not able to query the tables of Oracle database.

This is the error I am getting.

Cannot create a an instance of OLEdb provider "OraOLEDB.oracle" for linked server "<<link server name>>"

Please help.......

Data Warehouse Design for Case/Nested Tables in Market Basket Analysis

Sun, 11 Oct 2009 22:03:18 Z

I'm doing a Market Basket Analysis project which involves data warehousing as well as data mining. To put it briefly, what I want to do is: -load data fetched from a transactional system into a data warehouse; -apply the association rules on the prepared data warehouse as a source.

The problem I have is reconciling the data warehouse fact/dimension table design with the case/nested approach used in data mining. I imagine that the nested table in data mining should contain all items bought in an individual transaction. I have read that usually the nested table corresponds with the fact table in the data warehouse. However, I cannot find a way to design the fact table in the DW to serve this purpose. I can have a variable amount of products in the basket, so I find it impossible to design the fact table without adding another table: items_in_transaction, as depicted here: http://img39.imageshack.us/img39/4426/itemskl.png -- but I haven't seen anything like that used with the star schema commonly employed for data warehouses, and I don't know how to use this design with the case/nested tables approach (if I select "items_in_transaction" as the nested table, I can only use "Transaction" as the case table, and thus, "Users" are inaccessible for the analysis). Does anyone have an idea for a good DW design taking multiple items in a basket into account, that is suited for the use with case/nested tables?

Thanks in advance for your help.
Katie

How use Transparent data encryption (TDE) in Sql Server 2008 Express Edition

Thu, 10 Sep 2009 10:15:25 Z

HI to All
I Have Application that use sql server 2008 express edition and i want to use TDE for Encrypt my Data and I know that this ficture exist in Enterprise Edition
//------
my purpose protect my DataBase to seeing.
i dont want to see my data and tables
i want to sales my application and i dont want any one to see my database
Thanks

Question / Clarification on - SQL Server 2008 Management Data Warehouse and Data Collector

Sun, 04 Oct 2009 22:34:27 Z

I read this article at http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=202

Few Questions:

1. My understanding is "Data Collector" Tool / Utility / Service resides on the machine whose data we need to collect and not on "Management Datawarehouse Server". Right? If that's the case do you think upgrade tool will get challenging over a period of time, within an enterprise?

2. I assume the information that needs to be captured, can be defined using an XML file.

Can you please share a sample XML file on your page or atleast over at http://sqlcms.codeplex.com/

3. Once the XML file is completely defined with an specific set of information to be captured, can the XML file be copied to the rest of the machine so that we have consistency in information that we are capturing across all machines.

4. The path ("C:\temp") in your sample image, can this path be defined as network path?

Uniqueness with summary fact table with too many dimensions

Mon, 28 Sep 2009 16:25:51 Z

Hi,
We have summary fact tables that have > 15 dimensions. We need to put unique indexes but SQL server only supports indexes upto 14 dimensions.
Is there a way around this problem?
Thanks,

Inserting GUID and time range lookup

Wed, 30 Sep 2009 19:47:36 Z

I have two questions, the first one is how can I insert a GUID into every row that is coming into the data path. Second question, I have an event that shoots numbers to me every millisecond i.e. 9/26/2009 5:54:12.240 , and I have set up a DB that has time listd in 10 minute intervals i.e. 9/26/2009 5:50:00.000, 9/26/2009 6:00:00.000 PM... I want to be able to do a range lookup on this time. How would I go about doing that?

local and global indexes for partitioned tables

Sun, 20 Sep 2009 12:58:43 Z

I think I understand the concept of align and unaligned indexes. But what about global and local. In partitioned tables, can I have a global index (that could help me with OLTP like workloads and unque key checking) as well as local indexes, that could help me with roll-in or roll-out scenarios. Perhaps not on the same table at the same time -- ?

designing a data warehouse

Wed, 16 Sep 2009 15:04:41 Z

hi,

im quite in dilemma how i would design a data warehouse.

1. our business deals with service repairs. usually in the oltp system, the header and line are from separate tables. can i still do the same for the fact tables?

2. another thing is, the user requires highly detailed level of the reports. what i intend to do is, have a fact table that keeps the summary level. and if they require the detailed level, they could just drillthrough to an ODS table. Since fact table contains foreign keys, so does this mean my ODS should also contain this keys? Is ODS supposedly a denormalized table or is much like a fact table with the foreign keys in it?? Because i am confused how i would be able to link ODS and fact table

3. user wants to see repairs that are not yet finished. meaning, their field values might still change. if i pump this inside the summarized data warehouse and the values in the OLTP will change tomorrow, how will i be able to handle this change?

thanks a lot

chempz

Blank value allowed for primary and foreign key?

Thu, 24 Sep 2009 15:41:34 Z

I cant seem to find anything definitive regarding this situation I just uncovered:

Our data warehouse is being built using SSIS 2008 target db is SQL Server 2008.

On our source AS400 system a value of blank ('') has meaning and value.
So now I am building the data warehouse and I come across a table with a record that has '' as the primary key column value. It is not NULL, it is ''.
I need to know for sure if this is allowed as a primary key value and also can I use it as a foreign key reference? Can I join on ''?
If the anser is no, which I hope it is, then I will need to request from my AS400 developers to fix all that code.
No one need tell me that '' is horrible practice, believe me I blew a gasket when I saw this and have advised our AS400 lead to add this to the standards documentation.
Thanks!

How to insert updated Oracle table into SQL Server 2008

Wed, 23 Sep 2009 20:32:25 Z

im not a DBA, but i have a related problem. we have an oracle warehouse hosted outside our datacenter. i need to come up with a solution to bring over tables from the warehouse every night, into a SQL Server 2008 server on our datacenter. i do not know how to figure out if the row im going to insert in SQLSVR from oracle really needs to be inserted. how do i insert only the changed rows from oracle into sql server?

the following post provied some insight, but can anyone please elaborate on it.

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ed2f4781-4d5d-4369-bf3e-1fa018093e92/

CDC in RC0 - cannot enable DB

Fri, 20 Jun 2008 16:11:18 Z

Restored a SQL2005 DB (Standards) to SQL2008. When try to enable CDC (USE Standards; GO EXEC sys.sp_cdc_enable_db; GO), receive following message:

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186
Could not update the metadata that indicates database Standards is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.
Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

I am sysadmin. I have tried running under SQL service account with is also sysadmin. Same result.

When i build a 'test' database from scatch in SQL2008, i can enable CDC. Had similar inconsistant experience using CPT5 and trying to enable. But that is not real world. I have to bring many 2005 DBs up to 2008. fyi - we are using LiteSpeed 4.8x for restore.

Merging range in Partition Function is taking hours to complete

Tue, 15 Sep 2009 19:25:46 Z

Hi there,

We have several large partitioned tables and I am trying to implement the sliding window, based on TechNet article. On the test table that contains 10 records per day all works flewlesly. When I try to do the same thing on the real table (120GB per day) it chokes on merging range.

The whole process should be fast so here are steps that I am doing, please let me know did I missed something:
1. Create temp partition function with two oldest days
2. Create temp partition schema based on temp partition function
3. Create temp table and clustered index on temp partitoin schema
4. Add next filespace to be "next used" by real partition schema
5. Split range in partition function so it uses new date
6. Switch 1st partition to move oldest day data from the real to temp table
7. Merge 1st and 2nd partition

Steps 1 - 6 are very fast but the last one (7) takes hours to run!

Thanks,
Marin

need to bind to querys

Wed, 16 Sep 2009 18:04:17 Z

I'm trying to bind to querys and would like to know most efficient way to do it

I have these that both need binding as one

SELECT * FROM categories ORDER BY categoryDesc

and

select count(*) as amount from products where idCategory = "&idCat&" and listhidden = 0"

idCat is in a loop from the first query, I dont want to recordsets just the one

Many thxs