October 28, 2009 at 12:28 pm
Hi,
I have a datacube and I use pivot tables in excel to display the information in the datacube..
I have the following dimension
Service Category,
Insurance
Monthtime.
I have written a query (tsql query) and if i query against a different database( which populates the data for the cubes) I get the same number of rows returned for all the category except for 1.
If i run the datacube I get around 3492 clients but when i run the query I get around 3563 Clients
The datacubes have been populated using a base table and when i query against that base table in SSMS i get 3563 clients
So i am wondering why there is a difference of 71 between them..
Now how do I find out if there is any filters or anything thats causing the difference and IS there a way I can directly query against the dimensions to find out the difference in clients..
Any help will be appreciated..
Thanks,
Karen
October 28, 2009 at 12:50 pm
Possibly 71 records/clients without transactions? you could prove it out from either direction:
SQL: run a query using the transaction/fact table/s and the dimension table. Use of a outer join and a having clause will let you select any customers where they have no transactions. Count of these should be 71.
SSAS: in SSMS, drag the customers to rows, any given measure to a column. Right click on the background of the canvas and select 'Show Empty Members'. Sorting or filtering on the measure should show 71 floating to the top/bottom.
HTH,
Steve.
October 29, 2009 at 9:43 am
Steve,
How do i do the second option...
can you please give me the steps... i am a newbie to SSAS
October 29, 2009 at 11:59 am
Open SSMS (SQL Server Management Studio) and connect to the SSAS server/instance.
Expand the Databases folder and then expand the database that contains the cube you're focusing on.
Right click on the CUbe and select Browse.
In the empty window (on the right, the cube objects such as dims and measures are on the left), right click anywhere in that empty space and select Show Empty Cells
Now drag the Customer level attribute in on to rows
Drag a measure (like Sales or similar) to the columns
Then right click on the measure header (Sales) and select Sort Ascending
The first several rows (let's hope 71 🙂 ) should be blank/empty.
HTH,
Steve.
October 29, 2009 at 12:06 pm
I did that and didnt find any blanks,,
and i tried querying the fact table and the dimensions but got around 3565 rows but the datacube give me around 3494 and when i click on show empty it doesnt do anything...
do u know a way i can find the query that is send to Sql Server when the query is processed instead acutally procession the cube.. cause i dont want to be reprocessing the cube and if i dont need to
October 29, 2009 at 12:49 pm
Ok, this sounds like it could be more of a specific data issue that you're seeing.
Have you performed a SUM query against the source fact table and compared that total to whats being reported in the cube? if the totals are correct, then it could be aggregation of transaction records. It all really depends on what tables you were getting counts from (your earliers posts weren't clear). If they don't macth, i'd continue with the below to try and determine the cause of the missing data.
In your sql query (your test query), it doesn't aggregate or perform distinct and you have multiple entries for some clients. By default, SSAS will aggregate rows where it can (e.g. where a "select * from tbl1" will return say 6500 rows, the same data (ie total sales figure) will exist in the cube, but a row count won't match).
I would look initially at:
- select count(*) from dim_client -- get the count of clients, dimension in cube should contain same count
- query with a left join from the dim_client to the fact, determine which, if any, clients have no transaction records.
To answer your last question, yes, you can see the query, either by running a sql trace, or even simpler, in the processing dialog window, if you expand the item list far enough, you'll be presented with the queries being executed against the server/s.
Steve.
October 29, 2009 at 1:03 pm
this is my query
[Code]
select distinct fr.client_ID
from dbo.tbl_fact_count_consumer_served f
left outer join tbl_report_fact_count_consumer_demog fr on f.client_Id = fr.client_Id
left outer join tbl_demension_time t on f.time_Id = t.time_Id
left outer join tbl_ben_plans_to_insurance bpi on f.plan_Id = bpi.plan_Id
left outer join tbl_ben_plans bp on bpi.plan_Id = bp.Plan_Id
left outer join tbl_providers p on f.prov_Id = p.prov_id
left outer join tbl_demension_service ds on f.srvc_grp_Id = ds.srvc_grp_Id
left outer join tbl_services s on ds.srvc_Id = s.srvc_Id
left outer join tbl_service_summary ss on ds.srvc_sum_Id = ss.srvc_sum_Id
left outer join tbl_service_definitions sd on ds.srvc_def_Id = ds.srvc_def_Id
Where bpi.ins_Id = 2 and ds.srvc_sum_Id = 1
and f.time_Id = 29707
order by fr.client_ID
[/code]
which returns 3565 rows
October 29, 2009 at 1:45 pm
stevefromOZ (10/29/2009)
To answer your last question, yes, you can see the query, either by running a sql trace, or even simpler, in the processing dialog window, if you expand the item list far enough, you'll be presented with the queries being executed against the server/s.
is this a xml file cause there is a job that we run to process the cubes
November 10, 2009 at 8:29 am
As you guys suggested...I took the solution file from the Production server and deployed my cube to the test server and when i run the cube in SSMS for SSAS I get the 3568 rows and i get the same thing in tsql too in my test server.
But the Production server still has a short of 72 rows.. When i processed the data for the cubes in the test server and the same query that is used to get the data for the measures and i got around 3568 rows in the test and the production server... but I still dont know why there is a difference of 72 rows between the Production datacube and the Test datacube?
When i running the cubes in the test server i saw some attribute keys were not taken into consideration and it was because that dimension table didnt have a record in that and my query too isnt taking that into consideration...
I dont see what is the difference between the production datacube and the test datacube cause basically i just opened the solution from the Production datacube in my computer and changed the datasource to reflect it to the test server and deployed the cube.. and I shouldnt see a differnce in the numbers cause i am trying to query data from 7/1/2009 to 7/30/2009 amd since that is some what historic data the numbers should change.. I see the same no. of null rows in the production db as well as test server db..
So do you guys think that there is something that not updating correctly or what may be the issue?
Thanks,
Karen
November 11, 2009 at 7:02 am
Karen Roslund (10/29/2009)
stevefromOZ (10/29/2009)
To answer your last question, yes, you can see the query, either by running a sql trace, or even simpler, in the processing dialog window, if you expand the item list far enough, you'll be presented with the queries being executed against the server/s.
is this a xml file cause there is a job that we run to process the cubes
When you are processing the cube, you should see a window with the steps being taken (validating, etc.) as they are being executed. This is where you can drill into the details and see the query statements.
In your query - you have a couple of left outer joins that are probably not really left outer joins.
If you are selecting something out of the right file, and it does not exist, isn't this treated like an inner join?
Where bpi.ins_Id = 2 and ds.srvc_sum_Id = 1
Greg E
December 4, 2009 at 3:45 pm
Maybe the Production cube hasn't been processed lately and is missing those 71 records?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply