Facts with infinite measures

Hello!

I was wondering if there exists convenient ways to handel infinite measures in SSAS.

In my exmple there are facts describing software licenses. Normally, licenses grant the right for a specific amount of installations. However, as for something like a site or enterprise license, the allowed number of installations is unlimited.

Of course this can workarounded by a separate measure counting those unlimited licenses but it adds more complexity and inconveniance to analysis.

Thanks in advance for ideas,

Regards, Alex

Sorry - but I couldn't understand from your description what exactly you need, and what would "infinite measures" solve.|||

Hello Mosha,

I will do my very best to make it more clear.

Here is an example source table "Licenses" from the data warehouse:

DimOwner | DimSoftware | [Allowed Installations] | [Allowed Unlimited]

Department1 | MySoftware | 10 | false

Department2 | MySoftware | null | true

What ever might happen in the cube, as a result a user wants to analyse the available licenses in PivotTables that look like the following.

Analyzing on company level:

DimSoftware | Allowed Installations

MySoftware | <infinite> (some symbol)

With added DimOwner dimension:

DimOwner | DimSoftware | [Allowed Installations]

Department1 | MySoftware | 10

Department2 | MySoftware | <infinite> (some symbol)

Possible solution I currently can imagin:

DimOwner | DimSoftware | [Allowed Installations] | [Unlimited license]

Department1 | MySoftware | 10 | 0

Department2 | MySoftware | empty | 1

But doing so would double quite a lot of measures I currently have and therefore add complexity to analysis that I would like to hide from users.

Best regards, Alex

|||

OK, I see. I think the best solution is to have that additional measure indicator of unlimited licences. You can define it as boolean or integer and choose aggregation function MAX. Since this measure will ever get only one of two values, the AS will automatically compress it down to 1 bit per record. So overhead on partition size should be minimal. You then will hide both Allowed Installations and Unlimited license measures from the user, and create the following calculated measure:

CREATE Installations = IIF ( Measures.[Unlimited license], -1, Measures.[Allowed Installations] );

Format_String(Measures.Installations) = ';I\nf\i\nite;;'

Basically, I mark infinite number of installations with -1, and then use formatting to display it as "Infinite" to the end user.

HTH,

Mosha.

|||

Thanks a lot! Nice trick with the MAX and Format_string :)

Best regards,

Alex

|||

Tiny addition: Excel 2k7 prefers the following notation of Format_String for some reason:

Format_String(Measures.Installations) = ';"Infinite";;'

Fact Table with Dimensions

