dimensional modeling

I need some guideline for designing diemsional model for the situation
given below. Suppose we track individual sales transactions alongwith
different categories of product, areas and employee who made sale.
Sales targets have alreay been assigned to employees for a given time
period eg. quarter. How should we design DM which allows to track not
only actual sales but also compares it targets alloted? Where should be
alloted target amounts need to be stored?
In SQL 2005 UDM, you can have a single dimension connected to two fact
tables on a different levl of granularity, which is exactly what you need
(sales transactions vs. target sales). Check the "Defining Dimension
Granularity within a Measure Group" part of the Analysis Services Tutorial
in SQL 2005 BOL (http://msdn2.microsoft.com/en-us/library/ms166573.aspx).
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"viki" <waqer@.yahoo.com> wrote in message
news:1133692623.706401.175280@.z14g2000cwz.googlegr oups.com...
>I need some guideline for designing diemsional model for the situation
> given below. Suppose we track individual sales transactions alongwith
> different categories of product, areas and employee who made sale.
> Sales targets have alreay been assigned to employees for a given time
> period eg. quarter. How should we design DM which allows to track not
> only actual sales but also compares it targets alloted? Where should be
> alloted target amounts need to be stored?
>
|||Hi Viki,
I have a beginners page on my web site www.peternolan.com. It contains
links to books I recommend. Ralphs books cover these areas in detail.
There are also sample databases for what you are asking...The Data
Warehouse Design Solutions book would also be useful to you as you will
surely not stop at merely doing sales tracking...
Best Regards
Peter
www.peternolan.com

dimensional modeling

I need some guideline for designing diemsional model for the situation
given below. Suppose we track individual sales transactions alongwith
different categories of product, areas and employee who made sale.
Sales targets have alreay been assigned to employees for a given time
period eg. quarter. How should we design DM which allows to track not
only actual sales but also compares it targets alloted? Where should be
alloted target amounts need to be stored?In SQL 2005 UDM, you can have a single dimension connected to two fact
tables on a different levl of granularity, which is exactly what you need
(sales transactions vs. target sales). Check the "Defining Dimension
Granularity within a Measure Group" part of the Analysis Services Tutorial
in SQL 2005 BOL (http://msdn2.microsoft.com/en-us/library/ms166573.aspx).
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"viki" <waqer@.yahoo.com> wrote in message
news:1133692623.706401.175280@.z14g2000cwz.googlegroups.com...
>I need some guideline for designing diemsional model for the situation
> given below. Suppose we track individual sales transactions alongwith
> different categories of product, areas and employee who made sale.
> Sales targets have alreay been assigned to employees for a given time
> period eg. quarter. How should we design DM which allows to track not
> only actual sales but also compares it targets alloted? Where should be
> alloted target amounts need to be stored?
>|||Hi Viki,
I have a beginners page on my web site www.peternolan.com. It contains
links to books I recommend. Ralphs books cover these areas in detail.
There are also sample databases for what you are asking...The Data
Warehouse Design Solutions book would also be useful to you as you will
surely not stop at merely doing sales tracking...
Best Regards
Peter
www.peternolan.com

Dimension values without data in a fact table

I have an ODS system and a Data warehouse system
I have a Sales fact table in the ODS system and I have these fields:
SALES
ID_CUSTOMER (PK),
ID_MODEL (PK),
ID_TIME (PK),
SALES,
QUANT_ART,
COST
Then in some records in the fields ID_Time or ID_Model or ID_Customer I
don’t have values (NULL) because in the transactional systems these record
don’t have values (NULL).
The users want to generate aggregate reports with the Sales table...
The question is:
I have to put a “dummy” value in the dimensions Customer, Model and Time
(for example “0”) and put this value in the fact table if the dimensions
fields have NULL values?
Or I have to leave the NULL values?
What is the best choice? Why?
Personally -- and this really does boil down to personal preference -- I
believe that one of the key things that should happen during an ETL is
elimination of all "questionable" data. That includes unknown data -- can
you really report on something that's unknown? At the least, generate
well-known tokens to replace the NULLs with. If possible, get rid of those
rows on the way in (of course, that really depends on context) -- perhaps
they can appear in the aggregate data, but not in the line-level data?
There are various ways of dealing with the problem, but personally I am very
firm when designing data warehouses and ensure that, one way or another,
there will be absolutely no NULLs in the database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"CLAUDIO" <CLAUDIO@.discussions.microsoft.com> wrote in message
news:937E414E-AB9A-4E4D-8A58-D8251139550E@.microsoft.com...
> I have an ODS system and a Data warehouse system
> I have a Sales fact table in the ODS system and I have these fields:
> SALES
> ID_CUSTOMER (PK),
> ID_MODEL (PK),
> ID_TIME (PK),
> SALES,
> QUANT_ART,
> COST
>
> Then in some records in the fields ID_Time or ID_Model or ID_Customer I
> don't have values (NULL) because in the transactional systems these record
> don't have values (NULL).
> The users want to generate aggregate reports with the Sales table...
> The question is:
> I have to put a "dummy" value in the dimensions Customer, Model and Time
> (for example "0") and put this value in the fact table if the dimensions
> fields have NULL values?
> Or I have to leave the NULL values?
> What is the best choice? Why?
>
sql

Dimension values without data in a fact table

I have an ODS system and a Data warehouse system
I have a Sales fact table in the ODS system and I have these fields:
SALES
ID_CUSTOMER (PK),
ID_MODEL (PK),
ID_TIME (PK),
SALES,
QUANT_ART,
COST
Then in some records in the fields ID_Time or ID_Model or ID_Customer I
don’t have values (NULL) because in the transactional systems these record
don’t have values (NULL).
The users want to generate aggregate reports with the Sales table...
The question is:
I have to put a “dummy” value in the dimensions Customer, Model and Time
(for example “0”) and put this value in the fact table if the dimensions
fields have NULL values'
Or I have to leave the NULL values?
What is the best choice? Why?Personally -- and this really does boil down to personal preference -- I
believe that one of the key things that should happen during an ETL is
elimination of all "questionable" data. That includes unknown data -- can
you really report on something that's unknown? At the least, generate
well-known tokens to replace the NULLs with. If possible, get rid of those
rows on the way in (of course, that really depends on context) -- perhaps
they can appear in the aggregate data, but not in the line-level data?
There are various ways of dealing with the problem, but personally I am very
firm when designing data warehouses and ensure that, one way or another,
there will be absolutely no NULLs in the database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"CLAUDIO" <CLAUDIO@.discussions.microsoft.com> wrote in message
news:937E414E-AB9A-4E4D-8A58-D8251139550E@.microsoft.com...
> I have an ODS system and a Data warehouse system
> I have a Sales fact table in the ODS system and I have these fields:
> SALES
> ID_CUSTOMER (PK),
> ID_MODEL (PK),
> ID_TIME (PK),
> SALES,
> QUANT_ART,
> COST
>
> Then in some records in the fields ID_Time or ID_Model or ID_Customer I
> don't have values (NULL) because in the transactional systems these record
> don't have values (NULL).
> The users want to generate aggregate reports with the Sales table...
> The question is:
> I have to put a "dummy" value in the dimensions Customer, Model and Time
> (for example "0") and put this value in the fact table if the dimensions
> fields have NULL values'
> Or I have to leave the NULL values?
> What is the best choice? Why?
>

Dimension theory design question.

If I have a sales fact table with 1000 sales to 20 different customers would I have 20 rows in my customer dimension table or 1000 rows? Each sales row has a customer, but many would be duplicates. Do I add a row to the customer dimension for each sale or each customer?

Thanks.

Hello! You will have 20 customer records in your customer dimension table and 1000 records in your sales fact table.

You will have a one to many relation between the customer dimension table and the fact table.

Select Distinct (TSQL) will help you with duplicates in the customer dimension.

HTH

Thomas Ivarsson

|||

Thanks.

To recap: I have an identity integer field for the surrogate key in the dimCustomer table and a business key field. I have two choices for the business key: I can use the CustomerID or the SalesNum from the OLTP. I gather from your response that I should use the CustomerID as the business key. Then when I load the dimension table, if a new sale goes to an existing customer, a new row will NOT be added to the dimCustomer table. When I subsequently load my fact table, I'll use the CustomerID in my sales row to point to the business key in the dimension table and retrieve the surrogate key which will be loaded into the fact table as the foreign key to dimCustomer and part of my aggregate key in the factSales table.

Did I say that right? (There's a whole lot of keys goin on.)

|||

Correct! You will only add customers when the customer business key is not in that dimension table.

Yes, it is good design to use integers, non business keys, as a primary key(dimension table) and foreign key(fact table)

You load the dimension table first and the fact table after and you update the keys in the way you have described.

The fact table will only have the surrogate keys and the dimension table both the business key and the surrogate key.

Regards

Thomas Ivarsson

Dimension Security

Hello,

I have a question about the dimension security. I have an AS2005 database and I had setup the roles so that a Sales Manager can only see the data of his dimension.

If a create a report in Reporting Services or a report in Excel placing a member of the dimension where the sales manager belongs I have no problem and the report will show me only the correct data.

But, if I have a report where I have, for example, the product group on the rows and the month on the column, I see the data of ALL sales manager both on reporting services and excel.

Is there a way to filter the data even when I do not have any dimension member linked to the security on the report?

Thank you

Hi Andrea,
I guess that it will have something to do with Visual Totals. Are you sure that this option is turned on (advanced dimension data tab for your restricted attribute)?

Radim|||

Thank you very much! That option fixed everything!

Andrea

Dimension formula

In a sales cube I have added dimension formulas to an account dimension. Example: NetRevenuePer100kg. The formula is:

NetRevenue / Quantity * 100

When I use this in a MDX query like

select

{[DIM_ACCOUNT].[NetRevenuePer100kg]} on columns,

{[DIM_PRODUCT].[WOOD]} on rows

from cube

where ([DIM_TIME].[2005].[Q1])

everything is fine.

But using the following calculated member in the where clause:

... member [DIM_TIME].[MY_TIME] as 'Sum([DIM_TIME].[2005].[Q1].[Jan]:[DIM_TIME].[2005].[Q1].[Mar]) ...

... where ([DIM_TIME].[MY_TIME])

I get a value which is about 3 times greater than the correct one.

I think it is a problem about the calculation order. It seems that first the value for each month is calculated and then they are summed up. Using the AVG function is not the solution. It gives only a almost correct value.

When I use a calculated member instead of a dimension formula everything is ok.

But I wanna get it right with a dimension formula.

Thanks for any help

Cornelius,

You are correct about your issue being related to solve order. In the example you gave you should be able to get the result you are looking for by replacing the SUM() function with AGGREGATE().

Try:

... member [DIM_TIME].[MY_TIME] as 'AGGREGATE([DIM_TIME].[2005].[Q1].[Jan]:[DIM_TIME].[2005].[Q1].[Mar]) ...

HTH,

- Steve

|||

Thanks Steve for your reply!

I tried it. But there is no difference between Sum and Aggregate.

|||

Cornelius,

I should have confirmed this to begin with, but is your "NetRevenuePer100kg" calculation in the cube? Can you provide the MDX for both calulations and the query?

- Steve

|||

Hi Steve,

formula in the account dimension [NetRevPer100kg]:

iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)

The complete MDX Query (please copy and enlarge in your editor):

with
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)'
member [TIME_CalendarYear].[BasisZeitraum] as 'SUM({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]'
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})'
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle Bereichsleiter].Children
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechnung],[PRODUCT_GROUP].[sy_Slicer])

