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 Date Hierarchy - In several cubes, but with different captions

How I can model cubes that use one hierarchy, but

for each cube I want determine an own caption (AS2005).

How I can realize it?

Thank you in advanced for your answers.

Have you checked the translations tab in the dimension editor in BI-Dev Studio?

You can laso add columns to your dimension table in the RDBMS or in the data source view(named calculation) and make this additional rows member properties of dimension key or user hierarchy level attribute.

You will need a tool that supports translations or the change of a member name to an alternative member property. Excel 2007 and ProClarity Professional 6.2 also show member properties as information boxes when you put the cursor on a member.

HTH

Thomas Ivarsson

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.

One Cube or Many ?

Hi People,

I try to figure out the best way for cubes design...

whether it's to build one big cube for the entire organization or one cube for each business area...

Is there any article (or any other source of information) for the subject ?

Thanks!

Perhaps you should try to read this...

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx#ELGAC

|||

Microsoft advice you to aviod to many fact tables /measure groups in one cube. You can read more in the performance guide pointed to at the top of this message group.

Another problem is that security can be harder to manage with one big cube, because of the number of roles.

Building one cube for each business area sounds like a good idea.

HTH

Thomas Ivasson

|||

O.K

Thank's a lot.

sql

OLTP vs OLAP (Data Warehouse) as a DataSource in Analysis Services 2005

Hello,

Can I import an OLTP (Reltional DB) as a Data Source into SQL Server
Analysis Services 2005 and then use the Cube Wizard and the new Data
Source View feature to create the OLAP model ?

Or do I have to first design an OLAP Data Warehouse with a Star Schema
and then import this DW as a Data Source into my Analysis Services
Project.

With SQL Server 2000 , OLAP would be the way to go..but with SQL
Server 2005 , it seems as though the wizard and data source view
features do half the work for you.

I have an OLTP DB and am not sure which route I should take ! Any
suggestions / input would be much appreciated.

Thanks in Advance...

Regards
RusszeeHere's the followup answer to my own post...

Just what I thought...It's the new UDM all the way in SQL Server
2005 !!!

"SQL Server 2005 Analysis Services can create UDM cubes without the
intermediate step of building a star schema data warehouse. The UDM
enables organizations to build reporting applications directly against
a production system and doesn't require an intermediate data warehouse
as in the past. This is because the UDM doesn't require the data to be
in a star or snowflake schema, but can connect to any data source
whose data is stored in a third normal form. "

Source : http://www.microsoft.com/technet/pr...5/solvngbp.mspx

OLTP access

Hi,
With my OLTP on another server, OLAP cube design works up to the point of
actually processing the cube, then it errors processing the dimension.
If I move the OLTP database (SQL Server 2000) onto the machine running the
OLAP it works fine.
It seems to be a rights issue but the ODBC tests work fine and the cube
design has no trouble viewing the OLTP schema.
In its 'normal' position the OLTP is on a Win2k Server that is part of a
work group.
The OLAP is on a Win2k Domain Controller.
Any clues on how to establish a successful processing of the OLTP in its
normal position would be appreciated.
Thanks
Bobhave you made sure the services for OLTP sql server and AS on the other
server are started with the same admin account and not local?
When you are in the design in AS, you are authenticated using your login to
the sql server source so it is ok there, but when you are processing, it is
authenticated using the login that start up AS, so that login might not have
access to the sql server tables to process the dimensions.
"Bob" wrote:

> Hi,
> With my OLTP on another server, OLAP cube design works up to the point of
> actually processing the cube, then it errors processing the dimension.
> If I move the OLTP database (SQL Server 2000) onto the machine running the
> OLAP it works fine.
> It seems to be a rights issue but the ODBC tests work fine and the cube
> design has no trouble viewing the OLTP schema.
> In its 'normal' position the OLTP is on a Win2k Server that is part of a
> work group.
> The OLAP is on a Win2k Domain Controller.
> Any clues on how to establish a successful processing of the OLTP in its
> normal position would be appreciated.
> Thanks
> Bob
>
>|||Hi,
Thanks for your reply.
I altered the OLAP service to run under administrator but it has made no
difference.
This is extremely poor design in IMHO. If the ODBC object can connect to
the OLTP, the OLAP should be able to process, end of story.
There is no trusted connection between the OLAP machine (Win2k Domain
Controller) and the OLTP machine (Win2k Server but running as a member of a
workgroup)
So I am relying ODBC to sort out the security issues.
e.g.. Access can use ODBC to link to tables in the OLTP so how come the OLAP
is so frail?
I have tried to create an ODBC connection to the OLTP that uses SQL server
authentication but have failed . I keep getting
'Not associated with a trusted SQL server connection'.
In a perfect world I would promote the OLTP to be a domain controller of its
own domain and establish a trust relationship between the to domains. But no
can do.
I think Bill's merry men should be looking at this.
Either I am missing something fundamental or the OLAP connection needs
redesigning.
No way should it be this difficult to use in this situation.
i.e. Anything that Access can do, OLAP should be able to do. The security
implications are the same.
regards
Bob
"bc" <bc@.discussions.microsoft.com> wrote in message
news:D6258097-009A-492A-BA75-2AC40A664968@.microsoft.com...
> have you made sure the services for OLTP sql server and AS on the other
> server are started with the same admin account and not local?
> When you are in the design in AS, you are authenticated using your login
to
> the sql server source so it is ok there, but when you are processing, it
is
> authenticated using the login that start up AS, so that login might not
have[vbcol=seagreen]
> access to the sql server tables to process the dimensions.
>
> "Bob" wrote:
>
of[vbcol=seagreen]
the[vbcol=seagreen]

