Dimensional Model

I am in the process of designing a Booking, Billing and Backlog
datamart. My backend is going to be SQL Server 2K. Data source is
Baan ERP system. The transaction level I have to populate the
datamart is at sales order line level.
My problem is that the data model I have come up with is more
relational than dimensional. Here is what I have got so far.
Orders
Order Detail
Customer
Products
Countries
Regions
Employees
Other way of sourcing the data would be to write one big query from
ERP and then do the dimensional model but that would slow down the
data retrieval. I would be grateful if any you can share your
experience with this sort of a datamart.
Thanks
Ather
Well, you have to take the approach of dimensional modelling to make it
work. http://www.ralphkimball.com has some good articles to get you started,
also I would recommend a book "Data Wareshousing TookKit" by the same author
has some good examples that you can related to.
Pramod
"Ather Mian" <athermian@.yahoo.com> wrote in message
news:6ed9e09e.0403310515.4c0e0a79@.posting.google.c om...
> I am in the process of designing a Booking, Billing and Backlog
> datamart. My backend is going to be SQL Server 2K. Data source is
> Baan ERP system. The transaction level I have to populate the
> datamart is at sales order line level.
> My problem is that the data model I have come up with is more
> relational than dimensional. Here is what I have got so far.
> Orders
> Order Detail
> Customer
> Products
> Countries
> Regions
> Employees
> Other way of sourcing the data would be to write one big query from
> ERP and then do the dimensional model but that would slow down the
> data retrieval. I would be grateful if any you can share your
> experience with this sort of a datamart.
> Thanks
> Ather
sql

Dimensional Model

I am in the process of designing a Booking, Billing and Backlog
datamart. My backend is going to be SQL Server 2K. Data source is
Baan ERP system. The transaction level I have to populate the
datamart is at sales order line level.
My problem is that the data model I have come up with is more
relational than dimensional. Here is what I have got so far.
Orders
Order Detail
Customer
Products
Countries
Regions
Employees
Other way of sourcing the data would be to write one big query from
ERP and then do the dimensional model but that would slow down the
data retrieval. I would be grateful if any you can share your
experience with this sort of a datamart.
Thanks
AtherWell, you have to take the approach of dimensional modelling to make it
work. http://www.ralphkimball.com has some good articles to get you started,
also I would recommend a book "Data Wareshousing TookKit" by the same author
has some good examples that you can related to.
Pramod
"Ather Mian" <athermian@.yahoo.com> wrote in message
news:6ed9e09e.0403310515.4c0e0a79@.posting.google.com...
> I am in the process of designing a Booking, Billing and Backlog
> datamart. My backend is going to be SQL Server 2K. Data source is
> Baan ERP system. The transaction level I have to populate the
> datamart is at sales order line level.
> My problem is that the data model I have come up with is more
> relational than dimensional. Here is what I have got so far.
> Orders
> Order Detail
> Customer
> Products
> Countries
> Regions
> Employees
> Other way of sourcing the data would be to write one big query from
> ERP and then do the dimensional model but that would slow down the
> data retrieval. I would be grateful if any you can share your
> experience with this sort of a datamart.
> Thanks
> Ather

Dimension Properties

I am using SSAS 2005. I have a Dimension table that is based on the entire data source and then 2 Fact tables that set the data into two groups. When I browse the data for Fact Table 1 in SSAS 2005, I am getting missing dimension values for all the data in Fact table 2. Is there a join property that will tell SSAS to only use data from the Dimension table that is also in the Fact table? I have a primary key on the Dimension table that relates the dimension table to the Fact tables.

S

You will probably need to create a view in relational database or named query in DSV to build a join that only brings data you would like to see in Analysis Services.

You should not rely on Analysis Services to solve data integrity problems.

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

Digits converted to null

Hi,
Im loading data from excel source into a table with all the columns as varchar, I found out that rows from excel with digit value are transformed to Null values into the destination table.

One workaround was to add single quote at the beginning of the digits from the excel file. Is there a way in the SSIS to do the transformation instead ofmanually updating the excel file?

any help...tnx..

Not that I know of and I've spent some time looking. Reading data from Excel is tricky. For example, the data type of the column can change from row-to-row, and Excel can store data that doesn't match the defined data type. These are pretty big challenges for an OLE DB provider trying to read it like a table.
In several cases I've resorted to exporting my Excel source to a tab-delimited file for SSIS to read. At least you can automate this instead of having to manually fix each Excel file.|||You need to specify Import Mode by adding IMEX=1 to the connection string, in the Extended Properties argument along with the Excel version and HDR name/value pairs.