The calculated member [Test_FINE] gives the correct result. The member based on the dimension formula [NetRevPer100kg] only when I use the time member [Quartal] (uncommented).

It is a strange thing for me, that a calculated member and a member based on a dimension formula returning different results allthough using the identical mdx expression. And it only happens when I use a Sum (or Aggragate function) on an other member in the query.

Thanks for a reply

|||

Cornelius,

I noticed in your MDX you still have a background member defined using SUM:

member [TIME_CalendarYear].[BasisZeitraum] as 'SUM({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'

The reason I think you are running into this issue is related to solve order. The AGGREGATE function will correctly sum up the numerator and denominator before performing the division. SUM will simply add up the percentages. In AS2K5 all calculations in the cube script are evaluated before any query or session scoped calculated members. This is why you are probably seeing a difference in the cube based calculated member results versus your query scoped calculated member. It is important that any aggregated member in the WHERE clause be calculated using AGGREGATE rather than SUM.

HTH,

- Steve

|||

Hi Steve,

with
member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]'
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)'
member [TIME_CalendarYear].[BasisZeitraum] as 'AGGREGATE({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})'
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]'
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})'
member [SALES_MANAGER].[Alle B] as 'AGGREGATE({[SALES_MANAGER].[Alle Bereichsleiter]})'
member [CATEGORY].[Rechn.] as 'AGGREGATE({[CATEGORY].[Rechnung]})'
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle B]
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechn.],[PRODUCT_GROUP].[sy_Slicer])

