Creating your first data warehouse

Hello,
I am doing a research project on data warehousing so that I can see
what it can do for my workplace. I have googled on this subject and
the web sites I found relating to this have not been too detailed. I
have a basic set of data and business questions that I want answered
already scoped out and I just wanted some rough guidelines as to what
common processes go into creating your first data warehouse. So far it
seems like a starting point is to grab the raw data that is of interest
and push it to a data warehouse, perhaps aggregating data as it is
moved. Past that point I am a little vague on creating fact tables and
dimensions. Does anybody have some sagely advice, or links to sites
that are a good starting point for someone wanting to throw together a
data warehouse?weluvpaul@.hotmail.com wrote:
> Hello,
> I am doing a research project on data warehousing so that I can see
> what it can do for my workplace. I have googled on this subject and
> the web sites I found relating to this have not been too detailed. I
> have a basic set of data and business questions that I want answered
> already scoped out and I just wanted some rough guidelines as to what
> common processes go into creating your first data warehouse. So far it
> seems like a starting point is to grab the raw data that is of interest
> and push it to a data warehouse, perhaps aggregating data as it is
> moved. Past that point I am a little vague on creating fact tables and
> dimensions. Does anybody have some sagely advice, or links to sites
> that are a good starting point for someone wanting to throw together a
> data warehouse?
Buy "The datawarehouse etl toolkit" and "the data warehouse toolkit"
from amazon.
Very good books that will help you understand the whole concept.|||Hi weluvpaul@.hotmail.com,
feel free to browse all the materials on my web
site...www.peternolan.com. It is there for 'newbies'. Feel free to go
to the beginners page. It points you to books I recommend.
I would recommend you do not try to 'read the books and then build a
DW'. Thats rather a slow way to go about getting a DW built.....If
your company truely has no DW already I'd suggest you look outside for
some skills/experience to show you the way.
On 'What can a DW do for my business?'
For commercial organisations the answer should be 'make more money'...
Or more correctly 'enable the business to increase profitability on a
long term sustainable basis.'
Alas, this does not seem to be what most DWs get built for...
Browse through my 'Newsletters' and especially the one about Business
Benefits of DWing.
On my 'fastest payback' project we got all our money back on the EDW
and more before we even turned it on......
So far I haven't heard of any other project that paid back before it
was finished!!
Best Regards
Peter Nolan
www.peternolan.com
weluvpaul@.hotmail.com wrote:
> Hello,
> I am doing a research project on data warehousing so that I can see
> what it can do for my workplace. I have googled on this subject and
> the web sites I found relating to this have not been too detailed. I
> have a basic set of data and business questions that I want answered
> already scoped out and I just wanted some rough guidelines as to what
> common processes go into creating your first data warehouse. So far it
> seems like a starting point is to grab the raw data that is of interest
> and push it to a data warehouse, perhaps aggregating data as it is
> moved. Past that point I am a little vague on creating fact tables and
> dimensions. Does anybody have some sagely advice, or links to sites
> that are a good starting point for someone wanting to throw together a
> data warehouse?|||Thanks for the advice guys, I will check out the references!

Creating Outlook rule

We try to create "Out of office" notice but not to reply, if Subject contain
SPAM
how do we do that?
Rule that we want to create is simple
- Relpy to anyone
- If subject DOES NOT Contain "SPAM"
Thanks
MCMC wrote:
> We try to create "Out of office" notice but not to reply, if Subject contain
> SPAM
> how do we do that?
> Rule that we want to create is simple
> - Relpy to anyone
> - If subject DOES NOT Contain "SPAM"
>
> Thanks
> MC
>
What's this doing in a SQL Server newsgroup? <sigh>...|||Because when you are desparate you cast a wide net...
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uj2s0UilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> MC wrote:
>> We try to create "Out of office" notice but not to reply, if Subject
>> contain
>> SPAM
>> how do we do that?
>> Rule that we want to create is simple
>> - Relpy to anyone
>> - If subject DOES NOT Contain "SPAM"
>>
>> Thanks
>> MC
>>
> What's this doing in a SQL Server newsgroup? <sigh>...|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uj2s0UilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> MC wrote:
> > We try to create "Out of office" notice but not to reply, if Subject
contain
> > SPAM
> > how do we do that?
> >
> > Rule that we want to create is simple
> >
> > - Relpy to anyone
> > - If subject DOES NOT Contain "SPAM"
> >
> >
> > Thanks
> > MC
> >
> >
> What's this doing in a SQL Server newsgroup? <sigh>...
Wants to make sure his SQL 2000 box ain't responding to spam? :-)|||Oops,
MC
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uj2s0UilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> MC wrote:
> > We try to create "Out of office" notice but not to reply, if Subject
contain
> > SPAM
> > how do we do that?
> >
> > Rule that we want to create is simple
> >
> > - Relpy to anyone
> > - If subject DOES NOT Contain "SPAM"
> >
> >
> > Thanks
> > MC
> >
> >
> What's this doing in a SQL Server newsgroup? <sigh>...

