[SQL Server Data Warehousing] How would my fact and dim tables look like in this example?

Please bear with me.

This is a very simplified example, but it's more or less the foundation.

We get hourly data for several cells, for example ORLFL-1-123-1. This cell is a combination of AREA-CAB-SITE-SECTOR.

Data analysts want to get several KPIs from all the data coming from these cells, but by AREA, CAB, SITE, SECTOR (not cell). And the smallest date range is daily (daily, weekly, monthly, etc).

Let's say the KPIs are KPI_ABC, KPI_NDO, KPI_DRT, KPI_QRR.

I have DimDate, DimSite, DimArea, DimCab, DimSector. I assume that's correct.

My question are the Fact tables. I was thinking of creating one Fact table for each KPI (FactKPI_ABC, FactKPI_NDO, etc). Each Fact table would have the total per site per day, plus the attributes for the site. For example the fields for FactKPI_ABC would be:

Datekey
Site
KPI_ABC
Area
Cab
Site
Sector

The problem I see (if it's a problem) is that all the tables would be exactly the same, since the only value that changes is the actual KPI total.

Is this the correct way to go? Any help is greatly appreciated.

Thanks.

---

VM

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] SSIS Auditing Mechanism?‏

I'll help if I can...

In my approach, I have 2 audit tables; one for logging package execution and the other for logging table processing.  Before each data flow I log the package like this:

INSERT INTO AuditPkgExecution (
PkgName, PkgGUID, PkgVersionGUID, PkgVersionMajor, PkgVersionMinor, ExecStartDT, ParentPkgExecKey)
Values (@PkgName, @PkgGUID, @PkgVersionGUID, @PkgVersionMajor, @PkgVersionMinor, @ExecStartDT,
@ParentPkgExecKey)

On the insert my PkgExecKey increments, so I grab it and store it in a user variable to use later in my table logging.

SELECT MAX(PkgExecKey) AS PkgExecKey
FROM AuditPkgExecution
WHERE PkgName = @PkgName AND ExecStartDT = @ExecStartDT

Now, when loading the source file I count the input records and store that as a variable, then create the initial audit record for that source's destination table and store that TableProcessKey in a variable to use later.
INSERT INTO AuditTableProcessing (
PkgExecKey, TableName, TableInitialRowCnt)
Values (@PkgExecKey, 'FactTicket', @RowCount)

SELECT Max(TableProcessKey) AS TableProcessKey
FROM AuditTableProcessing
WHERE PkgExecKey = @PkgExecKey
AND TableName = 'FactTicket'

When I load the source data, I first count the starting number of rows in the destination table and store it in a variable.  Then I record the number of rows inserted, updated, errored on insert, and errored on update and store those in variables.  Finally, after the load I count the final number of rows in the destination table, then update the audit table...

UPDATE AuditTableProcessing
SET
ExtractRowCnt = @RC_Xtrct ,
InsertStdRowCnt = @RC_Normal,
UpdateRowCnt = @RC_Updated,
InsertErrorRowCnt = @RC_InsertError,
UpdateErrorRowCnt = @RC_UpdateError,
TableFinalRowCnt = @RowCount,
SuccessfulProcessingInd = 'Y' --if we made it here, we're cool
WHERE TableProcessKey = @TableProcessKey

The final step is, after the data flow task completes, to update the audit table for package execution:

UPDATE AuditPkgExecution
SET ExecStopDT = getdate()
, SuccessfulProcessingInd = 'Y'  
WHERE PkgExecKey = @PkgExecKey

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Optimum DR strategy for datawarehouse DBs

We have a SQL Server 2008R2 servers with Datawarehouse DBs which gets populated by  SSIS packages for ETLs. The database sizes are around 380GB - 400GB. The ETLs run throughout the night and load the data and we load around 1 million rows of data and no activity happens during the day time. We can afford a data loss of 1 day at any point. The business wants to design an DR stretegy for our DWH databases.  I want to keep into account the performance factor, network bandwidth for this environment.

I do not want to just rely on full backups as the size of each full backup for DWH dbs is around 80GB (After compression enabled) and we have 5 DBs and it will choke network bandwidth if i copy the backups to DR site over the network.

i have proposed the following solution

1. All the DBs in simple recovery model  

2. Take full backups (compression enabled) every weekend

3. Take differntial backups(compression enabled) during the weekday

Can you guys suggest if this is a correct DR strategy for Datawarehouse DBs considering performance,network and dataloss affordable and please help out with any other stretegy you can think of...

-sqluser

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Star Schema modeling on Order Header/Details data

In my situation I have a Order Header which contains info like order date, expected ship date, expected arrival date, vendor, buyor, etc... and that is my PO dimension.  I have an Order Detail that contains the line items with qty and cost.  I have a Receiver Header that includes receipt date and receipt status, and I have a Receiver Detail that includes qty and cost of received goods.

For Orders I calculate things like Avg Lead Time, Fill Rate %, and show current On Order.

Depending on how you handle your accounting, a discount that is applied to an order and NOT the line items on the order can skew your margin reporting.  If I had that situation, I would likely allocate the order-level discount to the cost of the line items on a pro-rata basis.

.

social.technet.microsoft.com/Forums

[MS SQL Server] SQL Performance Monitoring software

---

• SQL Performance Monitoring software
• SQL Server 2008 R2 SP2 and KB2551254
• How to Give User Access to another user
• TempDB on Amazon Ephemeral drives
• ssas understanding
• Database syncronization script for DR

SQL Performance Monitoring software Posted: 22 Apr 2013 04:53 AM PDT Hey guys,I'm looking for a good performance monitoring software for our sql servers. So far I was able to find RedGate SQL Monitor, which seems pretty nice, Ignite Confio 8, which also very good, and looks like they have excellent support, and Embarcadero DB Optimizer 3.5 (recommended by vendor). I'm not sure which one to go for, and why. Can anyone please recommend something that fairly simple to use, will give me great feedback on performance of database, suggest enhancements, and won't break the bank.

SQL Server 2008 R2 SP2 and KB2551254 Posted: 10 Apr 2013 02:21 AM PDT Hello,I'm in process of installing several servers with SQL Server 2008 R2 SP2. So far I've completed 5 of them and I'm finding a common issue. That Reporting Services fails to start after applying SQL Server 2008 R2 SP2. I've dug a little bit and have found one Connect post where Microsoft says they cannot reproduce it and several blogs indicating removing this KB resolves the issue.My question is this, has anyone else ran into this issue and have differing opinions? The information I've dug up seems a bit inconclusive and nothing concrete. I have however uninstalled it from one server and confirmed it now starts successfully.

How to Give User Access to another user Posted: 22 Apr 2013 01:23 AM PDT [font="Verdana"]Hi All,In Server I have 2 users one is MasterAccess and other one is ap. there are many tables but masteracess only can view few tables of CAS database and [b]ap user does not have the access of CAS database[/b] but he is the db_owner of other databases.Now in AP5 database I have created a user MasterAccess and Now I have given the rights to the ap user who can view the tables of MasterAccess user. I have written the code like below select * from CAS.dbo.BankOffices and the code is working fine. Now if i update the same table it is working. Begin tranupdate CAS.dbo.BankOffices set Bankoffices_Id =5where bank_code=115and the result shows me 2 rows affected....how? MasterAccess can only view the CAS table. He can not edit, insert; only he can view the selected 5 tables...Now what I want is that ap user will also able to view these 5 table but he will not be able to insert, update delete etc...Please help me[/font]

TempDB on Amazon Ephemeral drives Posted: 21 Apr 2013 10:23 AM PDT Has anybody used the EC2 Ephemeral drives as a location for their TEMPDB databases?Normally I use a standard volume but these drives are supposedly faster for TEMPDB -- but are volatile of course which [b]should [/b]not be an issue for the TEMPDB as long as it is put in the root of the drive and not a folder (as the folder will not get re-created when the machine restarts).thanks

ssas understanding Posted: 21 Apr 2013 06:03 AM PDT I am currently working on SSAS 2008.Gone through ebooks for understanding but never got proper indepth understanding of dimensions and measure,kpI...I would like to know from where to get a good study material with samples/examples for ssas 2008.can anybody suggest?

Database syncronization script for DR Posted: 21 Apr 2013 04:06 PM PDT Hi All,My client is planning to do a daily sycn using backup / restore script. weekly full backup follwed by daily incremental backup to be apply on the secondary server and incase of DR the secondary server will come up using a recover script.Is there any script available to automate all the process in batch file which will do the backup / restrore activity daily wise.Waiting for your input.Thank you all.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[Articles] A Billion Transactions

---

A Billion Transactions Posted: 21 Apr 2013 11:00 PM PDT A billion transactions is a lot of activity on your database, however Steve Jones thinks more and more of us might see this on a regular basis. Microsoft's facilities group shows this to be the case.

You are subscribed to email updates from SQLServerCentral.com Articles tagged Editorial To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server Data Warehousing] Pricing of PDW Appliance

Hi JohnnyKahWang,

Dell Parallel Data Warehouse is built using Microsoft SQL Server 2012 Parallel Data Warehouse providing a massively parallel processing appliance that was built to handle the world of “Big Data” addressing both requirements of any data volume as well as variety and velocity by providing seamless integration with Hadoop. Please refer to: http://www.microsoft.com/en-us/sqlserver/solutions-technologies/appliances/dell-pdw.aspx

HP AppSystem for Microsoft SQL Server 2012 Parallel Data Warehouse represents a complete solution for any data of any size with significant advances in performance, massive, seamless scalability, and extremely low cost per TB. Details see: http://www.microsoft.com/en-us/sqlserver/solutions-technologies/appliances/hp-pdw.aspx

Thanks,
Eileen

---

Eileen Zhao
TechNet Community Support

.

social.technet.microsoft.com/Forums