- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
SQL SERVER table design for easier QV manipulation
Before I experiment, I wish to use some experience from those who have come before me! thank you in advance.
I have sales and revenue data in 5 dimensions in my SQLSERVER (SS) data tables.
FiscalPeriod -> 201301,201302, etc
CustomerNbr,
Product -> FA,SD, EX, SP, etc
OrderType -> Booking (Sales), Execution (Revenue)
RevenueType -> Install, FP, Amortized, Material Only, Labor only, Labor and Material
Value in Dollars
Which SS staging table will be easier to set up graphs and dashboards after importing into QV:
Table 1: (DB Normalized)
FiscalPeriod,
CustomerNbr,
Product
OrderType,
RevenueType,
Value
Table 2: (DB - Not Normalized)
FiscalPeriod
Customer Number
Product
OrderType,
RevType1Value,
RevType2Value,
RevType3Value,
RevType4Value,
RevType5Value,
TotalRevValue
Thanks in advance.
Sportsgirl. . .
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if you use Count( DISTINCT CustomerNbr) it will be unique for each revenue type
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Table 1 would be my preference so I can use RevenueType as a ListBox or Dimension.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Table 1 is better for me too.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So then if there is a customer count for all revenue types,
will the customer be unique customer count? or counted twice, once for each revenue type?
Customer counts will be very important for an average per customer of the misture of different revenue types. .
just curious before doing the SQLSERVER setup and testing. . .
thanks
T L
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if you use Count( DISTINCT CustomerNbr) it will be unique for each revenue type
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count( DISTINCT CustomerNbr) exists within SQL SERVER, but not within MS Access, which shows I came up through the desktop database world. . . DT_AdamWilson thank you again!