Creating Outlook rule

MC wrote:
> We try to create "Out of office" notice but not to reply, if Subject conta
in
> SPAM
> how do we do that?
> Rule that we want to create is simple
> - Relpy to anyone
> - If subject DOES NOT Contain "SPAM"
>
> Thanks
> MC
>
What's this doing in a SQL Server newsgroup? <sigh>...Because when you are desparate you cast a wide net...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uj2s0UilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> MC wrote:
> What's this doing in a SQL Server newsgroup? <sigh>...|||We try to create "Out of office" notice but not to reply, if Subject contain
SPAM
how do we do that?
Rule that we want to create is simple
- Relpy to anyone
- If subject DOES NOT Contain "SPAM"
Thanks
MC|||MC wrote:
> We try to create "Out of office" notice but not to reply, if Subject conta
in
> SPAM
> how do we do that?
> Rule that we want to create is simple
> - Relpy to anyone
> - If subject DOES NOT Contain "SPAM"
>
> Thanks
> MC
>
What's this doing in a SQL Server newsgroup? <sigh>...|||Because when you are desparate you cast a wide net...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uj2s0UilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> MC wrote:
> What's this doing in a SQL Server newsgroup? <sigh>...|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uj2s0UilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> MC wrote:
contain[vbcol=seagreen]
> What's this doing in a SQL Server newsgroup? <sigh>...
Wants to make sure his SQL 2000 box ain't responding to spam? :-)|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uj2s0UilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> MC wrote:
contain[vbcol=seagreen]
> What's this doing in a SQL Server newsgroup? <sigh>...
Wants to make sure his SQL 2000 box ain't responding to spam? :-)|||Oops,
MC
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uj2s0UilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> MC wrote:
contain[vbcol=seagreen]
> What's this doing in a SQL Server newsgroup? <sigh>...|||Oops,
MC
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uj2s0UilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> MC wrote:
contain[vbcol=seagreen]
> What's this doing in a SQL Server newsgroup? <sigh>...

Creating offline (local) cubes using XMLA, or any other way..

Hi,

I realise this subject has been discussed a lot but there seems to be no definitive answer.

The company I work for creates many cubes (currently using many SQL statements) for the sales regions at the end of each month and we need to move to a better platform, hence analysis services projects.

Is there any "easy" way to take a cube created by an analysis services project and save it to an offline (local) cube?

Currently I am trying to use VB.NET 2005 to process the XMLA definition from AS2005, which is easy to get at.

The VB code is:

Dim myClient As New Microsoft.AnalysisServices.Xmla.XmlaClient

Dim l_result, l_XmlaCommand As String

l_result = ""

Try

REMDim l_connect As String = "Provider=MSOLAP.3;Data Source=L00086SINEY;Initial Catalog=Beta1;Integrated Security=SSPI "

Dim l_connect As String = "Provider=MSOLAP.3;Data Source=c:\inetpub\wwwroot\xmla\BetaTest.cub;Integrated Security=SSPI;UseExistingFile=False"

myClient.Connect(l_connect)

l_XmlaCommand = "<Statement>begin transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Dim FS As New IO.StreamReader("C:\XMLA_Command.txt")

l_XmlaCommand = FS.ReadToEnd

FS.Close()

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

l_XmlaCommand = "<Statement>commit transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Catch ex As Exception

MsgBox("Error in Client connection")

Finally

myClient.Disconnect()

End Try

The XMLA_Command.txt (first few lines) is:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ParentObject>

<DatabaseID>http://localhost/xmla/testcube.cub</DatabaseID>

</ParentObject>

<ObjectDefinition>

<Cube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ID>Beta1</ID>

<Name>Beta1</Name>

<Annotations>

<Annotation>

and finally the error I get is:

