.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Understanding Grain Statements / Measure Tables

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server

Hi all,

I'm working on my first data cube and have hit a potential roadblock.  My problem might be a knowledge gap rather than a technological one, so I'm happy to consider all and any answers.  Here's my situation.

I've been asked to see what BI can do for a specific client but I'm not having much luck moving forwards.  Technologically speaking, I'm using a single relational database stored and maintained within SQL Server 2008 R2.  I've access to BIDS, and the end view of this is to create a DataCube that can be plugged into PerformancePoint 2007.  I've gotten as far as setting up the data connections and a basic data view but from herein, I get stuck.

Because I've got the RDB, the view I've taken is to take the data with a view of applying a schema to it (star scheme would be sufficient I believe) rather than designing the schema first.  Problem is, every table I can see I believe is a dimension table, not a fact/measure table. As I'm lacking a grain statement, I'm not sure if there's a best way to move forwards.  The data specifically is a case management database for general medical practitioners, logging things such as correspondence, solicitors details and so on, not much of this is numeric and additive, although I need to get much closer to what's contained within it.

So, what can I

View Complete Post

More Related Resource Links

INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.

Truly Understanding View State

This article provides a detailed understanding of the ViewState.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Temporary Tables - MS SQL Server

Usage of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in current session while global temporary tables are visible across all sessions.

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

ListView DataPager with SQL-Statements that already return only paged data.



As far as I understand the ListView-Control and the DataPager-Control, they work like this: The ListView gets its datarows from its data source, then the DataPager causes the ListView to display only a subset of those rows.

So what, when I want to use custom databinding for the ListView, that *already* gets the certain subset from SQL-Server (for better performance). Can I and how can I use the DataPager then?

Thanx, S.

Using a trigger or anything else to populate two tables


Hi! I'm creating an application that's supposed to first add a record to table1, and then get the ID from that record to use when adding a record to table2, to be able to associate these two records with eachother.

The user gets to type in some values that goes to table1, and some values that goes to table2, but before the insert statement for table2 is executed i need the ID from the recently added record in table1. Some dude told me to use a trigger for the autopopulate purpose, but does that really work when i also need to save some values that's user input, and when those values doesn't get saved in table1?

Are there any other way to do this or can i send values to a trigger? I'm new to triggers and stored procedures, i don't have any particular knowledge of this, any help is appreciated!


Regards, Monsterbadboll

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

SqlDataSource UpdateCommand using 2 tables


I have two tables

Trans  with fields TransID, Date, CustomerID and some other stuff

Customer with fields CustomerID, Name, TaxId

On the screen the user only sees the fields Date and Customer Name. CustomerID is behind the scenes only.


I'm using SqlDataSource. Having no problems with SelectCommand. I don't know how to construct the UpdateCommand and InsertCommand.

Let's say the user changes the date, then I need to do an UPDATE.

UPDATE Trans SET Date = @Date, CustomerID = @CustomerID results in an error message and the record is not updated.

I get an error on the page that says "Sys.WebForms.PageRequestManagerServerErrorException: Input string was not in correct format".


I tried taking out the set for CustomerID and I still get the error on page.


Also, for inserting, the users will see a dropdownlist with Customer Names. I need to convert that to a CustomerID to be used in the new record being inserted in the database. I'm not sure how to do this.


Do I need to do something with Control Parameters?

How to display related tables in one crystal report and how to link this report with combobox?


Hi! I want to display a crystal report in my vb.net application. Suppose I have tables named student details, student marks, student address, etc... Now if I want to display all these details (fields of all tables) in one crystal report (with page breaks if necessary) then how will I achieve it. I will be providing a combo box in my application that contains list of student names. How can I link this combo box with the cystal report to dynamically display report for different student on selected index change of combo box? Help me friends. An example would be appreciable.

CLR Inside Out: Understanding The CLR Binder


In this article, we cover some of the best practices for assembly binding and loading using the CLR.

Aarthi Ramamurthy and Mark Miller

MSDN Magazine May 2009

Access Control: Understanding Windows File And Registry Permissions


Understanding the ACLs that govern permissions and rights before an operation is allowed to proceed is critical to enhancing security.

John R. Michener

MSDN Magazine November 2008

Advanced WPF: Understanding Routed Events And Commands In WPF


See how routed events and routed commands in Windows Presentation Foundation form the basis for communication between the parts of your UI.

Brian Noyes

MSDN Magazine September 2008

CLR Inside Out: Measure Early and Often for Performance, Part 2


In the second of a two-part series, Vance Morrison delves into the meaning of performance measurements, explaining what the numbers mean to you.

Vance Morrison

MSDN Magazine May 2008

CLR Inside Out: Measure Early and Often for Performance, Part 1


In this month's column, get the inside scoop on how to build performance into your apps from the start, rather than dealing with the fallout after you deploy them.

Vance Morrison

MSDN Magazine April 2008

Data Points: Creating Audit Tables, Invoking COM Objects, and More


Dealing with error handling between T-SQL and a calling application, evaluating when a field's value has changed, and creating auditing tables in SQL ServerT are all common issues that developers must tackle.

John Papa

MSDN Magazine April 2004

CLR Debugging: Improve Your Understanding of .NET Internals by Building a Debugger for Managed Code


With the release of .NET, the Microsoft scripting strategy has evolved. Script engines can now compile or interpret code for the Microsoft common language runtime (CLR) instead of integrating debugging capabilities directly into apps through Active Scripting. With that change comes a new set of published services for including debugging functionality in a custom host application. You'll see how to use the debugging services published in the .NET Framework to create a full-featured CLR debugger that allows you to set breakpoints, view call stacks, browse variables, view processes, enumerate threads, and perform other important debugging tasks.

Mike Pellegrino

MSDN Magazine November 2002

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend