One fact table/cube or multiple fact tables/cube

Hi,

I am now starting on my 2nd analysis server project and I have 8 dimensions and 6 fact tables. One fact table has 90 million rows, the other one has 30 million rows. The other four are less then 1 million rows big.

Should I create one cube with 6 fact tables in it or 6 cubes with one fact table ?

The advantage of the first one is that if you need to make a report you can have all the data in one query which is great for the users.

The advantage of the last one is that if you develop, you can easily calculate and test a small cube.

I am also thinking to get the best of both worlds namely going for the last option and a a 7th cube which has links to all the other six cubes.

Any suggestions ?

Constantijn Enders

This will have thoughts or considerations to address most of your questions.

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

I would love to hear other people's thoughts, too.

|||

And here's a separate discussion on the same topic:

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

|||

Hi,

I finally ended up at this blog http://prologika.com/CS/blogs/blog/archive/2006/06/27/1331.aspx

This confirmed my final thought, split into smaller and then one cube to rule them all

CE

One data source view and multiple data source

Hi,

In my datawarehouse we have different database one for dimensions and one for fact tables.

can we create a cube to pull dimensions from one data soure and fact from other databsource?

I recommend you to have the fact tables and the dimensions in the same database.

Your long term quick-fix is to use views between the databases.

Your short scenario description looks like you are building a cube directly from a source system.

If you need to connect another source system you will have to create a data wareouse to consolidate each source.

If not, you wille be creating information silos above each source system that you cannot connect to a second system.

HTH

Thomas Ivarsson

|||both the source are on the same SQL Server but different databases, I was planning to use View but was just considering the performance impact that will cause.|||

Actually Analysis Services allows for having dimensions and parittions to come from different datasources.

The caveat here is not to use different datasources to define your dimension. In such case Analysis Services might decide to use OPENROWSET clause as part of the query it sends during processing of dimension. This would slow you down considerably. But having partitions to come from different datasource should be perfectly fine.

Run Profier to capture SQL queries Aanlysis Server sends during processing and verify you dont get OPENROWSET is these queries.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

on nt4

I know this sounds oldish but the fact is I have Sql Server 2000 installed
on an NT4 machine. Everything works correctly exept Sql Server stops by
itself after a certain amount of time, irrigularly. There is nothing in the
log of the server of the machine.
Is there another tool that I could use to find out why this appends.
thanks
Fernand
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
Hi,
Could you please monitor your resource utlization (Memory in particular)
using performance monitor. Check the usage of virtual memory as well.
Look into the Event viewer as well as SQL server error logs for any messages
or errors.
As well as check you have the latest service pack applied for SQL 2000.
Latest is SP3a
select serverproperty('productlevel')
Thanks
Hari
MCDBA
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:nWmQc.5869$DP2.32555@.wagner.videotron.net...
> I know this sounds oldish but the fact is I have Sql Server 2000 installed
> on an NT4 machine. Everything works correctly exept Sql Server stops by
> itself after a certain amount of time, irrigularly. There is nothing in
the
> log of the server of the machine.
> Is there another tool that I could use to find out why this appends.
> thanks
> Fernand
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
>
|||I am not familiar with your sayings. What do you mean by Event viewer. On
XP it is easy to figure out, but on Nt4 the Log that I found did not give
any indication, not even the fact that I restarted Sql Server.
I will also install Sp3.
thanks
Fernand
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
emOBT4seEHA.2440@.tk2msftngp13.phx.gbl...
> Hi,
> Could you please monitor your resource utlization (Memory in particular)
> using performance monitor. Check the usage of virtual memory as well.
> Look into the Event viewer as well as SQL server error logs for any
messages[vbcol=seagreen]
> or errors.
> As well as check you have the latest service pack applied for SQL 2000.
> Latest is SP3a
> select serverproperty('productlevel')
>
> Thanks
> Hari
> MCDBA
>
> "Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
> news:nWmQc.5869$DP2.32555@.wagner.videotron.net...
installed
> the
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
|||Hi,
Event viewer -- Which stores all the System , Application and Security logs.
Go to Start -- Programs -- Administrative tools -- Event viewer-- Click the
Log menu to see the other logs.
Thanks
Hari
MCDBA
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:olnQc.6942$DP2.35920@.wagner.videotron.net...
> I am not familiar with your sayings. What do you mean by Event viewer.
On[vbcol=seagreen]
> XP it is easy to figure out, but on Nt4 the Log that I found did not give
> any indication, not even the fact that I restarted Sql Server.
> I will also install Sp3.
> thanks
> Fernand
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
> emOBT4seEHA.2440@.tk2msftngp13.phx.gbl...
> messages
> installed
by[vbcol=seagreen]
in
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
>

on nt4

