Please guide - especially about Time Dimension and approach in general

Hi,

I have a table which contains all the transaction details for which I am trying to create a CUBE... The explanation below in brackets is only for clarity about each field. Kindly note that I am using the following table as my fact table. Let's call it tblFact

This table contains fields like transaction date, Product (which is sold), Product Family (to which the Product Belongs), Brand (of the product), Qty sold, Unit Price (at which each unit was sold), and some other fields.

I have created a Product dimension based on tblFact. I don't know if this is a good idea or not :confused: Is it okay and am I on the right track or should I base my Product Dimension on some other table (say tblProducts and then in the Cube editor link tblProducts with tblFact based on the ProductID field? Please guide.

Now coming to my last question:
Currently I am also creating my Time Dimension based on tblFact. Is this also a wrong approach?
1. Should I instead create the Time Dimension based on a different table (say tblTime) and again link up tblTime and tblFact in the Cube editor?

2. if yes, then how do I create tblTime from tblFact in a manner that it only contains all the transaction dates.

3. Assuming that I should take the tblTime approach, then should this table (tblTime) also contain the ProductID - representing the Product which was sold for each date in tblTime?

I realize that this is a lenghty post but reply and more importantly guidance from the experienced users will be greatly appreciated becuase I have recently started learning/playing around on the OLAP side of things and I know that this is the time that I get my foundations correct otherwise I'll end up getting used to some bad practice and will find it difficult to change my approach to cube designing later down the road.

So many thanks in advance and I eagerly look forward to reply from someone.No worries mate,

This is what the forum is for...

Ok - Down to what you need to do

When doing the design for a cube I always do a bit of anyalsis first. Looks like you have crack this bit. You know what your dimensions are - Time , product , brand etc This is what you should group on to build your fact table.

Your facts are going to be Qty Sold , Price. This is what you will be suming on with the SQL to build your fact table.

You said "should I base my Product Dimension on some other table (say tblProducts and then in the Cube editor link tblProducts with tblFact based on the ProductID field? Please guide."

This is exactly what a good cube design is based on mate.

I presume the basis of your fact table is a transactions type table.
First thing you need to do is build all your dimension tables.

A table for product dimension table should look something like :

create table tblProduct
(prod_id tinyint ,
prod_txt varchar (255)
)

Don't forget to put in a id for unknown product - just in case you get these in your base transaction table

Build the rest of your dimension tables like this and assign a tinyint composite key to each dimension. What you what is your fact table to be as small as possible in terms of datatypes.

Now once you have this you want to build your fact table.

What you do is take your fact table and join to each of you dimension tables (should be a left outer join) and sum on the qty and unit price and group up accross all your dimensions.

This now should be you fact table that you can reference in Anyalsis Manager. You will have to define all this with in here as well.

Whoo, that was an effort.

Any problems , questions give me a shout

Cheers|||Hi aldo_2003,

Many thanks for the reply. It answers a good number of my questions. Can you kindly advise regarding the remaining question i.e. the quoted portions below:

Build the rest of your dimension tables like this and assign a tinyint composite key to each dimension. What you what is your fact table to be as small as possible in terms of datatypes.
Question: By composite key do you mean define a Primary key in each table? I will do so but was planning to define the data type for my ProductID field (for example) in my tblProducts as int. However I'll follow your advise and instead use the datatype tinyint. Thanks for the tip :)

And my last question hopefully:
Now coming to my last question:
Currently I am also creating my Time Dimension based on tblFact. Is this also a wrong approach?
1. Should I instead create the Time Dimension based on a different table (say tblTime) and again link up tblTime and tblFact in the Cube editor?

2. if yes, then how do I create tblTime from tblFact in a manner that it only contains all the transaction dates.

3. Assuming that I should take the tblTime approach, then should this table (tblTime) also contain the ProductID - representing the Product which was sold for each date in tblTime?

I think Part 2 (above) is easy and all I have to do is make a copy of tblFact but this copy (say tblTime) will only contain the transaction date column (from tblFact). Kindly confirm my understanding.

However it's the answer to part 1 (above) and especially the part 3 above that is requested.

Looking forward to your reply.|||Your welcome,

forgot about the time question

what you want to do is create a tblTime dimension table

should basically have the grandularity that you want to use

you can find scripts on the net that will help you create and manage a time dimension table.

table should look like

create table tblTime
(date_time smalldatetime,
quarter tinyint,
month tinyint,
week tinyint ,
day int
)

populate this table with all the dates in your date range i.e

Jan 1999 12:00am to Jan 2009 12:00am

This is now your time dimension table.
Using anyalsis manager join back on to the fact table.
Anyalsis manager should guide you through the process of creating a time dimension.

Hope this helps

p.s the only reason I used a tinyint is that I assumed you would have no more than 255 products, if you have more then up the datatype to what you need

Cheers|||Hi again,

Don't mean to "over-flatter" but your replies REALLY have been of great help... Here I was trying to build everything (the fact as well as the dimensions) using only a single table and now I am quite clear regarding what's the right approach :)

3 last questions please :rolleyes:

you can find scripts on the net that will help you create and manage a time dimension table.
table should look like

create table tblTime
(date_time smalldatetime,
quarter tinyint,
month tinyint,
week tinyint ,
day int
)

That's another new tip :) Can u kindly guide where I can get these scripts from? I am assuming that these scripts will not only create the table (in a similar structure as you have suggested above) but will also populate the table with the all the desired date ranges e.g. Jan 1999 12:00am to Jan 2009 12:00am.

2nd last question: So my approach which I was assuming for creating tblTime (for the Time dimension was incorrect) i.e. I thought that this will simply contain the ALL the transaction dates from the transaction table (as I described in my previous post). But from your reply my understanding is that this approach is wrong.

and the last question: So the tblTime does not have to store the ProductID?

Sorry for all the botheration.|||No worries buddy

Glad to be of help

The time dimension table is stand alone and does not have to contain any other info other than time info.

I'll see if my collegue know and get back to you

Cheers|||http://www.databasejournal.com/features/mssql/article.php/10894_1466091_6

http://www.winnetmag.com/Article/ArticleID/23142/Windows_23142.html|||Originally posted by aldo_2003
The time dimension table is stand alone and does not have to contain any other info other than time info.

I'll see if my collegue know and get back to you

Cheers

Thanks aldo : for the reply, the link to articles, and also for the clarification about the time dimension's underlying table containing only time related information.

Kindly do let me know if you get a script which not only creates the table for time but also populates it ...

Bless you!|||Check this link http://www.winnetmag.com/Articles/ArticleID/41531/pg/4/4.html for any help.|||Originally posted by Satya
Check this link http://www.winnetmag.com/Articles/ArticleID/41531/pg/4/4.html for any help.

Hi Satya,

Many sincere thanks for the article. I went through it...

However at this stage I am learning the basics (as evident from this post and my questions to aldo) therefore I found he article to be real "HEVY STUFF" and very difficult to digest at the moment. I talks about the MDX world and that's something that I have yet to explore... I know I will have to get into MDX soon but have enough of the basic to get right first :)

I however would like to request if you can help me with another post from me in this forum (Subject" Need help to create my Time Dimension")... it's somewhat related to portion of the discussion in this post but I did not want to unnecessarily prolong this particular thread/post...

Looking forward to your help in my other post.

Thanks again and regards.|||Joozh
Since this has been answered so well i just wanted to chime in and suggest some reading for you

