Newbie query-problems

Hello,
I have just started with SQL and could not find the solution to this
problem:
My access-database lists all the owners with the date they bought
and sold a property.
There are 3 tables:
Owner with fields: ID, Descr_L, Ledgernr
Property with fields: ID, Descr
Transact with fields: Date, Property, Owner, Linenr, Prev.owner,
Prev.Linenr
Each record hold a date, ID of property, ID of previous owner,
linenr of prev.owners ledger, ID of owner and linenr of ownersledger.
When I group by property and sort by date, I get a list where teh
owner on a row equals the prev.owner on the next row.
I like to get a list of breaks in this list (where the owner is NOT
equal prev.owner on the next row) showing:
Property.Descr, Owner.Descr_L, Owner.Ledgernr, Linenr
Please can anyone help me.
Thanksselect yada yada
From Transact t
Inner join ( Select * from Transact t2 where t2.property =
2.property --find the next row
and t2.date = (select min(t3.date) from Transact t3 where
t3.property = t1.property and t3.date < t1.date) ) as PrevRow
--the Previous row is the row in the transact table with the same property
id but the date is the smallest date > than the current row...
THen you can add a where clause
Where t.Prevowner != prevRow.owner
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"E.R. Visser" <ervi@.wxs.nl> wrote in message
news:ct2soi$869$1@.reader10.wxs.nl...
>
> Hello,
> I have just started with SQL and could not find the solution to this
> problem:
> My access-database lists all the owners with the date they bought
> and sold a property.
> There are 3 tables:
> Owner with fields: ID, Descr_L, Ledgernr
> Property with fields: ID, Descr
> Transact with fields: Date, Property, Owner, Linenr, Prev.owner,
> Prev.Linenr
> Each record hold a date, ID of property, ID of previous owner,
> linenr of prev.owners ledger, ID of owner and linenr of ownersledger.
> When I group by property and sort by date, I get a list where teh
> owner on a row equals the prev.owner on the next row.
> I like to get a list of breaks in this list (where the owner is NOT
> equal prev.owner on the next row) showing:
> Property.Descr, Owner.Descr_L, Owner.Ledgernr, Linenr
> Please can anyone help me.
> Thanks
>
>
>
>|||Wayne,
Thank you for your quick respons.
I'll try this solution.
Erik
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> schreef in bericht
news:%23vzTd6hAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> select yada yada
> From Transact t
> Inner join ( Select * from Transact t2 where t2.property =
> 2.property --find the next row
> and t2.date = (select min(t3.date) from Transact t3 where
> t3.property = t1.property and t3.date < t1.date) ) as PrevRow
> --the Previous row is the row in the transact table with the same property
> id but the date is the smallest date > than the current row...
> THen you can add a where clause
> Where t.Prevowner != prevRow.owner
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "E.R. Visser" <ervi@.wxs.nl> wrote in message
> news:ct2soi$869$1@.reader10.wxs.nl...
>

Newbie needs place to start

I am totally new to the BI world. I was just wondering where I should start to begin setting up a BI solution. I started trying to go through the Microsoft Anaylsis Services tutorial line for line in the MSDN website. I couldn't get the sample solution to work quite right. Should I be starting somewhere more simple to start. Please help, so far it looks a bit overwelming, especially with the terminology and examples I have seen so far from various sites about BI and Data Mining.

I think the virtual labs are a great place to start.

http://msdn2.microsoft.com/en-us/virtuallabs/aa740409.aspx

Each lab is 20 minutes and gives an overview of one or two technologies. Together they cover the whole BI space, and they all follow similar themes, so learning from one can work with another. Subjects include:

Business Intelligence: Building Integrated BI Solutions Leveraging SQL 2005 and Office 2007 Servers Business Intelligence: Using Business Scorecard Manager 2005 and ProClarity for Performance Management Business Intelligence: Excel 2007 PivotTables and Analysis Services Business Intelligence: Report Builder Business Intelligence: Integration Services Business Intelligence: Service Analysis Business Intelligence: Data Mining Business Intelligence: Business Scorecard Manager

newbie - getting data out of AS / cube