OLTP access

Hi,
With my OLTP on another server, OLAP cube design works up to the point of
actually processing the cube, then it errors processing the dimension.
If I move the OLTP database (SQL Server 2000) onto the machine running the
OLAP it works fine.
It seems to be a rights issue but the ODBC tests work fine and the cube
design has no trouble viewing the OLTP schema.
In its 'normal' position the OLTP is on a Win2k Server that is part of a
work group.
The OLAP is on a Win2k Domain Controller.
Any clues on how to establish a successful processing of the OLTP in its
normal position would be appreciated.
Thanks
Bob
have you made sure the services for OLTP sql server and AS on the other
server are started with the same admin account and not local?
When you are in the design in AS, you are authenticated using your login to
the sql server source so it is ok there, but when you are processing, it is
authenticated using the login that start up AS, so that login might not have
access to the sql server tables to process the dimensions.
"Bob" wrote:

> Hi,
> With my OLTP on another server, OLAP cube design works up to the point of
> actually processing the cube, then it errors processing the dimension.
> If I move the OLTP database (SQL Server 2000) onto the machine running the
> OLAP it works fine.
> It seems to be a rights issue but the ODBC tests work fine and the cube
> design has no trouble viewing the OLTP schema.
> In its 'normal' position the OLTP is on a Win2k Server that is part of a
> work group.
> The OLAP is on a Win2k Domain Controller.
> Any clues on how to establish a successful processing of the OLTP in its
> normal position would be appreciated.
> Thanks
> Bob
>
>
|||Hi,
Thanks for your reply.
I altered the OLAP service to run under administrator but it has made no
difference.
This is extremely poor design in IMHO. If the ODBC object can connect to
the OLTP, the OLAP should be able to process, end of story.
There is no trusted connection between the OLAP machine (Win2k Domain
Controller) and the OLTP machine (Win2k Server but running as a member of a
workgroup)
So I am relying ODBC to sort out the security issues.
e.g.. Access can use ODBC to link to tables in the OLTP so how come the OLAP
is so frail?
I have tried to create an ODBC connection to the OLTP that uses SQL server
authentication but have failed . I keep getting
'Not associated with a trusted SQL server connection'.
In a perfect world I would promote the OLTP to be a domain controller of its
own domain and establish a trust relationship between the to domains. But no
can do.
I think Bill's merry men should be looking at this.
Either I am missing something fundamental or the OLAP connection needs
redesigning.
No way should it be this difficult to use in this situation.
i.e. Anything that Access can do, OLAP should be able to do. The security
implications are the same.
regards
Bob
"bc" <bc@.discussions.microsoft.com> wrote in message
news:D6258097-009A-492A-BA75-2AC40A664968@.microsoft.com...
> have you made sure the services for OLTP sql server and AS on the other
> server are started with the same admin account and not local?
> When you are in the design in AS, you are authenticated using your login
to
> the sql server source so it is ok there, but when you are processing, it
is
> authenticated using the login that start up AS, so that login might not
have[vbcol=seagreen]
> access to the sql server tables to process the dimensions.
>
> "Bob" wrote:
of[vbcol=seagreen]
the[vbcol=seagreen]

OLEDB provider error.

Hi:

I have created a cube and deployed in sql server. I am trying to connect to analysis services to access cube with linked server. I am getting this error...how do i fix this.?

OLE DB provider "MSOLAP" for linked server "(null)" returned message "Errors in the OLE DB provider. The Extended Properties property is set to a value that is not supported.".

Msg 7373, Level 16, State 2, Line 1

Cannot set the initialization properties for OLE DB provider "MSOLAP" for linked server "(null)".

Thanks,

Pramod

What's your Extended Properties? Please check whether there is any typo.|||

Xinwei:

Extended properties for my database is null...

Thanks,

Pramod

|||

Just leave it blank like this: Extended Properties="".
What's your whole connection string?
Thanks.

|||

Xinwei:

This is the query i am using...

