Basic Fact Table Design Question

I'm not sure if this is the proper board....but....

I have a sales data from two different cost centers that generate the same data. When setting up my OLAP db, should I:

(1) design two fact tables that would have the same attributes,

(2) one fact table and one dimension table that contained a key field and a sales type field, or

(3) a fact table in which one of the attributes was the sales type field.

Thanks

Do the facts from the different cost centers represent the different instances of the same business process or event? If so, then they should be modeled as a single fact. Another way to approach this is to ask, "Would my users consider these to be the same thing and therefore would expect these to be in the same place in the DW?".

If you model these as a single fact, the cost center would be best handled in a dimension. Sounds like it would have low cardinality. (Is it possible you would ever have more than two cost centers? Many organizations have hundreds or even thousands of cost centers.)

B.

|||Cost center was probably a bad term. The business makes money selling stuff and performing services. They want total sales data and then sales broken between sales and services. The facts are pretty much the same: amount, date, cost of sale/service, etc. I thought a dimension table would work but it's going to be a table with an attribute that has only one of two conditions, plus a key field and a foreign key in the fact table. Seems like a lot of work for a simple A/B field.|||

I think option 2 is the better, but I need to understand some more requirements to decide.

Create a dimension is better, because tomorrow if you have more cost centers, your structure will be more flexible.

But option 3 is not bad...

Regards!

|||

This sounds like a line item fact table. For example, I bring my car in for repairs and what not, I receive a bill (an invoice). On that bill, there will be items for various parts and labor for various services, each with the number of units consumed, a price per unit, and the total cost for that line.

Each item purchased, it's code, name, base price, etc., belongs in a "product" dimension. We may identify each of these products as belonging to a "Parts" or "Service" category, but that catorigation too would belong in the dimension table.

Take a look at the Kimball book, "The Data Warehouse Toolkit", Chapters 2 & 5, for more ideas on this.

Good luck,

B.

Basic Design Question Currency Dimension or New Measure

Hi All,
I am in the middle of a decision and I would like to ask experts for their
ideas.
I have Sales Reporting System on MSSQL 2000 + AS + RS
So far I have used only USD as reporting currency, now I am going to add
EURO and Local Currencies. My Current Model has "Sales", "Performance%" etc
as measures and Items,Countries etc as dimensions.
The question is how should I add new currencies to the system ?
As new Measures Sales_EURO?
or
As adding them to the fact table as rows keeping only one Sales measure and
adding a Currency dimension ?
I have around 2 million record in my fact table and data is accessed via
Reporting Services reports and via an application using OWC Pivot Table.
Thanks in advance?
erdalErdal,
Check out Adventure Works Enterprise Analysis services sample. I believe it
solves your problem via a currency dimension.
"erdal akbulut" <erdalim21@.yahoo.com> wrote in message
news:eJWeL32cGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I am in the middle of a decision and I would like to ask experts for their
> ideas.
> I have Sales Reporting System on MSSQL 2000 + AS + RS
> So far I have used only USD as reporting currency, now I am going to add
> EURO and Local Currencies. My Current Model has "Sales", "Performance%"
> etc
> as measures and Items,Countries etc as dimensions.
> The question is how should I add new currencies to the system ?
> As new Measures Sales_EURO?
> or
> As adding them to the fact table as rows keeping only one Sales measure
> and
> adding a Currency dimension ?
> I have around 2 million record in my fact table and data is accessed via
> Reporting Services reports and via an application using OWC Pivot Table.
> Thanks in advance?
> erdal
>
>
>|||Tim Thanks for you reply.
Unfortunately I dont have SSAS 2005 installed, think that sample comes with
SSAS 2005. Do you know is there any documentation for that project that I
can use without installing the application itself?
erdal
"Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> wrote in message
news:eD1tkPdeGHA.380@.TK2MSFTNGP04.phx.gbl...
> Erdal,
> Check out Adventure Works Enterprise Analysis services sample. I believe
it
> solves your problem via a currency dimension.
> "erdal akbulut" <erdalim21@.yahoo.com> wrote in message
> news:eJWeL32cGHA.4576@.TK2MSFTNGP05.phx.gbl...
their[vbcol=seagreen]
>|||Not that I'm aware of. Sorry.
-Tim
"erdal akbulut" <erdalim21@.yahoo.com> wrote in message
news:uE7najleGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Tim Thanks for you reply.
> Unfortunately I dont have SSAS 2005 installed, think that sample comes
> with
> SSAS 2005. Do you know is there any documentation for that project that I
> can use without installing the application itself?
> erdal
> "Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> wrote in message
> news:eD1tkPdeGHA.380@.TK2MSFTNGP04.phx.gbl...
> it
> their
>

Basic Aggregate/HAVING question