I know this sounds oldish but the fact is I have Sql Server 2000 installed
on an NT4 machine. Everything works correctly exept Sql Server stops by
itself after a certain amount of time, irrigularly. There is nothing in the
log of the server of the machine.
Is there another tool that I could use to find out why this appends.
thanks
Fernand
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02Hi,
Could you please monitor your resource utlization (Memory in particular)
using performance monitor. Check the usage of virtual memory as well.
Look into the Event viewer as well as SQL server error logs for any messages
or errors.
As well as check you have the latest service pack applied for SQL 2000.
Latest is SP3a
select serverproperty('productlevel')
Thanks
Hari
MCDBA
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:nWmQc.5869$DP2.32555@.wagner.videotron.net...
> I know this sounds oldish but the fact is I have Sql Server 2000 installed
> on an NT4 machine. Everything works correctly exept Sql Server stops by
> itself after a certain amount of time, irrigularly. There is nothing in
the
> log of the server of the machine.
> Is there another tool that I could use to find out why this appends.
> thanks
> Fernand
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
>|||I am not familiar with your sayings. What do you mean by Event viewer. On
XP it is easy to figure out, but on Nt4 the Log that I found did not give
any indication, not even the fact that I restarted Sql Server.
I will also install Sp3.
thanks
Fernand
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
emOBT4seEHA.2440@.tk2msftngp13.phx.gbl...
> Hi,
> Could you please monitor your resource utlization (Memory in particular)
> using performance monitor. Check the usage of virtual memory as well.
> Look into the Event viewer as well as SQL server error logs for any
messages
> or errors.
> As well as check you have the latest service pack applied for SQL 2000.
> Latest is SP3a
> select serverproperty('productlevel')
>
> Thanks
> Hari
> MCDBA
>
> "Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
> news:nWmQc.5869$DP2.32555@.wagner.videotron.net...
installed[vbcol=seagreen]
> the
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02|||Hi,
Event viewer -- Which stores all the System , Application and Security logs.
Go to Start -- Programs -- Administrative tools -- Event viewer-- Click the
Log menu to see the other logs.
Thanks
Hari
MCDBA
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:olnQc.6942$DP2.35920@.wagner.videotron.net...
> I am not familiar with your sayings. What do you mean by Event viewer.
On
> XP it is easy to figure out, but on Nt4 the Log that I found did not give
> any indication, not even the fact that I restarted Sql Server.
> I will also install Sp3.
> thanks
> Fernand
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
> emOBT4seEHA.2440@.tk2msftngp13.phx.gbl...
> messages
> installed
by[vbcol=seagreen]
in[vbcol=seagreen]
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
>

on nt4

I know this sounds oldish but the fact is I have Sql Server 2000 installed
on an NT4 machine. Everything works correctly exept Sql Server stops by
itself after a certain amount of time, irrigularly. There is nothing in the
log of the server of the machine.
Is there another tool that I could use to find out why this appends.
thanks
Fernand
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02Hi,
Could you please monitor your resource utlization (Memory in particular)
using performance monitor. Check the usage of virtual memory as well.
Look into the Event viewer as well as SQL server error logs for any messages
or errors.
As well as check you have the latest service pack applied for SQL 2000.
Latest is SP3a
select serverproperty('productlevel')
Thanks
Hari
MCDBA
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:nWmQc.5869$DP2.32555@.wagner.videotron.net...
> I know this sounds oldish but the fact is I have Sql Server 2000 installed
> on an NT4 machine. Everything works correctly exept Sql Server stops by
> itself after a certain amount of time, irrigularly. There is nothing in
the
> log of the server of the machine.
> Is there another tool that I could use to find out why this appends.
> thanks
> Fernand
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
>|||I am not familiar with your sayings. What do you mean by Event viewer. On
XP it is easy to figure out, but on Nt4 the Log that I found did not give
any indication, not even the fact that I restarted Sql Server.
I will also install Sp3.
thanks
Fernand
"Hari Prasad" <hari_prasad_k@.hotmail.com> a écrit dans le message de news:
emOBT4seEHA.2440@.tk2msftngp13.phx.gbl...
> Hi,
> Could you please monitor your resource utlization (Memory in particular)
> using performance monitor. Check the usage of virtual memory as well.
> Look into the Event viewer as well as SQL server error logs for any
messages
> or errors.
> As well as check you have the latest service pack applied for SQL 2000.
> Latest is SP3a
> select serverproperty('productlevel')
>
> Thanks
> Hari
> MCDBA
>
> "Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
> news:nWmQc.5869$DP2.32555@.wagner.videotron.net...
> > I know this sounds oldish but the fact is I have Sql Server 2000
installed
> > on an NT4 machine. Everything works correctly exept Sql Server stops by
> > itself after a certain amount of time, irrigularly. There is nothing in
> the
> > log of the server of the machine.
> > Is there another tool that I could use to find out why this appends.
> >
> > thanks
> >
> > Fernand
> >
> >
> >
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
> >
> >
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02|||Hi,
Event viewer -- Which stores all the System , Application and Security logs.
Go to Start -- Programs -- Administrative tools -- Event viewer-- Click the
Log menu to see the other logs.
Thanks
Hari
MCDBA
"Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
news:olnQc.6942$DP2.35920@.wagner.videotron.net...
> I am not familiar with your sayings. What do you mean by Event viewer.
On
> XP it is easy to figure out, but on Nt4 the Log that I found did not give
> any indication, not even the fact that I restarted Sql Server.
> I will also install Sp3.
> thanks
> Fernand
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> a écrit dans le message de news:
> emOBT4seEHA.2440@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Could you please monitor your resource utlization (Memory in particular)
> > using performance monitor. Check the usage of virtual memory as well.
> > Look into the Event viewer as well as SQL server error logs for any
> messages
> > or errors.
> >
> > As well as check you have the latest service pack applied for SQL 2000.
> > Latest is SP3a
> >
> > select serverproperty('productlevel')
> >
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> > "Fernand St-Georges" <fernand.st-georges@.videotron.ca> wrote in message
> > news:nWmQc.5869$DP2.32555@.wagner.videotron.net...
> > > I know this sounds oldish but the fact is I have Sql Server 2000
> installed
> > > on an NT4 machine. Everything works correctly exept Sql Server stops
by
> > > itself after a certain amount of time, irrigularly. There is nothing
in
> > the
> > > log of the server of the machine.
> > > Is there another tool that I could use to find out why this appends.
> > >
> > > thanks
> > >
> > > Fernand
> > >
> > >
> > >
> > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
> > >
> > >
> >
> >
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.733 / Virus Database: 487 - Release Date: 2004-08-02
>

