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

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

Oleddb dropdown list is empty in Excel

Hello,

I have an issue with connecting Excel to an analysis Services 2005 database. The computer is running Windows XP Sp2 and Office XP. I installed the oledb driver 9.0 but when I tried to connect with the pivot table and I want to create the connection, the dropdown list of the data provider is empty. I tried on another computer with Excel XP and the system is working fine.
Is there somebody that could help?

Thank you

It sounds like it might be a connection/networking issue? On the computer, try making an ODBC Data Source. You can do this by choosing Start->Control Panel->Administrative Tools->Data Sources (ODBC).

From here, on the User DSN tab, press the Add... button. On the list of providers, you should see "SQL Native Client" or "SQL Server". Choose one of these. On the next dialog, you should see 3 fields, Name, Description, and Server. Use the drop down list of "Server" to see if you can find the server that you're looking for. If you can, then it's something else. If you can't, then talk to your system administrator about troubleshooting why that server can't be seen from your computer. Either way, you can cancel and close these dialogs without making any changes to your computer. This should only test the visibility of the server to your computer.

Hope this helps. Please follow up if this doesn't help you.

|||

Thanks for the help but it doesn't work. If I try to create an ODBC connection the system is working fine. It seems to me that the problem is related only to the OleDb component for Analysis Services. I am going to try to uninstall and install back office to see it this will fix something.

Thanks again!

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.

OLEDB error while processing Analysis service database

Hi,
I migrated AS 2000 database to AS 2005 thorugh migration wizard and started processing the database. But I am getting error like "OLE DB error: OLE DB or ODBC error: Query (5, 19) Parser: The syntax for 'AS' is incorrect..". After migrating I didn't modified any settings.
If anybody having info, please share.
ThanksIt looks the SQL query sent by Analysis Services for processing is bad. You can examine the query in the processing dialog and see what's wrong (wrong syntax for AS clause?).

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").

OLE DB Provider for ODBC/Win XP

I have SQL Server Developer Edition which runs on Windows XP and it has been
running fine thus far. I am now trying to run Analysis Services on it and it
needs OLE DB Provider for ODBC. I downloaded MDAC 2.8 and installed it on my
Windows XP platform. The installation went without a hitch but when I go to
Data Sources (ODBC), I cannot see OLE DB Provider for ODBC listed. Does OLE
DB Provider for ODBC only run on Windows 2000 or Windows NT?
Thanks,
AlistairOops, that should be OLE DB Provider for OLAP.
Alistair
"lestersal" <lestersal@.cox.net> wrote in message
news:yLs3c.40660$1k.22567@.okepread01...
> I have SQL Server Developer Edition which runs on Windows XP and it has
been
> running fine thus far. I am now trying to run Analysis Services on it and
it
> needs OLE DB Provider for ODBC. I downloaded MDAC 2.8 and installed it on
my
> Windows XP platform. The installation went without a hitch but when I go
to
> Data Sources (ODBC), I cannot see OLE DB Provider for ODBC listed. Does
OLE
> DB Provider for ODBC only run on Windows 2000 or Windows NT?
> Thanks,
> Alistair
>|||The Data Sources (ODBC) control panel applet only deals with ODBC data
sources and drivers. You are referring to an OLE DB provider.
OLE DB has a similar data sources interface. Try creating a empty text
file, and save it with a .udl file extension. Then double-click that file
and you should get the OLE DB interface.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"lestersal" <lestersal@.cox.net> wrote in message
news:5oA3c.40902$1k.38183@.okepread01...
> Oops, that should be OLE DB Provider for OLAP.
> Alistair
> "lestersal" <lestersal@.cox.net> wrote in message
> news:yLs3c.40660$1k.22567@.okepread01...
> been
and
> it
on
> my
> to
> OLE
>

OLE DB for ODBC connections in BIDS/AS project

Hi,

I try to configure a new data source in my Analysis Services Project in BIDS and I would like to test the OLE DB for ODBC Provider, but I cannot find it, although it is definitely installed on the box.

Is this kind of connection not allowed or not supported for AS?

Norbert

I'm pretty certain that it's not supported for AS2005.

Sorry,

Chris

|||

Indeed the OLEDB for ODBC provider is not supported by Analysis Services 2005.

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