In my case there is no difference using the SUM or AGGREGATE function.

I played with the solve_order.

with
member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]', solve_order=1
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)', solve_order=1
member [TIME_CalendarYear].[BasisZeitraum] as 'AGGREGATE({[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[Jan.]:[TIME_CalendarYear].[All].[2005].[1. Halbjahr].[1. Quartal].[M?r.]})', solve_order=0
--member [TIME_CalendarYear].[BasisZeitraum] as '[TIME_CalendarYear].[2005].[1. Halbjahr].[1. Quartal]', solve_order=0
member [PRODUCT_GROUP].[sy_Slicer] as 'AGGREGATE({[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044125)],[PRODUCT_GROUP].[ZZ_All].[Eingekaufte Artikel].[Handelsware].[Handelswaren].[Emcat TC 30 (80441)].[Emcat TC 30 (8044115)]})', solve_order=0
member [SALES_MANAGER].[Alle B] as 'AGGREGATE({[SALES_MANAGER].[Alle Bereichsleiter]})', solve_order=0
member [CATEGORY].[Rechn.] as 'AGGREGATE({[CATEGORY].[Rechnung]})', solve_order=0
select
{
([ACCOUNT_Sales].[Quantity]),
([ACCOUNT_Sales].[ContributionMargin].[NetRev]),
([ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg]),
([ACCOUNT_Sales].[Test_FINE])
} properties [ACCOUNT_Sales].[DISPLAY_CAPTION], [ACCOUNT_Sales].[DISPLAY_FORMAT], [ACCOUNT_Sales].[DISPLAY_STYLE] on columns, non empty
{ [SALES_MANAGER].[Alle B]
} on rows
from Sales
where ([TIME_CalendarYear].[BasisZeitraum], [CATEGORY].[Rechn.],[PRODUCT_GROUP].[sy_Slicer])