Errors in the metadata manager. Either the database with the ID of 'http://localhost/xmla/testcube.cub' does not exist in the server with the ID of 'L00086SINEY\LocalCube', or the user does not have permissions to access the object.

The cube is created fine when pointed at the Analysis Server but not when a local target is used.

Apologies for the length of post but it explains where I am up to.

Obviously if there is a "create local cube" wizard that I'm missing in AS2005 then I'll be more than happy to abandon the project!

Any help, even if it's book recommendations, would be greatly appreciated.

Thanks,

John.

Follow up:

Sorted the answer myself with a little (OK lot!) of help from a book by Teo Lachev, Applied Microsoft Analysis Services 2005. it uses code so it won't be ideal for everyone but it works and is a lot simpler than trying to work out how XMLA works in AS2005 with local cubes, especially as even xmla.org now seems largely forgotten.

By using the code from the above book you can point the created program to an existing cube created using AS2005 within the BIDS package and output it to a local cube file, very simple.

A big thank you goes out to Teo as days of searching have now finished.

|||John

I'm deeply interested in any solution for creating offline cubes programmatically.

How did you solve your problem?

I managed to create them in general using "CREATE GLOBAL CUBE"-syntax but
I can not ignore processing errors like I can do when using the management console.

Any answer is appreciated

Regards
Klaus Wiesel|||The sample in Teo's book is an application that will build the "CREATE GLOBAL CUBE" command, so it probably will not help you much, there were no options for setting the error configuration. Teo maintains a website at www.prologika.com|||I have put the MDX statment "Create Cube ... " in SQL Agent , and it gives me errors, i want to schedule the creation of the cube , not do it maunally. any help?

Creating offline (local) cubes using XMLA, or any other way..

Hi,

I realise this subject has been discussed a lot but there seems to be no definitive answer.

The company I work for creates many cubes (currently using many SQL statements) for the sales regions at the end of each month and we need to move to a better platform, hence analysis services projects.

Is there any "easy" way to take a cube created by an analysis services project and save it to an offline (local) cube?

Currently I am trying to use VB.NET 2005 to process the XMLA definition from AS2005, which is easy to get at.

The VB code is:

Dim myClient As New Microsoft.AnalysisServices.Xmla.XmlaClient

Dim l_result, l_XmlaCommand As String

l_result = ""

Try

REMDim l_connect As String = "Provider=MSOLAP.3;Data Source=L00086SINEY;Initial Catalog=Beta1;Integrated Security=SSPI "

Dim l_connect As String = "Provider=MSOLAP.3;Data Source=c:\inetpub\wwwroot\xmla\BetaTest.cub;Integrated Security=SSPI;UseExistingFile=False"

myClient.Connect(l_connect)

l_XmlaCommand = "<Statement>begin transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Dim FS As New IO.StreamReader("C:\XMLA_Command.txt")

l_XmlaCommand = FS.ReadToEnd

FS.Close()

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

l_XmlaCommand = "<Statement>commit transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Catch ex As Exception

MsgBox("Error in Client connection")

Finally

myClient.Disconnect()

End Try

The XMLA_Command.txt (first few lines) is:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ParentObject>

<DatabaseID>http://localhost/xmla/testcube.cub</DatabaseID>

</ParentObject>

<ObjectDefinition>

<Cube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ID>Beta1</ID>

<Name>Beta1</Name>

<Annotations>

<Annotation>

and finally the error I get is:

Errors in the metadata manager. Either the database with the ID of 'http://localhost/xmla/testcube.cub' does not exist in the server with the ID of 'L00086SINEY\LocalCube', or the user does not have permissions to access the object.

The cube is created fine when pointed at the Analysis Server but not when a local target is used.

Apologies for the length of post but it explains where I am up to.

Obviously if there is a "create local cube" wizard that I'm missing in AS2005 then I'll be more than happy to abandon the project!

Any help, even if it's book recommendations, would be greatly appreciated.

Thanks,

John.

Follow up:

Sorted the answer myself with a little (OK lot!) of help from a book by Teo Lachev, Applied Microsoft Analysis Services 2005. it uses code so it won't be ideal for everyone but it works and is a lot simpler than trying to work out how XMLA works in AS2005 with local cubes, especially as even xmla.org now seems largely forgotten.

By using the code from the above book you can point the created program to an existing cube created using AS2005 within the BIDS package and output it to a local cube file, very simple.