Please note that we have done our best to document this and other known issues in the topics for the Excel Source and the Excel Destination in BOL. This content has been further augmented for the upcoming Web refresh of BOL.

-Doug|||This type of problem was also applicable to DTS so this article should

probably still apply

Excel Inserts Null Values

(http://www.sqldts.com/default.aspx?254)

Allan

"DouglasL@.discussions.microsoft.com"

wrote in message

news:3b274106-b020-42bb-95cf-a1aeb554ea98@.discussions.microsoft.com:

> You need to specify Import Mode by adding IMEX=1 to the connection

> string, in the Extended Properties argument along with the Excel version

> and HDR name/value pairs.

>

> Please note that we have done our best to document this and other known

> issues in the topics for the Excel Source and the Excel Destination in

> BOL. This content has been further augmented for the upcoming Web

> refresh of BOL.

>

> -Doug

different image source for online and PDF reports?

In my current job, I've inherited a website and some ReportViewer
reports, and I've figured out how to poke around and make some changes
requested by my employer, but there's still a lot that I don't
understand. Any input would be appreciated.
Here's the current problem: the reports that we make available on our
website are mostly made up of images. Up until now, we've been saving
the images at the size that they need to be when the reports are
viewed online at 100%. Of course, this means that when exporting the
report to PDF, or printing it, the quality is unacceptable. I've tried
saving larger images and scaling them proportionately in the reports,
which results in beautiful PDFs and illegible online reports due to
resampling. Naturally, and reasonably, my employer wants our reports
to look good online AND when they're printed. I've looked around
online and found that other people have wrestled with this problem,
with no clear solutions.
The one idea that I've come up with is to use different images for the
online and printed versions (so the online report would use /preview/
image01.png and the PDF would use /print/image01.png or something). Do
you think this is possible? Any idea where I would start?
Thanks in advance for any advice.
SarahOn Jun 4, 2:37 pm, "s. o." <sarah.oh.li...@.gmail.com> wrote:
> In my current job, I've inherited a website and some ReportViewer
> reports, and I've figured out how to poke around and make some changes
> requested by my employer, but there's still a lot that I don't
> understand. Any input would be appreciated.
> Here's the current problem: the reports that we make available on our
> website are mostly made up of images. Up until now, we've been saving
> the images at the size that they need to be when the reports are
> viewed online at 100%. Of course, this means that when exporting the
> report to PDF, or printing it, the quality is unacceptable. I've tried
> saving larger images and scaling them proportionately in the reports,
> which results in beautiful PDFs and illegible online reports due to
> resampling. Naturally, and reasonably, my employer wants our reports
> to look good online AND when they're printed. I've looked around
> online and found that other people have wrestled with this problem,
> with no clear solutions.
> The one idea that I've come up with is to use different images for the
> online and printed versions (so the online report would use /preview/
> image01.png and the PDF would use /print/image01.png or something). Do
> you think this is possible? Any idea where I would start?
> Thanks in advance for any advice.
> Sarah
That sounds like the best option. You should have one report that can
be viewed and then when the user goes to export it, change the report
behind the scenes in the application to point to the smaller imaged
report. This should be fairly straight forward in terms of pointing to
one report in a report viewer and then pointing to another report once
the user selects something like a print preview aspx page or
something. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Different Data Types in Data Source View

I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".

Has anyone encountered this yet?

Thanks,

Brian

Brian,

There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Search for: "Data type mismatches with tinyint keys"

HTH,

- Steve

|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||

Sherrill,

This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.

HTH,

Steve

|||

That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.

I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.

Different Data Types in Data Source View

I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".

Has anyone encountered this yet?

Thanks,

Brian

Brian,

There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Search for: "Data type mismatches with tinyint keys"

HTH,

- Steve

|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||

Sherrill,

This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.

HTH,

Steve

|||

That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.

I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.

Different Data Sources

I am a RS newbe, and I have common reports but different data sources. Is
there a way to modify the data source for an instance of a report without
changing the values in the ReportServer database?
--
BobRS 2005 supports expression-based connection strings with private data
sources only. Look at the ExpressionBasedConnection report
here(http://prologika.com/CS/blogs/blog/archive/2005/12/08/714.aspx)
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Bob Joyce" <BobJoyce@.discussions.microsoft.com> wrote in message
news:E813E946-5635-491A-973C-F4ADB4A63726@.microsoft.com...
>I am a RS newbe, and I have common reports but different data sources. Is
> there a way to modify the data source for an instance of a report without
> changing the values in the ReportServer database?
> --
> Bob