OLE DB: How to set the property DBPROP_SSCE_TRANSACTION_COMMIT_MODE correctly?

Hello,

because of the fact that the database sdf-file is stored on a cf-card, i want that all commit transactions will be flushed to the file immediately.
So i want to open the database with the above property. But i don't know the type and value i have to place into the variant. The following code fragment gave me the error "DB_S_ERRORSOCCURRED when i try to set the property with IDBInitialize::SetProperties() :

//Initialize property DBPROP_SSCE_TRANSACTION_COMMIT_MODE
dbprop_ssce_session[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;
dbprop_ssce_session[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop_ssce_session[0].vValue.vt = VT_I4;
dbprop_ssce_session[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;

//Initialize property set DBPROPSET_SSCE_SESSION
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION;
dbpropset[1].rgProperties = dbprop_ssce_session;
dbpropset[1].cProperties = 1;
//(there is another property for the path not shown here)

I would appreciate it when someone out there knows the answer and could tell me.

Kind regards,
Andre

Your code looks ok. I use the ATL OLE DB Consumer Templates for this and they also set the colid member to DB_NULLID. This is the only thing I see missing from your code.

Other than this, I would also look at the OLE DB error parameters to get more details.

|||

Every thing is okay except that you are setting the property on IDBInitialize. This is a session related property and so should be set using ISessionProperties.

If the question is answered, please mark it as answered.

Thanks

Raja

|||Thanks Raja,

that was exactly the crux of the matter!

Kind regards,
Andre

OLE DB: How to set the property DBPROP_SSCE_TRANSACTION_COMMIT_MODE correctly?

Hello,

because of the fact that the database sdf-file is stored on a cf-card, i want that all commit transactions will be flushed to the file immediately.
So i want to open the database with the above property. But i don't know the type and value i have to place into the variant. The following code fragment gave me the error "DB_S_ERRORSOCCURRED when i try to set the property with IDBInitialize::SetProperties() :

//Initialize property DBPROP_SSCE_TRANSACTION_COMMIT_MODE
dbprop_ssce_session[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;
dbprop_ssce_session[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop_ssce_session[0].vValue.vt = VT_I4;
dbprop_ssce_session[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;

//Initialize property set DBPROPSET_SSCE_SESSION
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION;
dbpropset[1].rgProperties = dbprop_ssce_session;
dbpropset[1].cProperties = 1;
//(there is another property for the path not shown here)

I would appreciate it when someone out there knows the answer and could tell me.

Kind regards,
Andre

Your code looks ok. I use the ATL OLE DB Consumer Templates for this and they also set the colid member to DB_NULLID. This is the only thing I see missing from your code.

Other than this, I would also look at the OLE DB error parameters to get more details.

|||

Every thing is okay except that you are setting the property on IDBInitialize. This is a session related property and so should be set using ISessionProperties.

If the question is answered, please mark it as answered.

Thanks

Raja

|||Thanks Raja,

that was exactly the crux of the matter!

Kind regards,
Andre