A big thank you goes out to Teo as days of searching have now finished.

|||John

I'm deeply interested in any solution for creating offline cubes programmatically.

How did you solve your problem?

I managed to create them in general using "CREATE GLOBAL CUBE"-syntax but
I can not ignore processing errors like I can do when using the management console.

Any answer is appreciated

Regards
Klaus Wiesel|||The sample in Teo's book is an application that will build the "CREATE GLOBAL CUBE" command, so it probably will not help you much, there were no options for setting the error configuration. Teo maintains a website at www.prologika.com|||I have put the MDX statment "Create Cube ... " in SQL Agent , and it gives me errors, i want to schedule the creation of the cube , not do it maunally. any help?

Creating offline (local) cubes using XMLA, or any other way..

Hi,

I realise this subject has been discussed a lot but there seems to be no definitive answer.

The company I work for creates many cubes (currently using many SQL statements) for the sales regions at the end of each month and we need to move to a better platform, hence analysis services projects.

Is there any "easy" way to take a cube created by an analysis services project and save it to an offline (local) cube?

Currently I am trying to use VB.NET 2005 to process the XMLA definition from AS2005, which is easy to get at.

The VB code is:

Dim myClientAsNew Microsoft.AnalysisServices.Xmla.XmlaClient

Dim l_result, l_XmlaCommandAsString

l_result =""

Try

REMDim l_connect As String = "Provider=MSOLAP.3;Data Source=L00086SINEY;Initial Catalog=Beta1;Integrated Security=SSPI "

Dim l_connectAsString ="Provider=MSOLAP.3;Data Source=c:\inetpub\wwwroot\xmla\BetaTest.cub;Integrated Security=SSPI;UseExistingFile=False"

myClient.Connect(l_connect)

l_XmlaCommand ="<Statement>begin transaction</Statement>"

myClient.Execute(l_XmlaCommand,"", l_result,False,True)

Dim FSAsNew IO.StreamReader("C:\XMLA_Command.txt")

l_XmlaCommand = FS.ReadToEnd

FS.Close()

myClient.Execute(l_XmlaCommand,"", l_result,False,True)

l_XmlaCommand ="<Statement>commit transaction</Statement>"

myClient.Execute(l_XmlaCommand,"", l_result,False,True)

Catch exAs Exception

MsgBox("Error in Client connection")

Finally

myClient.Disconnect()

EndTry

The XMLA_Command.txt (first few lines) is:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ParentObject>

<DatabaseID>http://localhost/xmla/testcube.cub</DatabaseID>

</ParentObject>

<ObjectDefinition>

<Cube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ID>Beta1</ID>

<Name>Beta1</Name>

<Annotations>

<Annotation>

and finally the error I get is:

Errors in the metadata manager. Either the database with the ID of 'http://localhost/xmla/testcube.cub' does not exist in the server with the ID of 'L00086SINEY\LocalCube', or the user does not have permissions to access the object.

The cube is created fine when pointed at the Analysis Server but not when a local target is used.

Apologies for the length of post but it explains where I am up to.

Obviously if there is a "create local cube" wizard that I'm missing in AS2005 then I'll be more than happy to abandon the project!

Any help, even if it's book recommendations, would be greatly appreciated.

Thanks,

John.

Follow up:

Sorted the answer myself with a little (OK lot!) of help from a book by Teo Lachev, Applied Microsoft Analysis Services 2005. it uses code so it won't be ideal for everyone but it works and is a lot simpler than trying to work out how XMLA works in AS2005 with local cubes, especially as even xmla.org now seems largely forgotten.

By using the code from the above book you can point the created program to an existing cube created using AS2005 within the BIDS package and output it to a local cube file, very simple.

A big thank you goes out to Teo as days of searching have now finished.

|||John

I'm deeply interested in any solution for creating offline cubes programmatically.

How did you solve your problem?
I managed to create them in general using "CREATE GLOBAL CUBE"-syntax but
I can not ignore processing errors like I can do when using the management console.

Any answer is appreciated

Regards
Klaus Wiesel

|||The sample in Teo's book is an application that will build the "CREATE GLOBAL CUBE" command, so it probably will not help you much, there were no options for setting the error configuration. Teo maintains a website at www.prologika.com|||I have put the MDX statment "Create Cube ... " in SQL Agent , and it gives me errors, i want to schedule the creation of the cube , not do it maunally. any help?