SELECT a.* FROM OPENROWSET( 'MSOLAP','DATASOURCE=PKUMAR\PKUMAR;

Initial Catalog=Analysis Services Project1;', 'SELECT Measures.members ON ROWS,

[Product Category].members ON COLUMNS FROM [Adventure Works Internet Sales]') as a

go

Thanks,

|||The problem is on the Extended Properities as the error msg stated. If you have nothing to specify, please try delete it.|||Yep...it didnt help|||I am moving this to the SQL Server Analysis Services Forum.

OLE DB timeout processing AS Cube

When doing a full process of the Analysis Services Cube with several partitioned measure groups. We receive: "OLE DB error: OLE DB or ODBC error: Query timeout expired HYT00" or "OLE DB error: OLE DB or ODBC error: Query timeout expired HY008". There are 410 million rows in one base table and 80 million rows in another base table. The other measure groups process fine. It has abended at 61 minutes the last 3 times. I can individually process each measure group successfully.

Any help is greatly appreciated, Edwina Derrick

Simular problem for me. Have a huge database to be queried and can't pass the 60 minutes query time despite having made sure that ALL!! timeout values are suposed to be eternal (zero in most cases). This seems like a bug to me. I used the developer release version of the SQL2005. Would appriciate the Microsoft development team to test this problem and to suply information of when and how to bypass this 1 hour timeout limitation.

|||Try increasing ExternalCommandTimeout Analysis Server advanced property.

Being in situation when Analysis Server needs to wait for an hour for some data to come from SQL Server is very unusual. Such situation ususally means that SQL server needs to perform some heavy operation before it can start sending data out. Try optimizing your SQL Server database.
Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.|||

WTF....

isn't there a way to set the timeout?

If I was able to "Optimize my SQL Server database".....

I have five fact tables (All of them are required) and each table has it's own Dimensions.

since I am unable to create a cube on a left join relationship I need to work with views ("SQL server needs to perform some heavy operation before it can start sending data out").

OLE DB timeout processing AS Cube

When doing a full process of the Analysis Services Cube with several partitioned measure groups. We receive: "OLE DB error: OLE DB or ODBC error: Query timeout expired HYT00" or "OLE DB error: OLE DB or ODBC error: Query timeout expired HY008". There are 410 million rows in one base table and 80 million rows in another base table. The other measure groups process fine. It has abended at 61 minutes the last 3 times. I can individually process each measure group successfully.

Any help is greatly appreciated, Edwina Derrick

Simular problem for me. Have a huge database to be queried and can't pass the 60 minutes query time despite having made sure that ALL!! timeout values are suposed to be eternal (zero in most cases). This seems like a bug to me. I used the developer release version of the SQL2005. Would appriciate the Microsoft development team to test this problem and to suply information of when and how to bypass this 1 hour timeout limitation.

|||Try increasing ExternalCommandTimeout Analysis Server advanced property.

Being in situation when Analysis Server needs to wait for an hour for some data to come from SQL Server is very unusual. Such situation ususally means that SQL server needs to perform some heavy operation before it can start sending data out. Try optimizing your SQL Server database.
Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.|||

WTF....

isn't there a way to set the timeout?

If I was able to "Optimize my SQL Server database".....

I have five fact tables (All of them are required) and each table has it's own Dimensions.

since I am unable to create a cube on a left join relationship I need to work with views ("SQL server needs to perform some heavy operation before it can start sending data out").

OLE DB timeout processing AS Cube

When doing a full process of the Analysis Services Cube with several partitioned measure groups. We receive: "OLE DB error: OLE DB or ODBC error: Query timeout expired HYT00" or "OLE DB error: OLE DB or ODBC error: Query timeout expired HY008". There are 410 million rows in one base table and 80 million rows in another base table. The other measure groups process fine. It has abended at 61 minutes the last 3 times. I can individually process each measure group successfully.

Any help is greatly appreciated, Edwina Derrick

Simular problem for me. Have a huge database to be queried and can't pass the 60 minutes query time despite having made sure that ALL!! timeout values are suposed to be eternal (zero in most cases). This seems like a bug to me. I used the developer release version of the SQL2005. Would appriciate the Microsoft development team to test this problem and to suply information of when and how to bypass this 1 hour timeout limitation.

|||Try increasing ExternalCommandTimeout Analysis Server advanced property.

Being in situation when Analysis Server needs to wait for an hour for some data to come from SQL Server is very unusual. Such situation ususally means that SQL server needs to perform some heavy operation before it can start sending data out. Try optimizing your SQL Server database.
Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.|||

WTF....

isn't there a way to set the timeout?

If I was able to "Optimize my SQL Server database".....

I have five fact tables (All of them are required) and each table has it's own Dimensions.

since I am unable to create a cube on a left join relationship I need to work with views ("SQL server needs to perform some heavy operation before it can start sending data out").