The Data Warehouse Toolkit by Ralph Kimball (http://www.bestwebbuys.com/books/compare/isbn/0471200247/isrc/b-home-search)

this is the second edition of this book revised in 2002 and it is a must have for every OLAP develper.sql

Pivottable 10/11 with SSAS 2005 cube 'hang' when using filter fields

Dear all,

I am running into some difficulties with an SSAS 2005 SP2 cube in combination with an OWC 11 pivottable client. Everything basically works fine.

The problem occurs when I make a selection with one or more dimensions in the 'filter' section.

For instance, if I build a pivottable which shows:

YEAR in the Colums

PRODUCT GROUP in the ROWS

CUSTOMER NAME in the ROWS

PRODUCT FAMILY in FILTER SECTION

SALES as Measure

As soon as I make a selection on YEAR (show only 2007) and PRODUCT FAMILY (show only 'bikes' and 'minibikes') , the pivottable basically hangs. On the server the memory usage of SSAS shoots up to 1.8(!) GB

After +/- 60 seconds I get a blank screen and sometimes: 'Cannot display data because of structural changes in the database'.

I tried to make the same selecion in my Excel2007 eval, which worked fine. Pivottable 10 gives the same error. The error also occurs in Visual Studio (off course also OWC11).

Altering the cube's storage settings did not help.

My cube's main facttable containts approx 2.5 mln records.

Is this a known problem, what can I do to fix it?

Many many thanks

Rex

Dear all,

After doing some research it seems that SP2 is causing the errors. When downgrading my SSAS 2005 to SP1 all problems vanished. Only problem now is the performance compared to SP2 which is significantly lower.

To really make sure it was SP2 I installed a fresh SSAS2005 machine without any servicepacks and built my cube, after intensive testing no problems.

Upgraded to SP1, no problems.

Upgrade to SP2, massive memory usage of the SSAS service and total lockup.

I also installed the cumulative 3161 post SP2 update, but this did not fix the issue.

The problem only occurs when filtering data in the pivottable's 'filter' section.

Any ideas?

|||

Problem solved!!

After installing the Cumulative Hoftfixes 2 for SP2 (build 3175), released Jun 22nd the problem has disappeared and the performance of my cubes is great!

Link to the hotfix: http://support.microsoft.com/kb/936305

Pivot Tables and MDX

Hi!
I want to write some MDX queries that pull the same data I'm retriving into
an excel pivot table that is hitting my cube. Up front, I'm pretty sure thi
s is not doable, but need to ask anyway. I want some way of hooking into th
e MSOLAP provider to see th
e MDX that's generated and sent to analysis services to retrieve the data in
to the pivot table. This would be an excellent shortcut to getting my MDX q
uery results to match what I'm getting in the pivot table.
Any anyone know of a way to see the MDX that's being generated from the pivo
t table?
-thanksInclude property Log File in your connection string.
Log File="C:\work\Sales_stock\mdx\excel_log\log.txt"
in log.txt you will find mdx queries.
Ramunas Balukonis
"brian p" <anonymous@.discussions.microsoft.com> wrote in message
news:399BECD7-270E-4539-BAC0-C628EB09D2B8@.microsoft.com...
> Hi!
> I want to write some MDX queries that pull the same data I'm retriving
into an excel pivot table that is hitting my cube. Up front, I'm pretty
sure this is not doable, but need to ask anyway. I want some way of hooking
into the MSOLAP provider to see the MDX that's generated and sent to
analysis services to retrieve the data into the pivot table. This would be
an excellent shortcut to getting my MDX query results to match what I'm
getting in the pivot table.
> Any anyone know of a way to see the MDX that's being generated from the
pivot table?
> -thanks

pivot tables & offline cube access

Is it possible to have multiple pivot tables in the same Excel workbook accessing the same offline cube file?

Currently, when the user is connected to the server it connects to a .asdatabase to populate many pivot tables. I need to allow the user to access a local cube file when they are not connected to the server.

I am using excel vba macros to connect the pivot caches to the online data source, which works perfectly. I would like to do the same to connect the pivot caches to the local cube file.

Is this possible? The local cube file is created off the server database using MDX (create global cube).

Thanks,
Lyn

If this is AS2005 - there is a bug in this area that is being fixed in the SP2 release. I think the fix would allow you to do what you've described.|||

Is this possible? The local cube file is created off the server database using MDX (create global cube).

yes possible , but there is a problem. u have to run this MDX statment manually , I tries to put it in a schedule in a jobs list , but it says the MDX syntax is worng, any help?

Karim

pivot tables & offline cube access

Is it possible to have multiple pivot tables in the same Excel workbook accessing the same offline cube file?

Currently, when the user is connected to the server it connects to a .asdatabase to populate many pivot tables. I need to allow the user to access a local cube file when they are not connected to the server.

I am using excel vba macros to connect the pivot caches to the online data source, which works perfectly. I would like to do the same to connect the pivot caches to the local cube file.

Is this possible? The local cube file is created off the server database using MDX (create global cube).

Thanks,
Lyn

If this is AS2005 - there is a bug in this area that is being fixed in the SP2 release. I think the fix would allow you to do what you've described.|||

Is this possible? The local cube file is created off the server database using MDX (create global cube).

yes possible , but there is a problem. u have to run this MDX statment manually , I tries to put it in a schedule in a jobs list , but it says the MDX syntax is worng, any help?

Karim

pivot tables & offline cube access

Is it possible to have multiple pivot tables in the same Excel workbook accessing the same offline cube file?

Currently, when the user is connected to the server it connects to a .asdatabase to populate many pivot tables. I need to allow the user to access a local cube file when they are not connected to the server.

I am using excel vba macros to connect the pivot caches to the online data source, which works perfectly. I would like to do the same to connect the pivot caches to the local cube file.

Is this possible? The local cube file is created off the server database using MDX (create global cube).

Thanks,
Lyn

If this is AS2005 - there is a bug in this area that is being fixed in the SP2 release. I think the fix would allow you to do what you've described.|||

Is this possible? The local cube file is created off the server database using MDX (create global cube).

yes possible , but there is a problem. u have to run this MDX statment manually , I tries to put it in a schedule in a jobs list , but it says the MDX syntax is worng, any help?

Karim

Pivot Table with SSAS 2005

hi

i facing problem when i tring to browse cube created in SSAS 2005 from pivot table in FP appear this error

The query could not be processed:

o An error was encountered in the transport layer.

o The peer prematurely closed the connection.

any one have solutions for this problem

thanks

Ensure that you have the Microsoft OLE DB Provider for Analysis Services 9.0 installed, as well as Microsoft Core XML Services 6.0. They can be downloaded here

http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

Once they are both installed, run regedit and look under HKEY_CLASSES_ROOT/MSOLAP - there should be a key called CLSID which should have the same value as the CLSID under HKEY_CLASSES_ROOT/MSOLAP.3

Now try to connect to the SSAS cube again...some people have found they need to specify their username as <Domain>\<Username> to connect correctly

Pivot Table / CUBE ?

Please see the attached fileThere are two options.
If you know the column name than use the first option (see sample) if you do not know it than use the second option (it is dynamic execution).

Eyal

--Second option
CREATE PROC sp_CrossTab
@.table AS sysname, -- Table to crosstab
@.onrows AS nvarchar(128), -- Grouping key values (on rows)
@.onrowsalias AS sysname = NULL, -- Alias for grouping column
@.oncols AS nvarchar(128), -- Destination columns (on columns)
@.sumcol AS sysname = NULL -- Data cells
AS

DECLARE
@.sql AS varchar(8000),
@.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
SET @.sql =
'SELECT' + @.NEWLINE +
' ' + @.onrows +
CASE
WHEN @.onrowsalias IS NOT NULL THEN ' AS ' + @.onrowsalias
ELSE ''
END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @.keyssql AS varchar(1000)
SET @.keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +
'FROM ' + @.table

EXEC (@.keyssql)

-- 6
DECLARE @.key AS nvarchar(100)
SELECT @.key = MIN(keyvalue) FROM #keys

WHILE @.key IS NOT NULL
BEGIN
SET @.sql = @.sql + ',' + @.NEWLINE +
' SUM(CASE CAST(' + @.oncols +
' AS nvarchar(100))' + @.NEWLINE +
' WHEN N''' + @.key +
''' THEN ' + CASE
WHEN @.sumcol IS NULL THEN '1'
ELSE @.sumcol
END + @.NEWLINE +
' ELSE 0' + @.NEWLINE +
' END) AS c' + @.key

SELECT @.key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @.key
END

--7
SET @.sql = @.sql + @.NEWLINE +
'FROM ' + @.table + @.NEWLINE +
'GROUP BY ' + @.onrows + @.NEWLINE +
'ORDER BY ' + @.onrows

-- PRINT @.sql + @.NEWLINE -- For debug
EXEC (@.sql)

GO

--First option
Use pubs
GO

create table strings
(
groupcol char(1) not null,
keycol int not null,
string varchar(10) not null
)
GO

insert into strings values('a', 11, 'strA1')
insert into strings values('a', 152, 'strA2')
insert into strings values('b', 101, 'strB1')
insert into strings values('b', 201, 'strB2')
insert into strings values('b', 307, 'strB3')
insert into strings values('b', 499, 'strB4')
GO

select groupcol,
max(case when rownum = 1 then string end) as str1,
max(case when rownum = 2 then string end) as str2,
max(case when rownum = 3 then string end) as str3,
max(case when rownum = 4 then string end) as str4,
max(case when rownum = 5 then string end) as str5
from (select *, (select count(*)
from strings as s2
where s2.groupcol = s1.groupcol
and s2.keycol <= s1.keycol) rownum
from strings as s1) as s
group by groupcol
GO

DROP Table strings

GO|||Thanks a lot|||I use the second option (dynamic execution)|||I use stored procedure "sp_CrossTab", is OK (testing with "SQL ExecMS")

If I want to use this stored procedure in VB , did not returns a recordset.

This is VB Code: (Where have I did it wrong ????)

Private cn As New ADODB.Connection
Private cmd As New ADODB.Command
Private rs As New ADODB.Recordset

'--------------------
Private Sub Form_Load()

Dim SirConectare_SQL As String

SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Integrated Security=SSPI" & _
";Persist Security Info=False" & _
";Initial Catalog='" & "Test" & "'" & _
";Data Source='" & "Acasa" & "'"

With cn
.ConnectionString = SirConectare_SQL
.Open
.CursorLocation = adUseClient

End With

End Sub
'-----------------------
Private Sub Command1_Click()

cmd.ActiveConnection = cn
cmd.CommandText = "sp_CrossTab"
cmd.CommandType = adCmdStoredProc

cmd.Parameters(1).Value = "Table1"
cmd.Parameters(2).Value = "Day"
cmd.Parameters(3).Value = "XXXXX"
cmd.Parameters(4).Value = "Grup"
cmd.Parameters(5).Value = "Value_1"

Set rs = cmd.Execute

MsgBox rs.RecordCount

End Sub|||I forgot this ...

set nocount on

.....................

It is OK, thanks

pivot table

I'm trying to extract some data from an ssas 2005 cube to build a report with ssrs 2005.

The report should have a variable number of columns and a fixed number of rows ... so I think I cannot use a table control but I must use a matrix control ...

So I would group the column for the fiscal month and the row for the measure name or measure caption ... and put the measure value inside the matrix.

Like the following

month 1 month 2 measure 1 xxx xxx measure 2 xxx xxx measure 3 xxx xxx

To do that I should run a query to extract data in the following form ...

fiscal month mesaure name measure value month 1 measure 1 xxx month 1 measure 2 xxx month 1 measure 3 xxx month 2 measure 1 xxx month 2 measure 2 xxx month 2 measure 3 xxx

The problem is ... when running an mdx query on reporting services I need to put the meausure only on the columns ...

so any idea on how can I extract data from ssas in that form ?

Cosimo

Can you place an upper bound on the number of months you need to report? Also, how do you want your months to display from left to right -- most recent to least recent?|||

thank

I solved ... using a static group and a matrix control

Cosimo