Hello:
I'm a newbie to Datawarehousing and using of the concepts of Dimensional
modeling. I read about Kimball's methodology too.
I started using Analysis Services, which is really great. I created tables
with the primary index values.
My scenario is like this.
I should be able to drill down to any of these reports and the details
also. I would like evaluate the sale of tools in different regions during
different times, with different customers, with different tools description
and with different types of purchases(in any fashion), the way users need.
I created 5 different tables with
Tool Description, Region, Time, Purchase Type, Customer.
All tables are linked by Tool number which is unique.
Tool Description has fields such as
Tools Description, Tool Type, Tool Group, Tool Number(The index has
multiple columns to make it unique).
Region - Region, State, Zip Code, Tool_No(Unique Record)
Time - Year,Quarter,Month,Week,Day,Tool_No
Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
Tool_NO(Index values)
Customer(Customer age, Gender,Tool_No)
I created all these tables and keyed in values manually for my own testing
purposes. I would be populating the values using Stored PRocedures(probably
by DTS) as time goes.
But to create a FACT table with multiple dimensions, the following
questions arose.
Can I create a fact table with the following columns or do I need to add
Tool_No also in the FACT table.
Tool_Description,Year,Purchase_Type,Cust
omer,Total_Sales.
I guess that I need to populate this fact table using T-SQL before
creating a cube .Am I correct? Please do let me know.
In the FACT TAble, do I need to go to the level of granularity of the
Tool_No also.
Once I create a fact table with T-SQL queries, can I go ahead and create a
cube with all 4 - 5 dimensions, the way I would really like to drill it down
to the reports.
Any help is greatly appreciated.
Thanks,Bobby, i could take a look at your current SQL Server database and OLAp
structures. Send them to me, andI'll see what can I do
Alejandro Leguizamo
SQL Server MVP
Colombia
"Bobbyx2405" <Bobbyx2405@.discussions.microsoft.com> wrote in message
news:6899853C-03EA-490C-9EE1-657E276A85F3@.microsoft.com...
> Hello:
> I'm a newbie to Datawarehousing and using of the concepts of
> Dimensional
> modeling. I read about Kimball's methodology too.
> I started using Analysis Services, which is really great. I created
> tables
> with the primary index values.
> My scenario is like this.
> I should be able to drill down to any of these reports and the details
> also. I would like evaluate the sale of tools in different regions during
> different times, with different customers, with different tools
> description
> and with different types of purchases(in any fashion), the way users need.
> I created 5 different tables with
> Tool Description, Region, Time, Purchase Type, Customer.
> All tables are linked by Tool number which is unique.
> Tool Description has fields such as
> Tools Description, Tool Type, Tool Group, Tool Number(The index has
> multiple columns to make it unique).
> Region - Region, State, Zip Code, Tool_No(Unique Record)
> Time - Year,Quarter,Month,Week,Day,Tool_No
> Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
> Tool_NO(Index values)
> Customer(Customer age, Gender,Tool_No)
> I created all these tables and keyed in values manually for my own
> testing
> purposes. I would be populating the values using Stored
> PRocedures(probably
> by DTS) as time goes.
> But to create a FACT table with multiple dimensions, the following
> questions arose.
> Can I create a fact table with the following columns or do I need to add
> Tool_No also in the FACT table.
> Tool_Description,Year,Purchase_Type,Cust
omer,Total_Sales.
> I guess that I need to populate this fact table using T-SQL before
> creating a cube .Am I correct? Please do let me know.
> In the FACT TAble, do I need to go to the level of granularity of the
> Tool_No also.
> Once I create a fact table with T-SQL queries, can I go ahead and create
> a
> cube with all 4 - 5 dimensions, the way I would really like to drill it
> down
> to the reports.
>
> Any help is greatly appreciated.
> Thanks,
>
>

Extremely high number of pages/sec

Hello!
I have noticed very high number of page/sec in perfmon. It is averaging over
1000 pages/sec.I would like to isolate this issue to determine what process
is causing excessive pagination and possibly SQL server SPID. Is there an
easy way to troubleshoot the source of pagination?
Thanks,
Igor
First of all, I would like to rule out OS as primary suspect. Is there a way
to pinpoint SQL Server SPID as a source?
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:09f001c5146a$630ad3b0$a401280a@.phx.gbl...[vbcol=seagreen]
> You can run profiler, and include textdata, writes/reads
> columns in the trace. You'll be able to see what SPs/SQL
> commands have intensive io operations.
> OJ
> It is averaging over
> determine what process
> SPID. Is there an

Extremely High number of page/sec

Hello!
I have noticed very high number of page/sec in perfmon. It is averaging over
1000 pages/sec.I would like to isolate this issue to determine what process
is causing excessive pagination and possibly SQL server SPID. Is there an
easy way to troubleshoot the source of pagination?
Thanks,
Igor
Igor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
I should have added that high Read counts should be looked at as well.
David Gugick
Imceda Software
www.imceda.com
|||Igor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
Look for transactions with high CPU and/or high duration. You can use
Profiler for this or a number of SQL performance profiling tools (we
offer Coefficient).
David Gugick
Imceda Software
www.imceda.com

Extremely High number of page/sec

Hello!
I have noticed very high number of page/sec in perfmon. It is averaging over
1000 pages/sec.I would like to isolate this issue to determine what process
is causing excessive pagination and possibly SQL server SPID. Is there an
easy way to troubleshoot the source of pagination?
Thanks,
IgorIgor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
I should have added that high Read counts should be looked at as well.
David Gugick
Imceda Software
www.imceda.com|||Igor Marchenko wrote:
> Hello!
> I have noticed very high number of page/sec in perfmon. It is
> averaging over 1000 pages/sec.I would like to isolate this issue to
> determine what process is causing excessive pagination and possibly
> SQL server SPID. Is there an easy way to troubleshoot the source of
> pagination?
> Thanks,
> Igor
Look for transactions with high CPU and/or high duration. You can use
Profiler for this or a number of SQL performance profiling tools (we
offer Coefficient).
David Gugick
Imceda Software
www.imceda.com