Applying the solve_order 1 to the Account members and the solve_order 0 to the other dimension members I receive the same results ([Test_FINE] is correct, [CM_NetRevPer100kg] not).

member [ACCOUNT_Sales].[ContributionMarginPer100kg].[CM_NetRevPer100kg] as '[ACCOUNT_Sales].[ContributionMarginPer100kg].[NetRevPer100kg]', solve_order=0
member [ACCOUNT_Sales].[Test_FINE] as 'iif(([ACCOUNT_Sales].[Quantity]) = 0, NULL, [ACCOUNT_Sales].[NetRev] / [ACCOUNT_Sales].[Quantity] * 100)', solve_order=0

Setting the solve_order of the Account members to 0 and the solve_order of the other dimension members to 1 also the calculated member [Test_FINE] returns a incorrect value. This behaviour I understand: in this case first the calculation is performed and then the calculation results are aggregated.

But I still see no way to influence the aggregation of the member based on the dimension formula.

|||

Cornelius,

I am sorry that this is still not working. When you say you are using a dimension formula, can you be more specific. Are you using a "custom rollup column" or are you using a MDX statement in the cube script? I am curious as to the source of this problem and if possible it would be helpful if you could email me a copy of the project files.

spontello@.proclarity.com

- Steve

|||

Thanks to Steve there is a solution.

When using custom member formulas (what I called dimension formulas) there is no way to override the default aggregation behaviour of the cube. That issue is discussed in a previous post listed here:

http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_thread/thread/885149d85c381cb0/a0bc768a4066d4fe?q=custom+rollup+division&rnum=2#a0bc768a4066d4fe

The solution here is the ussage of calculated members.

But my calculations are regarding to members in an account dimension. In this account dimension I had declard properties like custom_caption, custom_style or custom_format I use in my client application.

On calculated members I cannot declare these properties.

So the next issue was to join an accurate calculation to the members in the account dimension. For this issue Steve proposed the ussage of calculated cells.

Using the Calculated Cells Wizard you can define a MDX Expression for the calculation and specify a specific member (in my case from the account dimension) to apply the calculation to.

It works wonderful.