I want to find the order that has the largest quanity in pubs.dbo.sales
If I try to use an aggregate in the WHERE clause I get an error
--
SELECT ord_num
FROM pubs.dbo.sales
WHERE qty=max(qty)
/*
An aggregate may not appear in the WHERE clause unless it is in a subquery
contained in a HAVING clause
or a select list, and the column being aggregated is an outer reference.
*/
But I am almost certain that I used to be able to do this in Sybase T-SQL
and it worked:
SELECT ord_num
FROM pubs.dbo.sales
HAVING qty=max(qty)
Now in MS T-SQL I get these errors:
/*
Column 'sales.ord_num' is invalid in the select list because it is not
contained in an aggregate function
and there is no GROUP BY clause.
Column 'sales.qty' is invalid in the HAVING clause because it is not
contained in an aggregate function
and there is no GROUP BY clause.
*/
It looks like I can get what I want by either one of two ways
1. A subquery:
SELECT ord_num, qty
FROM pubs.dbo.sales
WHERE qty=(SELECT max(qty) FROM sales)
/*
ord_num qty
-- --
QA7442.3 75
*/
2. A GROUP BY with TOP
SELECT TOP 1 ord_num, max(qty) AS 'qty'
FROM pubs.dbo.sales
GROUP BY ord_num
ORDER BY max(qty) DESC
/*
ord_num qty
-- --
QA7442.3 75
*/
I have 2 questions:
1. Are there other (better) ways to write this?
2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?> But I am almost certain that I used to be able to do this in Sybase T-SQL
> and it worked:
> SELECT ord_num
> FROM pubs.dbo.sales
> HAVING qty=max(qty)
Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause is
applied after the aggregation so it doesn't make sense to reference a base
table column in this way unless it exists in the aggregate result.
> 1. Are there other (better) ways to write this?
I doubt it's any "better" but just for variety here's an alternative:
SELECT ord_num, qty
FROM Sales
WHERE qty >= ALL
(SELECT qty
FROM Sales)
I prefer the WHERE qty= subquery method.
> 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?
Yes. HAVING without GROUP BY implies an aggregation across the whole set in
the same way that an aggregate function does without GROUP BY. The result
will be, at most, one row. You can use HAVING to return a value only if some
condition is met:
SELECT MIN(ord_date)
FROM Sales
HAVING MIN(ord_date)<='20000101'
And it can be especially useful in conjunction with EXISTS:
...
EXISTS -- Is ord_date unique?
(SELECT 1
FROM Sales
HAVING COUNT(DISTINCT ord_date)=COUNT(*))
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks David for the detailed answers.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:H4idnZ3fHI4t8lOi4p2dnA@.giganews.com...
> > But I am almost certain that I used to be able to do this in Sybase
T-SQL
> > and it worked:
> >
> > SELECT ord_num
> > FROM pubs.dbo.sales
> > HAVING qty=max(qty)
> Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
> syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause
is
> applied after the aggregation so it doesn't make sense to reference a base
> table column in this way unless it exists in the aggregate result.
>
> > 1. Are there other (better) ways to write this?
> I doubt it's any "better" but just for variety here's an alternative:
> SELECT ord_num, qty
> FROM Sales
> WHERE qty >= ALL
> (SELECT qty
> FROM Sales)
> I prefer the WHERE qty= subquery method.
>
> > 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so,
when?
> Yes. HAVING without GROUP BY implies an aggregation across the whole set
in
> the same way that an aggregate function does without GROUP BY. The result
> will be, at most, one row. You can use HAVING to return a value only if
some
> condition is met:
> SELECT MIN(ord_date)
> FROM Sales
> HAVING MIN(ord_date)<='20000101'
> And it can be especially useful in conjunction with EXISTS:
> ...
> EXISTS -- Is ord_date unique?
> (SELECT 1
> FROM Sales
> HAVING COUNT(DISTINCT ord_date)=COUNT(*))
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>

Bar charts

I have created a bar graph but cannot find a way to change the bar's color for different series.

I have a graph which has sales men and their sales between dates

Thanks for your help

Hi there,

There are lots of postings for this. Can you try this one?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=222204&SiteID=1

cheers,

Andrew

|||Have you set up a series group?|||

No I hadn't

Thanks for your help

|||

The only problem Im getting with setting it up as a series group is the chart lines are now the size of a match.

Is it possible to fatten them up?

sql

Bar Chart Scale

Can you have different scales on a bar chart. I want to chart sales and quantity. Oracle lets you label the top of the bar chart as money and the bottom as quantity.

I am dividing my sales by one million and quantity by one thousand to make them similar in size on the same chart. I am using the sum of the sales/1000000 as a point label. Is there any way to limit the number of decimal places displayed. Currently it is displaying something like 1.94889312043; 1.95M would be better.

Thank you.

Change the format code for the X and/or Y axis to N or F.|||

I should have been more clear.

I want sales by month AND tons shipped by month to show on the same bar chart.

I can put then on the chart together. I just wondered if you could label the y axies differently.

Oracle does something like to do something like:

Sales

0.0M 20M 40M

$$$$$$$$$

Jan

ttttttttt

$$$$$$$$$$$

Feb

ttttttttt

$$$$$

Mar

ttt

0.0K 50K 100K

Net Tons Shipped