We are in the beginning phases of designing a data warehouse solution, using
SSAS 2005 and .Net technologies. I know that we can use BI portals such as
from Proclarity, Panorama, Business Objects, etc. However, we wanted to
create our own UI front end (either Web or Winform).
My question is this -- can someone recommend what (and how) .Net
technologies to use to consume the data from SSAS? e.g. in a traditional
OLTP system, I would use sql queries and ado.net to fill my data table, and
maybe use an OR mapper to hydrate custom business objects. The data table
would then be sent to the UI front end for display / control binding and
handling.
For a data warehouse... what would I do differently? Do I still use ado.net
and maybe MDX / XMLA to query the cube? What data structures do I use to
consume the data? A Dataset with relationships? Would you transform that
data to customized business objects? Do you have any strategies on how to
support drill-down on the front end?
Or, would the middle-tier be very "thin" -- meaning the front-end would just
interface to AS directly and we have smart controls to bind to the raw data?
Any pointers would be greatly appreciated.
Thanks
Ray
p.s. I've been cross posting to the two newsgroups -- could someone tell me
what questions belong to which group?
Reporting Portal for OLAP
Hello Ray,
For a quick start have a look at SQL Server 2005 Reporting Services.
It comes free with SQL server and provides a web base portal that you
can customize using .NET and or Sharepoint. You can create your own
app/ portal using RS web services.
RS2005 Reports support MDX natively so you can use Reporting Services
to deliver standard reports against Analysis Services.
New to SQL server 2005 is Report Builder that provides a great web
based UI for creating ad hoc reports and publishing them back to the
Reporting services portal. Report Builder also supports drill through
to underlying data.
Check out
http://www.microsoft.com/technet/pro.../2005ssrs.mspx
Hope this gets you started.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hi,
Thank you for the response. I have looked a little bit into Reporting
Services... I may be a bit weak on the Sharepoint side of things though --
my concern is that the RS2005/Sharepoint didn't offer sufficient
customization abilities. Some things our Web App may need to do:
- controls for boston matrix, various types of chargs, speedometers
- full user session management, security, roles, etc.
- integration with other data sources (e.g. Weather, newsfeed)
- support for user input -- e.g. in cost/budget/forecasting analysis
- support for "advanced" reports -- our organization is proficient with
Crystal Reports, so we may wish to continue using it over RS2005.
I was concerned that the solution/portal offered by Microsoft (or other
vendors) would not meet our requirements. This is why I think it would make
sense if we built our own custom portal using our own technologies.
Hence, my question about the middle-tier -- What type of data constructs
should I be using?
Thank you,
Ray
<Myles.Matheson@.gmail.com> wrote in message
news:1124414479.649578.162120@.g49g2000cwa.googlegr oups.com...
> Reporting Portal for OLAP
> Hello Ray,
> For a quick start have a look at SQL Server 2005 Reporting Services.
> It comes free with SQL server and provides a web base portal that you
> can customize using .NET and or Sharepoint. You can create your own
> app/ portal using RS web services.
> RS2005 Reports support MDX natively so you can use Reporting Services
> to deliver standard reports against Analysis Services.
> New to SQL server 2005 is Report Builder that provides a great web
> based UI for creating ad hoc reports and publishing them back to the
> Reporting services portal. Report Builder also supports drill through
> to underlying data.
> Check out
> http://www.microsoft.com/technet/pro.../2005ssrs.mspx
> Hope this gets you started.
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
|||I found the best way to exchange data between the UI and business layers was
"processed" XML/A.
That is to say, the business layer implements services that receive user
requests, maps them to MDX query patterns, queries the server using Adomd.net
and specifying an XML/A response type, transforms the XML/A to match the
request (table, indicator, chart) and returns it to the UI layer.
Consider that a standard XML/A response will usually include tons of
unnecessary metadata (in a custom app), so you can substantially reduce the
size of the XML/A message before sending it to the UI layer.
HTH,
Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html
"Ray" wrote:

> Hi,
> Thank you for the response. I have looked a little bit into Reporting
> Services... I may be a bit weak on the Sharepoint side of things though --
> my concern is that the RS2005/Sharepoint didn't offer sufficient
> customization abilities. Some things our Web App may need to do:
> - controls for boston matrix, various types of chargs, speedometers
> - full user session management, security, roles, etc.
> - integration with other data sources (e.g. Weather, newsfeed)
> - support for user input -- e.g. in cost/budget/forecasting analysis
> - support for "advanced" reports -- our organization is proficient with
> Crystal Reports, so we may wish to continue using it over RS2005.
> I was concerned that the solution/portal offered by Microsoft (or other
> vendors) would not meet our requirements. This is why I think it would make
> sense if we built our own custom portal using our own technologies.
> Hence, my question about the middle-tier -- What type of data constructs
> should I be using?
> Thank you,
> Ray
> <Myles.Matheson@.gmail.com> wrote in message
> news:1124414479.649578.162120@.g49g2000cwa.googlegr oups.com...
>
>

newbie - getting data out of AS / cube

We are in the beginning phases of designing a data warehouse solution, using
SSAS 2005 and .Net technologies. I know that we can use BI portals such as
from Proclarity, Panorama, Business Objects, etc. However, we wanted to
create our own UI front end (either Web or Winform).
My question is this -- can someone recommend what (and how) .Net
technologies to use to consume the data from SSAS? e.g. in a traditional
OLTP system, I would use sql queries and ado.net to fill my data table, and
maybe use an OR mapper to hydrate custom business objects. The data table
would then be sent to the UI front end for display / control binding and
handling.
For a data warehouse... what would I do differently? Do I still use ado.net
and maybe MDX / XMLA to query the cube? What data structures do I use to
consume the data? A Dataset with relationships? Would you transform that
data to customized business objects? Do you have any strategies on how to
support drill-down on the front end?
Or, would the middle-tier be very "thin" -- meaning the front-end would just
interface to AS directly and we have smart controls to bind to the raw data?
Any pointers would be greatly appreciated.
Thanks
Ray
p.s. I've been cross posting to the two newsgroups -- could someone tell me
what questions belong to which group?Reporting Portal for OLAP
Hello Ray,
For a quick start have a look at SQL Server 2005 Reporting Services.
It comes free with SQL server and provides a web base portal that you
can customize using .NET and or Sharepoint. You can create your own
app/ portal using RS web services.
RS2005 Reports support MDX natively so you can use Reporting Services
to deliver standard reports against Analysis Services.
New to SQL server 2005 is Report Builder that provides a great web
based UI for creating ad hoc reports and publishing them back to the
Reporting services portal. Report Builder also supports drill through
to underlying data.
Check out
http://www.microsoft.com/technet/pr...5/2005ssrs.mspx
Hope this gets you started.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||Hi,
Thank you for the response. I have looked a little bit into Reporting
Services... I may be a bit weak on the Sharepoint side of things though --
my concern is that the RS2005/Sharepoint didn't offer sufficient
customization abilities. Some things our Web App may need to do:
- controls for boston matrix, various types of chargs, speedometers
- full user session management, security, roles, etc.
- integration with other data sources (e.g. Weather, newsfeed)
- support for user input -- e.g. in cost/budget/forecasting analysis
- support for "advanced" reports -- our organization is proficient with
Crystal Reports, so we may wish to continue using it over RS2005.
I was concerned that the solution/portal offered by Microsoft (or other
vendors) would not meet our requirements. This is why I think it would make
sense if we built our own custom portal using our own technologies.
Hence, my question about the middle-tier -- What type of data constructs
should I be using?
Thank you,
Ray
<Myles.Matheson@.gmail.com> wrote in message
news:1124414479.649578.162120@.g49g2000cwa.googlegroups.com...
> Reporting Portal for OLAP
> Hello Ray,
> For a quick start have a look at SQL Server 2005 Reporting Services.
> It comes free with SQL server and provides a web base portal that you
> can customize using .NET and or Sharepoint. You can create your own
> app/ portal using RS web services.
> RS2005 Reports support MDX natively so you can use Reporting Services
> to deliver standard reports against Analysis Services.
> New to SQL server 2005 is Report Builder that provides a great web
> based UI for creating ad hoc reports and publishing them back to the
> Reporting services portal. Report Builder also supports drill through
> to underlying data.
> Check out
> http://www.microsoft.com/technet/pr...5/2005ssrs.mspx
> Hope this gets you started.
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>|||I found the best way to exchange data between the UI and business layers was
"processed" XML/A.
That is to say, the business layer implements services that receive user
requests, maps them to MDX query patterns, queries the server using Adomd.ne
t
and specifying an XML/A response type, transforms the XML/A to match the
request (table, indicator, chart) and returns it to the UI layer.
Consider that a standard XML/A response will usually include tons of
unnecessary metadata (in a custom app), so you can substantially reduce the
size of the XML/A message before sending it to the UI layer.
HTH,
--
Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html
"Ray" wrote:

> Hi,
> Thank you for the response. I have looked a little bit into Reporting
> Services... I may be a bit weak on the Sharepoint side of things though --
> my concern is that the RS2005/Sharepoint didn't offer sufficient
> customization abilities. Some things our Web App may need to do:
> - controls for boston matrix, various types of chargs, speedometers
> - full user session management, security, roles, etc.
> - integration with other data sources (e.g. Weather, newsfeed)
> - support for user input -- e.g. in cost/budget/forecasting analysis
> - support for "advanced" reports -- our organization is proficient with
> Crystal Reports, so we may wish to continue using it over RS2005.
> I was concerned that the solution/portal offered by Microsoft (or other
> vendors) would not meet our requirements. This is why I think it would mak
e
> sense if we built our own custom portal using our own technologies.
> Hence, my question about the middle-tier -- What type of data constructs
> should I be using?
> Thank you,
> Ray
> <Myles.Matheson@.gmail.com> wrote in message
> news:1124414479.649578.162120@.g49g2000cwa.googlegroups.com...
>
>

Newbee:reporting services gets: Connection could not be made to reportserver

When I try to deploy the sample reports solution to the local IIS on this
win2003 server, the message "A connection could not be made to the report
server http://a1900/Reportserver" appears.
I do this by:
1. Loading the SampleReports.sln into the vb.net IDE.
2. Right-clicking on the SampleReports solution, then selecting Properties,
then setting 'TargetServer URL'=http://a1900/Reportserver (it also fails
when I set it to 'http://localhost/ReportServer'
3. Right-clicking on the SampleReports solution, then selecting 'Deploy'.
4. No errors during the build phase.
The IIS Manager window shows in the left pane:
iis information services
- a1900 (local computer)
-FTP Sites
--...
-Web Sites
-- Default Web Site
--_vti_bin
--reports
-- reportServer
etc.
-Web Service Extensions
...
The IIS is on the same machine that I operate on and has entries of 'Default
Web Site\Reportserver' and 'Default Web Site\Reports'. The 'Default Website'
has 'Permissions' set to 'Full control' for administrators, as which I am
logged in.
I notice that the 'Reports' and 'ReportServer' entries do not have the blue
globe icon (?) that most of the other IIS folder entries have. Perhaps
that's the cause of my problem.
Do I have to add Web service extensions for Reporting Services?
Perhaps the Reporting services installation did not go right?
How do I find the correct url for the target server?
Where do I have to look.
What books to read?
Any help is greatly appreciated.
Thanks.Some questions & answers:
"Do I have to add Web service extensions for Reporting Services? "
No.
"How do I find the correct url for the target server?"
You are using the correct URL. To test it manually, navigate to the
following path:
http://localhost/ReportServer/ReportService.asmx
This is the webservice that the "Deploy" option uses based upon the
"TargetServer" property you set in the Project settings.
You should receive a SOAP response (XML) w-hen you navigate to that
URL. If you do not get this, then you may have an IIS configuration
problem. Because of the security configurations needed, I generally
just reinstall Reporting Services to make sure the Virtual Directory
and permissions are setup properly. In IIS 6, you should see what
looks like a cog/gear as the symbol for the ReportServer virtual
directory. The globes are for websites (DefaultWeb and such) which
isnt required.
One other thing to check is if your DefaultWebsite is using port 80,
and accepts "(All Unassigned)" IP addresses. Anything unusual in
these IIS settings may indicate part of your problem.
I hope this helps a bit...
~Lance Hunt
http://weblogs.asp.net/lhunt/